数据库设计-具有属性的多类别产品
我正在为供应商设计一个基本的库存系统。
他们有许多不同的产品类别。
每个产品类别都有许多不同的属性。
A - x1、x2、x3、a1、a2、a3；
B - x1、x2、x3、b1、b2、b3、b4；
C-x1、x2、x3、c1、c2；
Laptop - Make, Price, Quantity, Processor, OS, Hard drive, Memory, Video Card etc
Monitor - Make, Price, Quantity, Size, ContrastRatio, Resolution etc
Server - Make, Price, Quantity, Processor, OS, Memory, Netowrking etc
设计1：每个类别都有不同的表格。
Design2：公用表，属性表。
最好的方法是什么？
I am designing a basic inventory system for a vendor.
They have many different product categories.
Each product category has many different properties.
A - x1, x2, x3, a1, a2, a3;
B - x1, x2, x3, b1, b2, b3, b4;
C - x1, x2, x3, c1, c2;
Laptop - Make, Price, Quantity, Processor, OS, Hard drive, Memory, Video Card etc
Monitor - Make, Price, Quantity, Size, ContrastRatio, Resolution etc
Server - Make, Price, Quantity, Processor, OS, Memory, Netowrking etc
Design1: Different tables for each category.
Design2: Common Table, property table.
What is the best approach?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
绝对不想不必要地增加模式（奥卡姆规则，你知道）。安排多对多关系的标准方法只是一个中间表：
它易于查询，并且是行业最佳实践。
Definitely don't want to multiply schemas unnecessarily (Occam's rule, y'know). The standard way of arranging many-to-many relationships is simply an intermediate table:
Its straightforward to query and an industry best practice.
在不了解更多关于您的领域的情况下，我倾向于使用设计 2。这将限制表的数量，并使对多个类别的不同属性的查询更具可读性和效率。
如果您有少量静态类别并且每个类别都有不同的属性，则设计 1 值得考虑。如果是这种情况，您仍然可以通过创建属性字典表来使用设计 2。这里您将有一个包含属性键/属性名称对的表。然后您的类别表可以包含类别 id、属性 id、属性值列。如果所有属性都具有相同的数据类型，则此方法很有效，但如果不同，则可能会很尴尬。
Without knowing more about your domain, I'd be inclined to use design 2. This will limit the number of tables and make queries on the different properties of multiple categories much more readable and efficient.
Design 1 would be worth consideration if you have a small number of categories that are static and each have different properties. If this is the case, you could still use design 2 by creating a property dictionary table. Here you would have a table that contains property property key/property name pairs. Then your category table can contain category id, property id, property value columns. This works well if all the properties have the same data type but can get awkward if they do not.
采用设计 1，每个类别都有一个不同的表。
如果属性不都是相同的数据类型，设计 2 将会很痛苦。如果没有，它将迫使您将它们全部存储为字符串并编写大量转换代码。这也妨碍了简单的数据库级数据类型验证。
Go with Design 1, a different table for each category.
Design 2 is going to be a pain if the attributes are not all the same data type. If not, it will force you to store them all as strings and write a lot of casting code. This also prevents easy DB level data type validation.
使用第二个选项将需要每个查询都有一个联接，使用第一个选项将使查询变得更加困难，因为您将有许多表，而您现在必须决定要查询哪个表。我更喜欢第二种选择，因为从长远来看，应用程序开发更简单（但我可能很懒）。
Going with the second option will require a join for each query, going with the first option will make querying harder because you will have a number of tables and you now have to decide which one to query. I would prefer the second option because in the long run the application development is simpler (but I may be lazy).
通常的设计是最常见属性的通用表，然后是每个类别的子表，其中包含该类别的特殊属性。
或者，您可以使用实体值结构，但通常不推荐使用它，因为它很难查询并且不能很好地扩展。
您需要做的一件人们经常忘记的事情是将购买价格存储在库存中商品的记录中（我认为是常见属性之一）。如果您依赖产品表中的价格，它会随着价格变化而更新，但出于会计目的，这并不是为该商品支付的价格。当您接受审计或纳税时，这可能会非常糟糕！
Usual design is common table for the most common properties and then a child table for each category with the special properties for that category.
Alternately you can use entity value structure but it is not generally recommended as it is hard to query and does not scale well.
One thing you will need to do that people often forget is to store the price at purchase in the record for the item in inventory (one of what I would consider the common properties). If you rely on a price in a product table, it will get updated as the price changes, but for accounting purposes that is not what was paid for the item. This can be very bad when you are audited or at tax time!
“如果属性不都是相同的数据类型，设计 2 将很痛苦。”
如果属性不都是相同的数据类型，那么这些属性所表示的属性不可能是共同的。
"Design 2 is going to be a pain if the attributes are not all the same data type."
If the attributes are not all the same data type, then the properties such attributes represent cannot possibly be common.