问题描述
我正在构建一种将客户订单插入数据库的简单方法。
我们有几个产品，每个都需要不同的属性。
I'm building a simple way to insert customer orders into the db.We have several products, each one needs different properties.
我已经开始设计以下表格：
I've started designing the following tables:
CUSTOMER -> Order (FK to CUSTOMER) -> OrderItem (FK to Order)
现在我在想如何将产品特定的表链接到 OrderItem的即可。
假设我有两个产品： product1（room_name，width，height，color）和product2（number，width，height，type，optionals）。我会创建两个不同的表格，并将它们与 OrderItem 相链接，以获取具体的选项，我错了吗？ （当然会有两个以上的产品）
Now I'm thinking How could I link product-specific tables to OrderItem.Suppose I've two products: product1 (room_name, width, height, color) and product2 (number, width, height, type, optionals). I'd create two different tables and link them with the OrderItem, to get specific options, am I wrong? (of course there will be more than just two products)
我该怎么做？
推荐答案
这里有一些选项
-
IMHO我会选择一个继承模式， ProductBase与独特的代理。产品基础将具有分类例如ProductType，然后允许您加入相应的子类产品表。 OrderItem将仅引用代理。引用完整性是可执行的，它给予扩展到其他形式的产品的机会。然而，它需要在所有产品表类型中使用通用的唯一代理。如果有其他表（OrderItem除外）引用了Product，它也将避免使用FK来复合键。
IMHO I would choose an Inheritance pattern, i.e. a new table called "ProductBase" with a unique Surrogate. Product base would have a classification e.g. "ProductType" which would then allow you to join into the appropriate 'subclass' Product table. OrderItem would reference just the Surrogate. Referential Integrity is enforcable, and it gives the opportunity for extending to additional forms of products. It does however require the use of a common unique surrogate amongst all Product table types. If there are other tables (other than OrderItem) referencing Product, it would also avoid the use of having to FK to composite keys.
OrderItem中的可空的外键，即OrderItem将具有可空的FK到这两种（全部）类型的产品表，尽管它们中只有一个将出现在每一行上。
通过将OrderItem内部连接到相应的Product表将消除基于NULL的错误产品联接。 RI仍然可以被执行。
Nullable Foreign Keys in OrderItem, i.e. OrderItem would have nullable FK to both (all) types of Product Tables, although only one of them would be present on each row.By inner joining OrderItem to the appropriate Product tables would eliminate the 'wrong' product joins based on the NULLs. RI can still be enforced.
如果您在所有产品子类表上都有主键，则还可以添加单个外部 Order和ProductTypeSwitch在OrderItem上。这里的问题是您无法执行RI。
If you have the SAME type of Primary Key on all your Product subclass tables, then you could also add a single Product "Foreign" Key and a "ProductType" "Switch" on OrderItem. The problem here is that you can't enforce RI.
那就是说，我真的不会创建每个产品的新桌子 - 肯定有一些广泛的类别的产品，可以统一的模式。
That said, I really wouldn't be creating a new table for each and every product - surely there are some broad 'categories' of Product which can be modelled in a uniform manner.
毫无疑问，如果你卖飞机和杂货您可能需要一个飞机产品（EMX）和一个杂货产品，但肯定是A300 ，波音747和西斯纳 Skyhawk 将适合于 AircraftProduct 内的行，即使每个表格中有几个可选可空字段不适用于此类别中的所有产品？
No doubt if you sell Aircraft and Groceries that you would probably need a AircraftProduct and a GroceryProduct, but surely A300, Boeing 747 and Cessna Skyhawk would fit as rows inside AircraftProduct, even if there are a few 'optional' nullable fields in each table not applicable to all products in this 'category'?
编辑：首先参见Dems和Duffmo的帖子，看看是否可以通过使用EAV /多值/元数据模式来模拟多个Product表，产品。
Edit : First see Dems and Duffmo's posts to see if you can avoid the requirement for having multiple Product tables at all, by using EAV / Multivalue / Metadata patterns to model Product.
这篇关于DB设计为每个客户订单存储不同的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！