我应该使用EAV模型吗？ [英] Should I use EAV model?
问题描述
我正在设计一个电子商务应用程序的数据库/域名，我很难想出如何存储产品。
I'm am designing my database/domain for an eCommerce application and I'm having a hard time figuring out how to store products.
该网站将出售各种产品，笔，皮带，纹身，雨伞等等。这些产品中的每一个将共享几个共同的属性，高度，宽度，长度，重量等，但一些产品有特殊的数据。例如，笔具有不同的墨水颜色，并且提示/盖子和手册可以具有不同类型的折叠。到目前为止，我已经考虑了大约20多个额外的属性，但这些属性可能只适用于网站上的1％的产品。
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.
所以我想知道是否合适实施EAV模型来处理额外的数据。请记住，当客户在前台查看该网站时，会有一个过滤侧边栏，如eBay和carsales.com.au。 （所以记住会有一些查询）
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)
我认为实现Class Table继承是不切实际的，因为系统需要保持灵活性。这是因为，在轨道上，我们可能会在将来有更多的属性与新类型的产品。
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.
另一件事我考虑使用NoSQL数据库（可能是MongoDB ）但是我对这些类型的数据库没有什么经验，甚至可以解决我的问题吗？
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?
选项审核：
- 具有大量列的单一产品实体
- 分离属性实体（EAV）
- 切换到无模式持久性
我正在使用属性实体构建一个原型来查看它的灵活性，并测试性能，查询获得的失控程度如何。
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.
编辑：我当然可以使用任何其他解决方案。
I am, of course, open to any other solutions.
推荐答案
很好的问题，但当然没有一个真正的方式。根据@BenV，Magento使用EAV模型。我的经验是绝对积极的，但它确实跳过其他用户。一些注意事项：
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。性能
EAV需要复杂的多表连接才能使用相关属性来填充对象。这确实引起了一场表演。然而，可以通过仔细缓存（通过堆栈的所有级别，包括查询缓存）和选择性使用非规范化来缓解这种情况。 Magento允许管理员为其中SKU的数量保证的类别和产品选择非规格化模型（通常以千为单位）。这反过来需要观察者触发重新索引（总是好！）和更新到平面非规范化表，当产品数据更改。也可以安排或手动触发管理员的提示。
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。第三方用户复杂性
如果您打算将此应用程序提供给其他用户，许多人会发现EAV太复杂，您最终会在用户论坛上处理很多猥亵和不知情的滥用行为（ref Magento !!）。
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。未来的可扩展性和插件架构。
毫无疑问，EAV模型在扩展性是一个因素时，真的是自己的。在模型中添加新属性非常简单，同时最小化破坏现有ORM和控制器代码的风险。
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。数据类型的更改
EAV确实使更改属性数据类型变得更加困难。如果您的初始设计要求将来更改的特定属性数据类型（例如 int 至 varchar ），则表示您将必须将该属性的所有记录迁移到与新数据类型匹配的相应表。当然，纯粹主义者会建议你第一次获得设计，但现实有时会侵犯你！
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 int to varchar), 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。手工产品导入
EAV几乎不可能使用SQL和/或phpMyAdmin样式的CSV / XML将产品（或其他实体）导入数据库。您需要编写一个接受结构化数据并将其传递到应用程序的Model层的Importer模块，以将其持久化到数据库。这增加了你的复杂性。
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.
这篇关于我应该使用EAV模型吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
