数据库设计——一张表多字段、多表一字段还是抽象表？
我必须设计一个架构来存储具有许多属性但共同点很少的对象。
我在这里找到了一些解决方案，但我仍然不确定最好的做法。我看到有四种方法：
- 一张表包含许多字段：这可能会导致许多
NULL值，并且当需要添加一些属性或修改一些数据类型时会遇到困难。 - 为每个属性创建一个新表：这使得添加和更新列变得容易，并保留搜索功能，尽管每个
SELECT都会导致大量JOIN。 - 为每个类型属性创建一个表，例如：标签、数量、间隔等。我不确定在这种情况下是否需要区分浮点数、小数、整数等。
- 创建抽象表（我在这里读到它称为观察模式），它存储属性名称和数据类型。
我应该遵循哪些标准，应该回答哪些问题才能在这些解决方案之间进行选择？
谢谢
I have to design a schema to store objects that have many properties, but few in common.
I found some solutions here, but I'm still not convinced about the best thing to do. I see four ways of doing it :
- One table with many fields : this may lead to many
NULLvalues, and a hard time when the need comes to add some properties or modifiy some datatypes. - Creating a new table for each property : this makes adding and updating columns easy, and preserves the searching capabilities, though each
SELECTwill result in lots ofJOIN. - Creating a table for each type of property, eg: tags, quantities, intervals, etc. I'm not sure in that case if I need to distinguish floats, decimals, integers, etc.
- Creating abstract tables (I read here it's called Observation Pattern), that store the properties name and datatype.
Which criteria should I follow, which questions should I answer to choose between those solutions ?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我想说这取决于您正在使用的 ORM 技术以及对象的序列化功能。
一般来说，我更喜欢抽象和灵活性。
I would say that this depends on the ORM technology you are using and the serialization capabilities of your objects.
Generally, I prefer abstraction and flexibility.
这取决于您需要如何处理这些属性。如果这些属性只是间接感兴趣，那么 4 是一个非常灵活和紧凑的解决方案。间接利益是什么意思？如果您需要检索并显示属性中的信息，这是间接的。如果您需要对属性进行计算或详细操作，那么这更直接。换句话说，如果您可能对属性使用的唯一方法是“.ToString()”，那么您可以使用 4。此模式的显着优点是允许您添加新的属性类型而无需更改数据库架构，因为这些只是插入到属性类型表中的新行。
另一方面，如果不同的属性类型需要以取决于其数据类型的方式进行操作，那么将不同类型的属性存储在一个字段中将是一件痛苦的事情。如果是这种情况，您可以执行 3.，但这也是一个问题，因为它要求您知道要转到哪个表来获取特定类型的值。这不是一个无法克服的问题，但也并不优雅。
您可以尝试一种混合方法，而不是 3.，其中单个属性表具有多个列，每个数据类型对应一个列，最好还有一个其他列（可能是计算列），充当“ToString”。这样，您的间接使用就可以转到简单、可预测的位置，并且只有更复杂的应用程序需要担心针对特定属性类型应转到哪一列。
It depends on what you need to do with the properties. If the properties are of only indirect interest, then 4 is a very flexible and compact solution. What do I mean by an indirect interest? If you need to retrieve and display the information in the properties this is indirect. If you need to do calculations or detailed manipulations with the properties then this is more direct. In other words, if the only method you're likely to use on the properties is ".ToString()" then you can get away with 4. This schema has the significant advantage of allowing you to add new property types without changing your database schema, since these are just new row inserts into your property type table.
If on the other hand, the different property types need to be manipulated in ways which depend on their data types, then it will be a pain to store the different types of properties in one field. If this is the case you could do 3., but this is also a problem because it requires you to know which table to go to to get a value of a particular type. This isn't an insurmountable problem but it isn't elegant either.
Instead of 3. you could try a sort of hybrid aproach where your single property table has multiple columns, one for each data type, and preferably one other column - maybe a calculated column - that acts as a "ToString". This way your indirect uses can go to the simple, predictable spot and only your more involved applications need to worry about which column to go to for a particular property type.