Allows say I've got a database like the following:

Table: People

id | name | age
 1 | dave | 78

Table: Likes

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

An amount be the easiest method to handle upgrading daves data? Presently, I actually do the next:

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 remove all of the customers data in the table after which readd their new stuff. This appears inefficient. It is possible to better way?

It isn't obvious in my experience your reason for recommending a hyperlink between upgrading an archive within the parent table and it is loved ones within the child table. The purpose of getting separate tables is strictly that people can customize the non-key posts in People without touching Likes.

If this involves upgrading Likes you will find two different transactions. The very first is when Dork states, "I did not mean 'oranges' I designed to say I love flower organizing". Fixing an error would make use of an update:

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

The WHERE clause can use the LIKES.ID column rather.

Another situation is how the preferences have really transformed. That's, when Dork states "Now I am 79 I do not like women anymore. I've new tastes.". This may seem 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 main difference between both of these claims is mainly among clearness. We're able to have implemented the 2nd group of claims being an update along with a single place but that might be misleading. Keeping the excellence between significant changes towards the data and fixing mistakes is really a helpful discipline. It's especially useful if we are keeping historic records and/or auditing changes.

What's certainly an awful idea is removing all Dave's Likes records after which re-placing them. The application should have the ability to track which records have transformed.

With respect to the DBMS you could utilize "MERGE" "REPLACE" "Place OR REPLACE", most DBMSes support this functionality however the syntax varies extremely so you will have to RTFM to discover how can do that using the databse you're using.

You could include a brand new column to Likes storing the particular age. Using this method you receive a history funtionality and you will determine his likes by asking for individuals using the greatest age like:

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

When you wish have the ability to override some Loves also could add another column "overriden_by" that contains the id from the new Like. This could create a simpler query:

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