Is it OK to store redundant data in case records from foreign table are deleted

Question

Let's say I have a database table called products which has a list of products, with the primary key product_id

I then have a database table called purchase_order_products which has a list of products assigned to a purchase order, with a foreign key product_id.

Now, if I enforce referential integrity between the two tables, it only requires a single purchase order to reference a product, and it won't be possible to ever delete that particular product from the database (unless the purchase orders for that product are also deleted).

It seems I have a few options:

1) Enforce referential integrity and don't allow the product to ever be deleted.

2) Don't enforce referential integrity, and if anyone ever views a purchase order where the product no longer exists, simply display the product name as "UNKNOWN" or "DELETED".

3) The final option is to not only store the product name in the products table but also store it in the purchase_order_products table alongside the foreign key. Obviously this is redundant data, but it would allow the product to be deleted from the products table, whilst still allowing users to see the names of now non-existent products that were part of purchase orders in the past.

I'm swaying towards option #3 but wondered what is the "correct" way of handling this.

Answer 1

You can enforce referential integrity and use ON DELETE SET NULL, then display "UNKNOWN" or "DELETED" when a purchase order's product_id is null. Thus, option 1 and 2 aren't mutually exclusive.

Option 3 is valid. Having two copies of product_name isn't redundant if the relations they're used in express different predicates. Product <x>'s current name is <y> is different from When purchase_order <z> was created, product <x>'s name was <y>. It's a common technique to record current and historical prices separately, the same can be done for names or any other attributes of a product.

Answer 2

There is no reason to duplicate data. A simple solution is to implement a soft delete on the products. The best way is to have a date field called something appropriate like Deleted and set it to a date far in the future, like 12/31/9999, for current products. To delete a product, just set the Deleted value to the date the product is deleted. This way, to list currently available products, filter out the products where Deleted is in the past.

When showing purchase orders, ignore the Deleted value so it shows all products, even the ones no longer available. Optionally, you could show by some indicator if a product is one that is no longer available.

You might also want to create a view that ignores deleted products for those times in would not be appropriate to show deleted products, as when creating new purchase orders.

You would also want to write a delete trigger on the products table to convert the delete process to just change the value in the Deleted field. You would also want to have a function in the API to allow a product to be "deleted" as of a certain date. Maybe the product was removed a month ago but the database was not updated. Or the product is slated to be removed at a future date so go ahead and set the date. The product will simply disappear from the current products view when that date is reached.