java - How to modify related tables

Question

Attribute table attr
Product table item
Association table item_attr

Attribute table fields
cat_id、title

Product table fields
item_id、title

Product table table fields
item_attr_id、item_id、attr_id

For example, there are 5 records of attributes, cat_id are 1, 2, 3, 4, 5
Add a product and select attributes 2 and 5

Then the association table should be two records. If you want to modify it now, for example, remove 5 and add 4, this is generally the case.

Now I get the original old attr_id first, and then search one by one to see if the new attr_id is there. If not, delete it and recycle the new attr_id Search one by one to see if there is any attr_id in it. If it is skipped, add

if not.

Is there a better way? At first, my idea was to delete them all and then add new ones. Isn’t this unscientific?

Answer 1

The association table should be the inventory table. One product + one attribute defines an inventory. What you said above is that after traversing and comparing, if there are changes, modify them, and if there are no changes, delete them. This is correct.

Answer 2

What you said later is scientific, but what you said before is too complicated, so it is unscientific.
First delete all based on item_id in the association table, and then add all
delete from item_attr where item_id = 1;
insert into item_attr (item_id, attr_id) values ​​(1, 1), (1, 2), (1, 2);
This is the most efficient

Answer 3

Delete them all and add them all based on the latest ones. Simple logic and best efficiency