我应该使用 EAV 模型吗？
我正在为电子商务应用程序设计数据库/域，但我很难弄清楚如何存储产品。
该网站将销售各种产品，钢笔、丁字裤、纹身、雨伞，应有尽有。这些产品中的每一个都会共享一些共同的属性，高度、宽度、长度、重量等，但有些产品有特殊的数据。例如，钢笔有不同的墨水颜色，笔尖/笔盖和小册子可以有不同类型的折叠。到目前为止，我已经想出了一些 20 多个额外属性，但这些属性可能只适用于网站上 1% 的产品。
所以我想知道是否适合实现 EAV 模型来处理额外的数据。请记住，当客户在前端查看该网站时，将会有一个过滤侧边栏，就像 eBay 和 carsales.com.au 上一样。 （因此请记住，将会有相当多的查询）
我认为实现类表继承并不实际，因为系统需要保持灵活性。这是因为，未来我们可能会通过新型产品拥有更多属性。
我考虑过的另一件事是使用 NoSQL 数据库（可能是 MongoDB），但是我对这些类型的数据库缺乏经验，它能解决我的问题吗？
选项审查：
- 具有大量列的单一产品实体
- 单独的属性实体 (EAV)
- 切换到无模式持久性
我正在使用属性实体构建原型，以了解它的灵活性，并测试性能和如何使用查询失去控制。
编辑：当然，我对任何其他解决方案持开放态度。
I'm am designing my database/domain for an eCommerce application and I'm having a hard time figuring out how to store products.
The website will sell a wide range of products, pens, thongs, tattoos, umbrellas, everything. Each of these product will share a few common attributes, height, width, length, weight, etc but some products have special data. For example, pens have different ink colors and tips/lids and brochures can have different types of folds. So far I have thought up some 20+ extra attributes, but these attributes may only apply to 1% of products on the website.
So I am wondering if it is appropriate to implement a EAV model to handle the extra data. Keeping in mind that when customers are viewing the site in the frontend, there will be a filtering sidebar like on eBay and carsales.com.au. (So keeping in mind there will be a fair bit of querying)
I don't think it's practical to implement Class Table inheritance as the system needs to remain flexible. This is because, down the track we may have more attributes in the future with new types of products.
The other thing I have considered is using a NoSQL database (probably MongoDB) however I have little experience with these types of databases, will it even solve my problem?
Review of options:
- Single products entity with lots of columns
- Separate attributes entity (EAV)
- Switch to schema-less persistence
I'm in the process of building a prototype with an attributes entity to see how flexible it is, and testing the performance and how out of control the querying gets.
EDIT: I am, of course, open to any other solutions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
很好的问题，但是当然，没有“唯一正确的方法”。根据@BenV，Magento 确实使用 EAV 模型。我对它的体验非常积极，但它确实让其他用户感到困惑。一些注意事项：
1。性能。
EAV 需要复杂的多表连接来使用相关属性填充对象。这确实会导致性能下降。但是，可以通过仔细的缓存（在堆栈的所有级别，包括查询缓存）和选择性使用非规范化来缓解这种情况。 Magento 确实允许管理员为 SKU 数量（通常为数千）的类别和产品选择非规范化模型。这反过来又需要观察者在产品数据发生变化时触发重新索引（总是好的！）并更新“平面”非规范化表。也可以通过向管理员提示来安排或手动触发。
2.第三方用户复杂性
如果您打算向其他用户提供此应用程序，许多人会发现 EAV 太复杂，并且您最终会在用户论坛上处理大量的抱怨和不知情的滥用行为（参考 Magento！！）。
3.未来的可扩展性和插件架构。
毫无疑问，当可扩展性成为一个因素时，EAV 模型才真正发挥作用。在模型中添加新属性非常简单，同时最大限度地降低破坏现有 ORM 和控制器代码的风险。
4.数据类型的变化
EAV 确实使更改属性数据类型变得更加困难。如果您的初始设计需要将来更改的特定属性数据类型（例如将
int更改为varchar），则意味着您必须将该属性的所有记录迁移到与新数据类型匹配的相应表。当然，纯粹主义者会建议您第一次就把设计做好，但现实有时确实会干扰您！5.手动产品导入
EAV 几乎不可能完成的一件事是使用 SQL 和/或 phpMyAdmin 样式的 CSV/XML 将产品（或其他实体）导入数据库。您需要编写一个导入器模块来接受结构化数据并将其传递到应用程序的模型层以将其保存到数据库中。这确实增加了你的复杂性。
Great question, but of course, there is no "one true way". As per @BenV, Magento does use the EAV model. My experience with it has been overwhelmingly positive, however it does trip up other users. Some considerations:
1. Performance.
EAV requires complex, multi-table joins to populate your object with the relevant attributes. That does incur a performance hit. However, that can be mitigated through careful caching (at all levels through the stack, including query caching) and the selective use of denormalization. Magento does allow administrators to select a denormalized model for categories and products where the number of SKUs warrants it (generally in the thousands). That in turn requires Observers that trigger re-indexing (always good!) and updates to the "flat" denormalized tables when product data changes. That can also be scheduled or manually triggered with a prompt to the administrator.
2. 3rd Party User Complexity
If you ever plan to make this application available to other users, many will find EAV too complex and you'll end up dealing with a lot of bleating and uninformed abuse on the user forums (ref Magento!!).
3. Future extensibility and plugin architecture.
There is no doubt that the EAV model really comes into it's own when extensibility is a factor. It is very simple to add new attributes into the model while minimizing the risk of breaking existing ORM and controller code.
4. Changes in datatype
EAV does make it a little harder to alter attribute datatypes. If your initial design calls for a particular attribute datatype that changes in future (say
inttovarchar), it means that you will have to migrate all the records for that attribute to the corresponding table that matches the new datatype. Of course, purists would suggest that you get the design right first time, but reality does intrude sometimes!5. Manual product imports
One thing that EAV makes almost impossible is importing products (or other entities) into the database using SQL and/or phpMyAdmin-style CSV/XML. You'll need to write an Importer module that accepts the structured data and passes it through the application's Model layer to persist it to the database. That does add to your complexity.
开源购物车 Magento 允许使用 EAV 设计为其产品自定义属性。您可以在此处查看他们的数据库架构。
The open source shopping cart Magento allows custom attributes for their products using an EAV design. You can check out their database schema here.
我建议您仔细研究带有 OXM 插件的 Doctrine 2 ORM (https://github.com/doctrine/oxm)。它将用不同的属性解决您的问题。当然，您将需要为可搜索的自定义属性构建索引，但我认为这不会成为问题:)
如果您不关心社区成员的数量，那么您也可以使用 MongoDB。
I would suggest you to look closer on Doctrine 2 ORM with OXM plugin for it (https://github.com/doctrine/oxm). It will solve your problem with different attributes. Of course you will be required to build indexes for searchable custom attributes, but I don't think it will be a problem :)
If you don't care about number of community members, then you can use MongoDB as well.