I have a table named orders_products which hold all the products associated with each individual orders. Now, if the customer decides to edit the quantity, the attributes, or simply just remove a product, what would be the easiest way to handle this change in the database?
orders_products
id | quantity | fk_products_id | attributes | price | fk_orders_nr
fk_products_id refers to the actual product id in our products tableattributes is a comma sepparated string with id's refering to our attributes table (attributes can be the angle, lenght, diameter etc. for each product. As in 45 degres, left angeled, 20 cm in length, 43mm in diameter).fk_orders_nr is the actual order the product belongs to.
Since I have a class that handles this part on the client side. Would it be easier to just DELETE all associated products, based on the order id (fk_orders_nr), and just re-insert and updated the whole set based on what's stored in the class?
Or is there a neat way to handle this directly in MySQL?
I've looked a bit into on duplicate key update, and ignore, but they doesn't seem to fit my needs..
I need to check to see if the actual product id exists, then decide to insert new, or update existing (it could be both the quantity, and/or optional attributes), also the product might be removed if not in the list from the class anymore.
When writing this question. I think deleting the whole set, and reinsert it, might be the easiest way.
This database looks badly designed. Firstly I assume by fk_products_id you mean product_id. You do not need to specify that a column is a foreign key in its name.
Secondly, I would advise you to keep all columns atomic, as in, no multi-values. The attributes column keeping a comma-separated list will give you headaches in the future and it also breaks the FIRST normal form (the most basic one).
Thirdly, you don't need (although it could sometimes be useful) an id as a primary key in your junction table. You can just use a compound primary key from your fk_products_id and fk_orders_nr keys.
When writing this question. I think deleting the whole set, and reinsert it, might be the easiest way.
Yes, that is the way it's usually done.
But I insist you ignore everything about the current problem you're having and redesign your database from scratch, putting special attention into normalization. These are basic database design standards and they exist for a reason. Hopefully you won't learn about the reason when it's too late.