How to automatically update table, whenever data added to another table?



  • I have created two tables in MySQL. One (Todo_tbl) holds the data while the other (Statistics_tbl) hold the sums one of the variables from Todo_tbl.

    Following the definitions of these tables:

    create table Todo_tbl (
    id INT auto_increment,
    person VARCHAR(45) ,
    task VARCHAR(45) ,
    duration INT(4),
    deadline_day VARCHAR(2),
    deadline_month VARCHAR(2),
    PRIMARY KEY(id)
    )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
    

    insert into Todo_tbl values(1,'John', 'dust the floors', 40,04,03);
    insert into Todo_tbl values(2,'Matt', 'do the dishes', 15,02,02);
    insert into Todo_tbl values(3,'Mary', 'dusting', 40,03,02);
    insert into Todo_tbl values(4,'Chloe', 'cleaning the windows', 75,04,05);
    insert into Todo_tbl values(5,'John', 'wash the floors', 60,03,03);
    insert into Todo_tbl values(6,'Bridget', 'take out the trash', 15,03,03);
    insert into Todo_tbl values(7,'Matt', 'do the laundry', 18,02,02);
    insert into Todo_tbl values(8,'Bridget', 'water the plants', 15,03,03);

    select * from Todo_tbl;

    create table Statistics_tbl (
    SELECT person, SUM(duration) as total_duration FROM Todo_tbl GROUP BY person
    );

    select * from Statistics_tbl;

    The problem is that whenever I add new data to the Todo_tbl, it is not considered in the Statistics_tbl.

    Is there any way to connect two tables, so that when there is a change in one, the other will respond as well?



  • Why do you need a table to hold the statistics when you have a perfectly good query that can get you the same thing from the original table?

    Databases like to stick to a concept known as the "Single Source of Truth".
    One table (or a group of them) holds the fundamental data. If that's wrong, everything's wrong. In this case, that's your todo_tbl.

    If you need another table that has to be kept in step with todo_tbl - however that's done - then you run the risk of the two getting out of step and misrepresenting what's what.

    Suggestion: Pop an index on Todo_tbl.person (may help with the grouping) and use the select statement you already have to get the statistics as the heart of a View that gets you the statistics data straight from the Single Source of Truth.

    CREATE VIEW statistics_v AS
    SELECT 
      person 
    , SUM( duration ) as total_duration 
    FROM todo_tbl 
    GROUP BY person ; 
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2