Let's say I have a database table called products which has a list of products, with the primary key product_id
假设我有一个名为products的数据库表,其中包含产品列表,主键为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.
然后,我有一个名为purchase_order_products的数据库表,其中包含分配给采购订单的产品列表,其中包含外键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.
1)实施参照完整性,不允许删除产品。
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".
2)不强制参照完整性,如果有人查看产品不再存在的采购订单,只需将产品名称显示为“未知”或“已删除”。
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.
3)最后一个选项是不仅将产品名称存储在products表中,还将其与外键一起存储在purchase_order_products表中。显然这是冗余数据,但它允许从产品表中删除产品,同时仍允许用户查看过去作为采购订单一部分的现在不存在的产品的名称。
I'm swaying towards option #3 but wondered what is the "correct" way of handling this.
我正在朝着选项#3摇摆不定,但想知道处理这个问题的“正确”方法是什么。
2 个解决方案
#1
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.
您可以强制执行参照完整性并使用ON DELETE SET NULL,然后在采购订单的product_id为空时显示“UNKNOWN”或“DELETED”。因此,选项1和2不是相互排斥的。
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.
选项3有效。如果他们使用的关系表达不同的谓词,那么拥有product_name的两个副本并不是多余的。 Product
#2
1
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.
没有理由重复数据。一个简单的解决方案是在产品上实现软删除。最好的方法是让一个称为适当的日期字段,如Deleted,并将其设置为将来的日期,如12/31/9999,对于当前产品。要删除产品,只需将“已删除”值设置为删除产品的日期。这样,要列出当前可用的产品,请过滤掉过去已删除的产品。
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.
您还希望在products表上编写一个删除触发器,以将删除过程转换为仅更改Deleted字段中的值。您还希望在API中具有一个功能,以允许在特定日期之前“删除”产品。也许该产品在一个月前被删除了,但数据库没有更新。或者产品将在未来日期删除,因此请继续并设置日期。达到该日期时,产品将从当前产品视图中消失。
#1
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.
您可以强制执行参照完整性并使用ON DELETE SET NULL,然后在采购订单的product_id为空时显示“UNKNOWN”或“DELETED”。因此,选项1和2不是相互排斥的。
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.
选项3有效。如果他们使用的关系表达不同的谓词,那么拥有product_name的两个副本并不是多余的。 Product
#2
1
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.
没有理由重复数据。一个简单的解决方案是在产品上实现软删除。最好的方法是让一个称为适当的日期字段,如Deleted,并将其设置为将来的日期,如12/31/9999,对于当前产品。要删除产品,只需将“已删除”值设置为删除产品的日期。这样,要列出当前可用的产品,请过滤掉过去已删除的产品。
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.
您还希望在products表上编写一个删除触发器,以将删除过程转换为仅更改Deleted字段中的值。您还希望在API中具有一个功能,以允许在特定日期之前“删除”产品。也许该产品在一个月前被删除了,但数据库没有更新。或者产品将在未来日期删除,因此请继续并设置日期。达到该日期时,产品将从当前产品视图中消失。