实体-属性-值表设计

It seems like an EAV structure would be most suitable.

• 选择产品
• 产品属于属性集
• 属性集包含属性 x 和 y
  • 属性 x 是数据类型日期时间(值存储在 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 是数据类型日期时间，但在属性值中存储为 TEXT
    • 属性 y 是数据类型 int 但在 attribute_values 中存储为 TEXT

    推荐答案

    对于这个问题的大多数评论，我将提供相反的意见.虽然 EAV 是邪恶的，因为您可以在 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.