实体属性值表设计 [英] Entity-Attribute-Value Table Design

问题描述

我目前正在为电子商务平台的产品部分设计数据库结构。它需要以这样一种方式进行设计，使得有可能以无限数量的不同属性销售无限数量的不同类型的产品。

I am currently designing a database structure for the products section of an ecommerce platform. It needs to be designed in such a way that makes it possible to sell an infinite number of different types of products with an infinite number of different attributes.

例如。笔记本电脑的属性将是RAM，屏幕尺寸，重量等。一本书的属性将是作者，ISBN，出版商等。

E.g. The attributes of a laptop would be RAM, Screen Size, Weight, etc. The attributes of a book would be Author, ISBN, Publisher, etc.

看起来像EAV结构将是最合适的。

It seems like an EAV structure would be most suitable.

• 选择产品


• 产品属于属性集<属性x包含属性x和y
  
  
  
  • 属性x是数据类型datetime（存储在attribute_values_datetime中的值）
  
  属性y是数据类型int（存储在attribute_values_int中的值）
  

  假设上述，我可以将选择加入attribute_values_datetime表以获取正确的数据，而不会得到结果集和建立第二个查询现在表是已知的？将会有一个很大的性能打击构建这种类型的查询，或下面将更适合（虽然功能较少）

  Assuming the above, could I join the selection to the attribute_values_datetime table to get the right data without getting the result set and building a second query now that the table is known? Would there be a large performance hit constructing a query of this type or would the below be more suitable (although less functional)

  
  
  • 选择一个产品
  
  
  • 产品属于属性集
  
  
  • 属性集包含属性x和y
    
    
    
    • 属性x是数据类型datetime，但在attribute_values中存储为TEXT
    
    
    • 属性y是数据类型int，但是在attribute_values
    
    
    • Select a product
    • Product belongs to attribute set
    • Attribute set contains attributes x and y
      • Attribute x is data type datetime but stored as TEXT in attribute_values
      • Attribute y is data type int but stored as TEXT in attribute_values

      推荐答案

      我将对这个问题的大部分评论提出相反的意见。虽然EAV是EVIL ，由于您可以在SO和DBA.SE等其他地方彻底解释多次的原因，所以有一个很常见的应用程序，大多数情况都是错误的EAV在很大程度上是无关紧要的，EAV的（少数）优势非常密切。该应用程序是在线产品目录。

      I'm going to offer a contrary opinion to most of the comments on this question. While EAV is EVIL for all of the reasons that you can find thoroughly explained many times here on SO and DBA.SE and elsewhere, there is one really common application for which most of the things that are wrong with EAV are largely irrelevant and the (few) advantages of EAV are very much germane. That application is online product catalogs.

      EAV的主要问题是它不让数据库做真正做的事情，这有助于给通过将它们安排在模式中，可以将关于不同实体的信息的不同属性的适当上下文。有一个架构在访问，解释和执行数据的完整性方面带来了许多许多优势。

      The main problem with EAV is that it doesn't let the database do what it is really good at doing, which is helping to give proper context to different attributes of information about different entities by arranging them in a schema. Having a schema brings many, many advantages around accessing, interpreting and enforcing integrity of your data.

      产品目录的事实是，产品的属性几乎完全不相关到目录系统本身。产品目录系统（最多）可以使用产品属性的三件事情。

      The fact about product catalogs is that the attributes of a product are almost entirely irrelevant to the catalog system itself. Product catalog systems do (at most) three things with product attributes.

      
      

      1. 将列表中的产品属性显示给最终用户形式：{attribute name}：{attribute value}。

      1. Display the product attributes in a list to end users in the form: {attribute name}: {attribute value}.

      在比较网格中显示不同产品的属性与每个产品相符的多个产品的属性其他（产品通常是列，属性通常是行）

      Display the attributes of multiple products in a comparison grid where attributes of different products line up against each other (products are usually columns, attributes are usually rows)

      根据特定属性/值组合驱动某些东西（例如定价）的规则。

      Drive rules for something (e.g. pricing) based on particular attribute/value combinations.

      如果您的所有系统都是反向语义上与信息无关的信息（对于系统），则此信息的模式基本上无益。实际上，在线产品目录中的模式 ，特别是如果您的目录有多种不同类型的产品，因为您总是不得不回到模式来修改它，以允许对于新产品类别或属性类型。

      If all your system does is regurgitate information that is semantically irrelevant (to the system) then the schema for this information is basically unhelpful. In fact the schema gets in the way in an online product catalog, especially if your catalog has many diverse types of products, because you're always having to go back into the schema to tinker with it to allow for new product categories or attribute types.

      由于使用方法，产品目录中的属性值的数据类型不一定非常重要。对于某些属性，您可能需要施加约束，例如必须是数字或必须来自此列表{...}。这取决于您的目录的属性一致性是多么重要，以及您希望实现的精细程度。看看几个在线零售商的产品目录，我会说最多的是准备交换简单的一致性。

      Because of how it's used, even the data type of an attribute value in a product catalog is not necessarily (vitally) important. For some attributes you may want to impose contraints, like "must be a number" or "must come from this list {...}". That depends on how important attribute consistency is to your catalog and how elaborate you want your implementation to be. Looking at the product catalogs of several online retailers I'd say most are prepared to trade off simplicity for consistency.

      是的，EAV是邪恶的，除非不是。

      Yes, EAV is evil, except when it isn't.

      这篇关于实体属性值表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！