How should I handle updating data in a one-to-many relationship database schema?

Question

Lets say I have a database similar to the following:

Table: People

id | name | age
---+------+-----
 1 | dave | 78

Table: Likes

id | userid | like
---+--------+-------
 1 |    1   | apples
---+--------+-------
 2 |    1   | oranges
---+--------+-------
 3 |    1   | women

What would be the best way to handle updating daves data? Currently, I do the following:

Update his age
UPDATE People SET age = 79 WHERE id = 1;

Update his likes
DELETE FROM Likes WHERE userid = 1;
INSERT INTO LIKES (userid, like) VALUES (1, 'new like');
INSERT INTO LIKES (userid, like) VALUES (1, 'another like');

I delete all the users data from the table and then readd their new stuff. This seems inefficient. Is there a better way?

Answer 1

You could add a new column to Likes storing the actual age. By doing this you get a history funtionality and you can determine his likes by requesting those with the highest age like:

SELECT * FROM Likes WHERE userid = 1 AND age = (SELECT MAX(age) FROM Likes WHERE userid = 79 GROUP BY userid);

When you want be able to override some Likes you also could add another column "overriden_by" containing the id of the new Like. This would result in a simpler query:

SELECT * FROM Likes WHERE userid = 1 and overriden_by is null;

Answer 2

Depending on the DBMS you could use "MERGE" "REPLACE" "INSERT OR REPLACE", most DBMSes support this functionality but the syntax varies wildly so you will need to RTFM to find out how do do this with the databse you are using.

Answer 3

It's not clear to me why you are suggesting a link between updating a record in the parent table and its dependents in the child table. The point of having separate tables is precisely that we can modify the non-key columns in People without touching Likes.

When it comes to updating Likes there are two different business transactions. The first is when Dave says, "I didn't mean 'oranges' I meant to say I like flower arranging". Correcting a mistake would use an update:

update likes
set like = 'flower arranging'
where userid = 1
and like = 'oranges'
/

The WHERE clause could use the LIKES.ID column instead.

The other case is where the preferences have actually changed. That is, when Dave says "Now I'm 79 I don't like women any more. I have new tastes.". This might look like this:

delete from likes
where userid = 1
and like = 'women'
/
insert into likes (userid, like) 
values (1, 'dominoes')
/
insert into likes (userid, like) 
values (1, 'Werthers Orignals')
/

The difference between these two statements is primarily one of clarity. We could have implemented the second set of statements as an update and a single insert but that would be misleading. Keeping the distinction between meaningful changes to the data and correcting mistakes is a useful discipline. It is especially helpful when we are keeping historical records and/or auditing changes.

What is definitely a bad idea is deleting all Dave's Likes records and then re-inserting them. Your application should be able to track which records have changed.