I really hope someone more SQL smart might help me. Imagine that the following table and associations they are compressed.
orders(PK_refno,customer, status) order_accessories(PK_refno,PK_acc) accessories(PK_acc,name,desc)
As you can tell this can be a typical
1:*----*:*----*:1 scenario the the problem or my problem is when upgrading, because the add-ons that every order has could be modified, and therefore an user can also addOreliminate add-ons.
The only method I have thought to get it done by utilizing MySQL would be to remove all add-ons after which place the up-to-date ones.
I dislike it by doing this. When I believe that most likely there is a SQL method of doing it. Maybe someone can suggest and advanced query (which I'll study obviously)
Another way I figured ended up being to:
I am not keen on this either because it might be completed in the application, not within the database.
Among the finest to include which i went for first removing all the records that matched up an order to become up-to-date after which re-placed they.
Let us the table begins such as this.
order_accessories PK_refno PK_acc 1 73 1 74 1 75 1 86 1 92
Let us also state that 75 should be 76. Presuming a sane interface, the consumer can just change 75 to 76. A sane interface would send this statement towards the dbms.
update order_accessories set PK_acc = 76 where (PK_refno = 1 and PK_acc = 75);
If 75 weren't said to be there to begin with, then your user would certainly remove that certain row. A sane interface would send this statement towards the dbms.
delete from order_accessories where (PK_refno = 1 and PK_acc = 75);