产品属性的数据库架构
和很多人一样，我正在寻找产品/产品属性数据库架构。我正在使用 Ruby on Rails 和（Thinking）Sphinx 进行多面搜索。
要求：
- 添加新产品类型及其选项不需要更改数据库架构
- 支持使用 Sphinx 进行分面搜索。
我遇到的解决方案：
（查看 Bill Karwin 的回答< /a>)
选项 1：单表继承
确实不是一个选项。该表将包含许多列。
选项 2：类表继承
Ruby on Rails 在启动时缓存数据库模式，这意味着每当引入新类型的产品时都会重新启动。如果您有足够大的产品目录，这可能意味着数百个表。
选项 3：序列化 LOB
无法在没有大量应用程序逻辑的情况下进行分面搜索。
选项 4：实体-属性-值
出于测试目的，EAV 运行良好。然而，当您添加越来越多的选项（例如，当选项增加价格或交货时间时），它可能很快就会变得混乱和维护地狱。
我应该选择什么选项？还有哪些其他解决方案？有没有我忽略的灵丹妙药（哈）？
As so many people I'm looking for a Products /Product Properties database schema. I'm using Ruby on Rails and (Thinking) Sphinx for faceted searches.
Requirements:
- Adding new product types and their options should not require a change to the database schema
- Support faceted searches using Sphinx.
Solutions I've come across:
(See Bill Karwin's answer)
Option 1: Single Table Inheritance
Not an option really. The table would contain way to many columns.
Option 2: Class Table Inheritance
Ruby on Rails caches the database schema on start-up which means a restart whenever a new type of product is introduced. If you have a size able product catalog this could mean hundreds of tables.
Option 3: Serialized LOB
Kills being able to do faceted searches without heavy application logic.
Option 4: Entity-Attribute-Value
For testing purposes, EAV worked fine. However it could quickly become a mess and a maintenance hell as you add more and more options (e.g. when an option increase the prices or delivery time).
What option should I go with? What other solutions are out there? Is there a silver bullet (ha) I overlooked?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
IMO，类表继承和 EAV 的结合以及一些严格的限制将是最好的方法。
EAV 就像药物：少量且在少数情况下，它们可能是有益的；太多会杀了你。作为架构主要部分的 EAV 将失败。毫无疑问。但是，作为良好数据库模式的补充，如果您实施适当的限制，它们会很有用。
EAV 的主要规则是您永远不能编写包含
'[AttributeCol] = 'Attribute'的查询。换句话说，您永远无法过滤、排序、限制范围，也无法将特定属性放置在报表或表单上的任何位置。它只是一包数据，可以完全在报告或屏幕上的列表中吐出。事实上，我见过人们将此功能实现为 Xml 列。如果您能够维持此限制，那么您可以将 EAV 结构添加到类表继承设计中，作为允许用户仅出于存储目的向产品添加一组属性的方法。当他们想要使用某个属性执行任何禁止的任务时，它必须成为一流的列以及所需的一切。
关键在于执行。如果您认为自己无法合理地强制执行或让其他开发人员强制执行对 EAV 结构的使用的此限制，那么跳过它可能是有意义的。但是，如果您可以强制执行此限制，则可以解决人们仅出于存储和跟踪目的而添加大量属性的问题。
IMO, a combination of a class table inheritance and EAV with some serious restrictions would be the best approach.
EAVs are like drugs: in small quantities and a narrow set of circumstances, they can be benefical; too much will kill you. EAVs as the main portion of the schema will fail. No question. However, as a supplement to a good database schema they can be useful if you enforce proper restrictions.
The main rule with an EAV is that you can never write a query that includes
'[AttributeCol] = 'Attribute'. In other words, you can never filter, sort, restrict in range nor place a specific attribute anywhere on a report or form. It is just a bag of data that can be spit out entirely on a report or onscreen in a list. In fact, I have seen people implement this feature as an Xml column.If you are able to maintain this restriction, then you can add an EAV structure to a class table inheritance design as a means of allowing users to add a set of attributes to a product merely for storage purposes. The moment they want to do any of the verboten tasks with an attribute, it must become a first class column and all that entails.
The key is in enforcement. If you do not feel that you can reasonably enforce or have other developers enforce this limitation on the use of the EAV structure, then it might make sense to skip it. However, if you can enforce this limitation, it can solve the problem of people wanting to add tons of attributes merely for storage and tracking purposes.