您如何模拟实体的自定义属性？ [英] How do you model custom attributes of entities?

问题描述

假设我们有一个应用程序可以存储所有类型的产品。每个产品至少有一个 ID 和一个名称，但所有其他属性都可以由用户自己定义。 p>

1. 例如他可以创建一个产品组，它将包含容量和生成


2. 的属性。他可以使用属性大小和颜色创建一个产品组


3. 我们需要存储定义的产品和具体产品本身。


4. 我们希望确保按产品属性聚合（GROUP BY）很容易。例如。选择每一代ipods的总容量


5. 解决方案不能要求模式更改（由于来自Bill Karwin的输入而增加的要求 - 请参阅他的答案！）

如何根据上述要求对模式进行建模？

注意：要求4.很重要！

感谢大家贡献和讨论的方法。过去我已经看到了一些解决这个问题的解决方案，但是对于我来说，这个问题并不容易。（

解决方案

不会很容易，因为你将要使用什么聚合运算符颜色？请注意，不可能在情况2中使用您的要求4。

任何情况下，由于数据类型的变化，聚合只是困难的，并且可以通过以更简单的方式接近来减轻聚合。因为知道添加苹果和橙子是没有意义的。

这是经典的EAV模型，它在数据库中有一个精心设计的地方，为了使其更具有类型安全性，我看到了将值存储在类型安全表中而不是一个单一的自由格式varchar列。

而不是值：

  EntityID int 
，AttributeID int 
，Value varchar（255）
  

多个表：

  EntityID int 
，AttributeID int 
，ValueMoney钱
 
 EntityID int 
，AttributeID int 
，ValueInt int 
 
等
  

然后每一代获取您的iPod容量：

  SELECT vG.ValueVarChar AS Generation，SUM（vC。 ValueDecimal）AS TotalCapacity 
 FROM Products AS p 
 INNER JOIN属性AS aG 
 ON aG.AttributeName ='generation'
 INNER JOIN ValueVarChar AS vG 
 ON vG.EntityID = p.ProductID 
 AND vG.AttributeID = aG.AttributeID 
 INNER JOIN属性AS aC 
 ON aC.AttributeName ='capacity'
 INNER JOIN ValueDecimal AS vC 
 ON vC.EntityID = p.ProductID 
 AND vC.AttributeID = aC.AttributeID 
 GROUP BY vG.ValueVarChar 
  

Let's say we're having an application which should be able to store all kind of products. Each product has at least an ID and a Name but all other attributes can be defined by the user himself.

1. E.g. He could create a productgroup Ipods which would contain attributes capacity and generation
2. E.g. He could create a productgroup TShirts with the attributes size and color
3. We need to store the definition of a product and the concrete products itself.
4. We want to ensure that it is easily possible to aggregate (GROUP BY) by product attributes. E.g. select the total sum of capacity for each generation of ipods
5. The solution must not require schema changes (added requirement due to input from Bill Karwin - see his answer as well!)

How would you model your schema in respect to the above requirements?

Note: Requirment 4. is important!

Thanks everyone for contributing and discussing the approach. I have seen some solutions to this problem in the past but none of them made grouping easy for me :(

解决方案

The grouping is not going to be easy because what aggregate operator are you going to use on "color"? Note that it is not possible to use your requirement 4 on case 2.

In any case, the aggregating is only difficult because of the variation in data types and can be mitigated by approaching it in a more typesafe way - knowing that it never makes sense to add apples and oranges.

This is the classic EAV model and it has a place in databases where carefully designed. In order to make it a bit more typesafe, I've seen cases where the values are stored in type-safe tables instead of in a single free form varchar column.

Instead of Values:

EntityID int
,AttributeID int
,Value varchar(255)

You have multiple tables:

EntityID int
,AttributeID int
,ValueMoney money

EntityID int
,AttributeID int
,ValueInt int

etc.

Then to get your iPod capacity per generation:

SELECT vG.ValueVarChar AS Generation, SUM(vC.ValueDecimal) AS TotalCapacity
FROM Products AS p
INNER JOIN Attributes AS aG
    ON aG.AttributeName = 'generation'
INNER JOIN ValueVarChar AS vG
    ON vG.EntityID = p.ProductID
    AND vG.AttributeID = aG.AttributeID
INNER JOIN Attributes AS aC
    ON aC.AttributeName = 'capacity'
INNER JOIN ValueDecimal AS vC
    ON vC.EntityID = p.ProductID
    AND vC.AttributeID = aC.AttributeID
GROUP BY vG.ValueVarChar

这篇关于您如何模拟实体的自定义属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！