Best practice for dealing with deleted table rows?

Question

Here is my situation. I have the following tables:

• Product
• Product Attribute
• Order Product (references a product_id and an order_id)
• Order Product Attribute (references an order_product and a product_attribute)

When an admin goes in to edit a product attribute (for instance "color"), he may delete that attribute by mistake and later add it back. If orders have already been placed for that product, when he deletes and re-adds the attribute, its ID in the Product Attribute table changes. This make it so that the Order Product Attribute references a non-existent attribute ID (though the attribute does still exist).

What is the best way to go about solving this problem? Maybe I'm just in need of a different perspective on the issue.

[Note: the issue could also occur (just as importantly) if when editing the product, all of the attributes are flushed and the ones currently selected, which may be the same, are re-added.]

Answer 1

Add a trigger to table to prevent deletes for in use attributes?

OR

Add a deleted flag to that table and set the flag rather than actually deleting the row. Add IsDeleted = False to the where clause of any queries that access the attribute table for selecting.

Answer 2

Don't allow 'deleting by mistake'... just have an 'active' or 'inactive' flag as a column in the product attribute table.

You can always save the attributes with the order instead of the attribute id. If your design is such that the attribute ids continually change, then your orders have no historical validity. Either make new 'versions' of the product and set the old version to inactive when changes are made or save the state of the product in the order so that you have some historical record.

Answer 3

Use innodb as your table type and add proper foreign key relationships to your dependent tables. Then, you won't be able to accidentally delete rows upon which you depend.

Of course, if you still want (in some circumstances) to allow this, you can set foreign_key_ checks to off.