具有额外属性的记录：稀疏表还是 EAV？
我有一个模型，已经有几十列，大部分时间都会被填充。现在我需要添加每次可能不同的字段。
最好的方法是什么？我不喜欢 EAV 模式。我也不喜欢稀疏表的想法，特别是考虑到这些额外的属性可能非常不同。
示例：
WorkOrder:
PK id
FK assigned_to
FK contractor
DATE expected_completion
DATE actual_completion
... (many more)
现在我想添加如下属性：
ep_1 (extra_property)
ep_2
ep_3
ep_4
... (many more)
这些额外的属性可能因记录而异，大多数时候它们的数量有限，但不能保证。
将记录视为：
id | assigned_to | contractor | ... | ep_1 | ep_2 | ep_3 | ... | ep_n
1 | 2 | 3 | ... | XYZ | NULL | NULL | ... | 23
2 | 3 | 5 | ... | NULL | 1 | NULL | ... | NULL
3 | 2 | 1 | ... | NULL | 0 | NULL | ... | NULL
4 | 4 | 1 | ... | XYZ | NUL | NULL | ... | 45
我希望能够列出、过滤和搜索记录，就好像这些额外属性实际上是列一样，例如：我应该能够进行诸如 SELECT fields FROM table WHERE ep_n > 之类的查询20 和 SELECT fields FROM table WHERE ep_1='ABC'
对此的最佳解决方案是什么？
I have a model that already has a couple dozen of columns that will be filled most of the time. Now I need to add fields that might be different each time.
what's the best approach? I don't like the EAV pattern. I don't like the idea of having a sparse table either, especially considering how these extra properties could be very different.
Example:
WorkOrder:
PK id
FK assigned_to
FK contractor
DATE expected_completion
DATE actual_completion
... (many more)
Now I want to add properties like:
ep_1 (extra_property)
ep_2
ep_3
ep_4
... (many more)
These extra properties can be wildly different from record to record, and most of the time there will be a limited number of them, but there are no guarantees.
Think of records as:
id | assigned_to | contractor | ... | ep_1 | ep_2 | ep_3 | ... | ep_n
1 | 2 | 3 | ... | XYZ | NULL | NULL | ... | 23
2 | 3 | 5 | ... | NULL | 1 | NULL | ... | NULL
3 | 2 | 1 | ... | NULL | 0 | NULL | ... | NULL
4 | 4 | 1 | ... | XYZ | NUL | NULL | ... | 45
I want to be able to list, filter, and search records as if those extra properties were actually columns, eg: I should be able to make queries like SELECT fields FROM table WHERE ep_n > 20 and SELECT fields FROM table WHERE ep_1='ABC'
What's the best solution to this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
什么数据库？以 SQL Server 为例，您可以考虑使用 稀疏列针对稀疏表进行了优化。对于 EAV 建模，我建议阅读 SQL Server 客户顾问团队关于该主题的白皮书：用于性能和可扩展性的语义数据建模的最佳实践。许多建议也适用于其他供应商，而不是特定于 SQL Server。
What database? With SQL Server for instance, you can consider using Sparse Columns which are optimized for sparse tables. For EAV modeling I recommend reading the whitepaper on the subject from the SQL Server customer adviser team: Best Practices for Semantic Data Modeling for Performance and Scalability. Many of the recommendations apply to other vendors too, are not SQL Server specific.