良好的数据库设计，可变数量的属性 [英] Good database design, variable number of attributes
问题描述
我正在尝试创建一个包含设备列表的数据库。所有设备将具有某些公共属性（例如制造商，型号＃，序列号等），然后存在特定于某一设备的其他属性（即，调制解调器将具有访问＃，而太阳能电池板将具有输出容量）。我不知道如何用好的数据库设计原则来表示这些变化的属性，我试过搜索网络，但我不完全确定要搜索什么。
I'm trying to create a database that contains a list of equipment. All of the equipment will have certain common attributes (such as manufacturer, model #, serial #, etc.), then there are other attributes that are specific to a certain piece of equipment (ie, a modem will have an access #, whereas a solar panel will have an output capacity). I'm not sure how to represent these changing attributes with good database design principles, I've tried searching the web, but I'm not entirely sure what to search for.
我想出了以下可能的解决方案和我对他们的初步想法：
I've come up with the following possible solutions and my initial thoughts on them:
-
有一个大表，每个可能的属性，只是放在它不适用的空。显然这有一些缺陷。
Have one big table with every possible attribute and just put null where it's not applicable. Obviously this has some flaws.
每种设备类型都有一个单独的表格。这似乎可能是一个噩梦使用，如果我想打印所有设备的列表，我怎么知道哪些表查找？
Have a separate table for each equipment type. This seems like it might be a nightmare to use, if I want to print a list of all the equipment, how do I know which tables to lookup?
具有公共属性的表，以及使用外键访问的每个设备类型的其他表，以存储额外的属性。我可能做这个工作，但它会很麻烦，只是不觉得像一个很好的解决方案。
Have a table with the common attributes, and other tables for each equipment type accessed with a foreign key to store the extra attributes. I could probably make this work, but it would be cumbersome and just doesn't feel like a very good solution.
实体 - 属性值类型模型。只是不似乎是一个非常适合我想做的一个很好的。
An entity-attribute-value type model. Just doesn't seem like a very good fit for what I want to do.
我没有很多数据库的经验，所以我在学习，涉及这个问题或必须阅读关于数据库设计的文章将不胜感激。谢谢！
I don't have a lot of experience with databases so I'm learning as I go here, any links relating to this problem or "must read" articles on database design would be appreciated. Thanks!
编辑：
首先，我发现我需要Google继承映射，这可能有助于任何有类似问题的人。为了解决这个问题，我最终使用＃2和＃3的混合。它实际上很容易，工作良好，并解决了添加额外的设备类型的问题，而没有EAV的复杂性。感谢所有的意见和建议！
First off, I found out that I needed to Google "Inheritance mapping", that might help anyone else that has a similar question. To solve the problem I ended up using a hybrid of #2 and #3. It was actually pretty easy, works well, and solves the problem of adding additional equipment types without the complexity of EAV. Thanks for all the comments and suggestions!
推荐答案
选项1,2和3共享一个非常严重的缺陷：当有人梦想时，你必须修改基础表模式新属性。在选项1的情况下，问题由于引入新设备类型的可能性而变得更加复杂。你确定这套属性是固定的吗？您会如何满意您的服务中断或告诉客户您不能拥有新的属性？
Options 1, 2, and 3 share one very serious flaw: you have to modify the underlying table schema when someone dreams up a new attribute. In the case of Option 1 the problem is compounded by the possibility that a new equipment type will be introduced. How sure are you that the set of attributes is fixed for all time? How happy will you be to take outages or tell the client that no, you can't have a new attribute?
如果您很可能查询常见属性，你可能尝试3和4的混合，带有一个破折号2分割属性类型，而不是设备类型，这似乎更加不稳定。选项4，如果我理解正确，是一个正常形式版本的选项1，它解决了所有其固有的问题（稀疏和脆弱）。
If you are very likely to do queries off common attributes, you might try a hybrid of 3 and 4, with a dash of 2 thrown in splitting on attribute type rather than equipment type, which seems much more volatile. Option 4, if I understand correctly, is a normal form version of option 1 which solves all its inherent problems (sparseness and brittleness).
INVENTORY( id*, model, manufacturer, serial )
ATTRIBUTE( id*, name, type, description )
INVENTORY_FACT_STRING( inv_id*, attr_id*, value )
INVENTORY_FACT_NUMBER( inv_id*, attr_id*, value )
INVENTORY_FACT_LIST_STRING( inv_id*, attr_id*, ordinal*, value )
等。
这篇关于良好的数据库设计，可变数量的属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
