实体属性值数据库与严格的关系模型电子商务 [英] Entity Attribute Value Database vs. strict Relational Model Ecommerce

问题描述

可以肯定地说，EAV/CR 数据库模型不好.也就是说，

问题:应该使用什么数据库模型、技术或模式来处理描述可在运行时更改的电子商务产品的属性类"?

在一个好的电子商务数据库中，您将存储选项类别(例如电视分辨率，然后为每台电视设置一个分辨率，但下一个产品可能不是电视，也没有电视分辨率").您如何存储它们、高效搜索并允许您的用户使用描述其产品的可变字段来设置产品类型?如果搜索引擎发现客户通常根据控制台深度搜索电视，您可以将控制台深度添加到您的字段中，然后在运行时为每种电视产品类型添加一个深度.

优秀的电子商务应用程序有一个很好的共同特征，它们显示一组产品，然后有向下钻取"侧边菜单，您可以在其中看到电视分辨率"作为标题，以及前五名最常见的电视搜索结果的分辨率.您单击一个，它只会显示该分辨率的电视，允许您通过在侧边菜单中选择其他类别来进一步深入了解.这些选项是在运行时添加的动态产品属性.<​​/p>

进一步讨论:

长话短说，互联网上是否有任何链接或模型描述可以从学术上"修复以下设置?我感谢诺埃尔肯尼迪建议一个类别表，但需要可能比那更大.我在下面以不同的方式描述它，试图突出其重要性.我可能需要进行视点校正来解决问题，或者我可能需要更深入地研究 EAV/CR.

喜欢对 EAV/CR 模型的积极响应.我的开发人员同事都说了 Jeffrey Kemp 在下面提到的内容:新实体必须由专业人士建模和设计"(断章取意，阅读下面的回复).问题是:

• 实体每周添加和删除属性
  (搜索关键字决定未来的属性)
• 每周都有新实体
  (产品由零件组装而成)
• 旧实体每周都会消失
  (已存档、不太受欢迎、季节性)

客户想要为产品添加属性有两个原因:

• 部门/关键词搜索/同类产品对比图
• 结账前的消费品配置

属性必须有意义，而不仅仅是关键字搜索.如果他们想比较所有有奶油糖霜"的蛋糕，他们可以点击蛋糕，点击生日主题，点击奶油糖霜，然后检查所有有趣的蛋糕，知道它们都有奶油糖霜.这不是特定于蛋糕的，只是一个例子.

解决方案

我能想到的有一些一般的利弊，在某些情况下，一个比另一个更好:

选项 1，EAV 模型:

• 优点:减少设计和开发简单应用程序的时间
• Pro:易于添加的新实体(甚至可能由用户添加?)
• Pro:通用"界面组件
• 缺点:验证简单数据类型所需的复杂代码
• 缺点:简单得多的复杂 SQL报告
• 缺点:复杂的报告几乎可以变成不可能
• 缺点:大型数据集的性能不佳

选项 2，分别为每个实体建模:

• 缺点:需要更多时间来收集要求和设计
• 缺点:必须对新实体进行建模并且由专业人士设计
• 缺点:每个自定义界面组件实体
• 优点:数据类型约束和验证易于实现
• 优点:SQL易于编写，易于编写理解和调试
• 优点:即使是最复杂的报告也相对简单
• 专业:大型数据集的最佳性能

选项 3，组合(模型实体正确"，但为某些/所有实体的自定义属性添加扩展")

• 利弊:收集需求和设计所需的时间比选项 1 多，但可能不如选项 2 *
• 缺点:新实体必须由专业人士建模和设计
• 优点:以后可以轻松添加新属性
• 缺点:验证简单数据类型所需的复杂代码(用于自定义属性)
• 缺点:仍然需要自定义界面组件，但对于自定义属性来说，通用界面组件可能是可能的
• 缺点:一旦报告中包含任何自定义属性，SQL 就会变得复杂
• 缺点:通常性能良好，除非您开始需要通过自定义属性进行搜索或报告

* 我不确定选项 3 是否一定会在设计阶段节省任何时间.

就我个人而言，我倾向于选项 2，并尽可能避免使用 EAV.但是，对于某些场景，用户需要 EAV 带来的灵活性；但这需要付出很大的代价.

It is safe to say that the EAV/CR database model is bad. That said,

Question: What database model, technique, or pattern should be used to deal with "classes" of attributes describing e-commerce products which can be changed at run time?

In a good E-commerce database, you will store classes of options (like TV resolution then have a resolution for each TV, but the next product may not be a TV and not have "TV resolution"). How do you store them, search efficiently, and allow your users to setup product types with variable fields describing their products? If the search engine finds that customers typically search for TVs based on console depth, you could add console depth to your fields, then add a single depth for each tv product type at run time.

There is a nice common feature among good e-commerce apps where they show a set of products, then have "drill down" side menus where you can see "TV Resolution" as a header, and the top five most common TV Resolutions for the found set. You click one and it only shows TVs of that resolution, allowing you to further drill down by selecting other categories on the side menu. These options would be the dynamic product attributes added at run time.

Further discussion:

So long story short, are there any links out on the Internet or model descriptions that could "academically" fix the following setup? I thank Noel Kennedy for suggesting a category table, but the need may be greater than that. I describe it a different way below, trying to highlight the significance. I may need a viewpoint correction to solve the problem, or I may need to go deeper in to the EAV/CR.

Love the positive response to the EAV/CR model. My fellow developers all say what Jeffrey Kemp touched on below: "new entities must be modeled and designed by a professional" (taken out of context, read his response below). The problem is:

• entities add and remove attributes weekly
  (search keywords dictate future attributes)
• new entities arrive weekly
  (products are assembled from parts)
• old entities go away weekly
  (archived, less popular, seasonal)

The customer wants to add attributes to the products for two reasons:

• department / keyword search / comparison chart between like products
• consumer product configuration before checkout

The attributes must have significance, not just a keyword search. If they want to compare all cakes that have a "whipped cream frosting", they can click cakes, click birthday theme, click whipped cream frosting, then check all cakes that are interesting knowing they all have whipped cream frosting. This is not specific to cakes, just an example.

解决方案

There's a few general pros and cons I can think of, there are situations where one is better than the other:

Option 1, EAV Model:

• Pro: less time to design and develop a simple application
• Pro: new entities easy to add (might even be added by users?)
• Pro: "generic" interface components
• Con: complex code required to validate simple data types
• Con: much more complex SQL for simple reports
• Con: complex reports can become almost impossible
• Con: poor performance for large data sets

Option 2, Modelling each entity separately:

• Con: more time required to gather requirements and design
• Con: new entities must be modelled and designed by a professional
• Con: custom interface components for each entity
• Pro: data type constraints and validation simple to implement
• Pro: SQL is easy to write, easy to understand and debug
• Pro: even the most complex reports are relatively simple
• Pro: best performance for large data sets

Option 3, Combination (model entities "properly", but add "extensions" for custom attributes for some/all entities)

• Pro/Con: more time required to gather requirements and design than option 1 but perhaps not as much as option 2 *
• Con: new entities must be modelled and designed by a professional
• Pro: new attributes might be easily added later on
• Con: complex code required to validate simple data types (for the custom attributes)
• Con: custom interface components still required, but generic interface components may be possible for the custom attributes
• Con: SQL becomes complex as soon as any custom attribute is included in a report
• Con: good performance generally, unless you start need to search by or report by the custom attributes

* I'm not sure if Option 3 would necessarily save any time in the design phase.

Personally I would lean toward option 2, and avoid EAV wherever possible. However, for some scenarios the users need the flexibility that comes with EAV; but this comes with a great cost.

这篇关于实体属性值数据库与严格的关系模型电子商务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！