我们可以在一个eav设计中有一个具有多个值的属性吗？ [英] can we have one attribute with multiple values in an eav design?

问题描述

我正在使用EAV进行数据库设计。我试图用具有多个值的属性建模实体时面临一个问题？

例如

实体

  id |名称| description 
  -  | ---- | ------------ 
 1 | configuration1 |配置1 
  

属性

 code> id | entityId |名称|键入
  -  | -------- | ---- | ---- 
 1 | 1 | att1 | string 
 2 | 1 | att2 | int 
 3 | 1 | att3 |列表< String> （我将如何建模？）
  

价值

  id | attributeId |值
  -  | ----------- | ----- 
 1 | 1 | a 
 2 | 2 | 1 
 3 | 3 | b 
 4 | 3 | c 
 5 | 3 | d 
  

这是处理值列表的正确方法吗？

请提供任何有用的链接来建模这个？

另外两个问题

1）类型列表正确吗？我想确保当一个属性有多个值时，我将给出类型为列表

2）当属性对应于对象时，数据库设计将如何更改？例如用户有地址。我将如何处理复合参数？

如果您能为我提供粗略的表格表示或图表，那将是非常好的

谢谢

Shekhar

解决方案

是！可以在EAV中具有多值属性属性。

实际上它比传统的关系模型更容易，需要创建一个额外列，或以分隔格式分隔多个值存储;当查询数据库的基础字段（属性）的给定值时，这两种方法都会增加复杂性。

拥有多个值的最简单的方法是简单地增加一个额外的值价值记录！ （如问题所示）

此外，EAV商店结构可以更改为明确容纳多值，其中包括：

• 属性表中的额外的类似布尔的字段，用于指示该字段是否可以是多值。 （BTW，属性的类似属性也可以编纂，例如属性是否需要等等。


• 表格可以获得额外的列指示值的序列号（对于所有非多值属性，设置为0或1，否则为递增整数）。

如上所述，对EAV存储的物理模式的这些更改不是必需的，但它们可以用于确保数据符合（逻辑）模式，并且可能显示特定的多值属性的几个值

编辑 :(详细介绍实现多值和/或复合（对象）属性）

如果您绝对肯定构成属性的多个[sub - ]值（或类似地构成对象类型属性的多个部分）完全是原子的，即永远不会被搜索或显示（或...），您可以通过将多个值编码为单个字符串，将这种属性值集合存储为值表中的单个记录;为此，可以考虑使用JSON或XML，这对于非常可扩展/泛型来说似乎特别有趣，但是您可以以可靠的方式解析出来的任何其他格式也可以工作（比如分隔格式）。 p>

存储这种属性值部分的更自然的方式（EAV-wise）是通过单独存储它们（在值表的多个记录中，可能是序列字段如前所示）。这种方法允许在一些上下文中处理子部分，就像它们是属性一样。

在这两种情况下，您需要更改属性表以添加必要的属性和类型代码来描述这样的多部分属性。类似于存储数据的方法（在值表中），您可以制作属性记录，使给定[多部分]属性的所有信息都存储在单个属性记录中，或者[和这是通常更容易和更灵活]您可以为每个部分创建一个属性，再加上一个属性将它们绑定在一起（例如，包含一个属性，该属性包含用子部件的每个属性ID值分隔的字符串。

例如：

金属管道项目的复合属性可以是直径，由两部分组成：数值和单位代码（毫米，对英寸）。

第一种方法：

- 属性表中将有一条记录，其中一个类型指示这是一个多值，以及一个扩展属性，以包含各个子部分的[ordered]类型列表。

- 在值表中将存在单个记录，其中包含一个编码值，例如0.75 |英寸（或< diam> 0.75< / diam>< unit> Inch< / unit> ）。

使用第二种方法：

- 属性表中将有3条记录：记录或类型数字，名称为diamvalue，字符串类型的记录，名为unit和复合名称Diameter的记录;这个最后一个记录将以某种方式引用另外两个属性的ID（一个简单的逗号分隔的字符串）。
- 值表中将有两个记录，每个diamvalue和单位属性（这样的记录将有一个额外的字段，称为父，包含Diameter属性的AttributeID。可选地，Diameter属性可能还有一个值记录[我个人发现这个冗余与父属性

如前所述，第二个解决方案的主要优点是[适当时]可以基于某个特定项目的值查询一组特定项目的目录属性部分，例如搜索具有度量单位的所有管道。这些查询在SQL级别解析，由此，通过第一种方法，SQL将必须扫描属性Diameter的所有属性值，并将值解析为搜索单位代码

图片价值一千字; - ）

该图显示了可能的布局具有第二种方法的样本数据。

 实体
 id |名称| description 
  -  | ---- | ------------ 
 1 | configuration1 | configuration1 
 
属性
 id |名称|类型|必需|重复| SubAttribIdList 
  -  | ---- | ---- | -------- | ------- | --------------- 
 1 | att1 |字符串| N | N | null（仅适用于复合类型）
 2 | att2 | int | Y | N | null 
 3 | att3 |字符串| Y | Y | null 
 4 | DiamValue |数字| Y | N | null 
 5 |单位|字符串| Y | N | null 
 6 |直径|复合| N | N | 4,5 
 
值
 id | entityId | attributeId | ParentAttribId | SeqNr |值
  -  | -------- | ----------- | -------------- | ----- | ----- 
 1 | 1 | 1 | null | 1 | a 
 2 | 1 | 2 | null | 1 | 1 
 3 | 1 | 3 | null | 1 | b（此值和下一个节目显示重复的属性）
 4 | 1 | 3 | null | 2 | c 
 5 | 1 | 3 | null | 3 | d 
 6 | 1 | 4 | 6 | 1 | 0.75（此值和下一个显示复合属性
 7 | 1 | 5 | 6 | 1 | Inches 
  

一些注释：

- 对于两个值，ids 6和7的SeqNr为1，它们的顺序对于SubAttribIdList是隐含的，如果属性ID 6已经成为多value（Repeats）属性，一个实体可以具有两个值，其中两个值对成对，2对，3等。

- 不可重复属性的序列号被系统地设置为1，也可以是NULL，这不适用。

- 属性的必需属性不在多值或复合问题中;我只是将它添加到常用的帮助应用程序（或实体访问层）执行各种完整性规则。

- 此布局中的一些设计选项意味着复合属性最多包含1个级别（复合不能包括在内）在复合中），以及防止组合包含多值属性。可以通过适当的结构（并且在接入层中增加复杂度）来避免这些限制，但是更简单的模式通常是可接受的（需要这种花哨结构的属性通常表示逻辑模式中的缺陷） 。

i am doing a database design using EAV. I am facing an issue when i try to model an entity with attribute having multiple values?

For example

Entity

id         | name           | description
--         | ----           | ------------ 
1          | configuration1 | configuration1

Attribute

id         | entityId    | name  | type
--         | --------    | ----  | ----
1          | 1           | att1  | string
2          | 1           | att2  | int
3          | 1           | att3  | List<String>  (How will i model this?)

Value

id        | attributeId    | value
--        | -----------    | -----
1         | 1              | a    
2         | 2              | 1
3         | 3              | b
4         | 3              | c    
5         | 3              | d

Is this the correct way to handle list of values?

Please provide any helpful link to model this?

Two more questions

1) Is type as List correct? I want to be sure that when one attribute has multiple values i will give the type as List

2) How the database design will change when attribute corresponds to an Object? For example User has Address..How will i handle composite parameters?

It would be great if you can provide me a rough tabular representation or diagram

Thanks

Shekhar

解决方案

Yes! it is quite possible to have multi-value attributes attributes in EAV.
In fact it is easier than with the traditional relational model where one would need to either create an extra column, or store the multiple values with a delimited format of sort; both such approach suffer added complexity when querying the database for a given value of the underlying field (attribute).

The simplest way to have multiple values is simply to have an extra value record! (as shown in the question)
In addition, the EAV store structure can be changed to explicitly accommodate multi-values, with:

• an extra boolean-like field in the attribute table to indicate whether the field can or cannot be multi-value. (BTW, similar properties of the attribute can be codified as well, for example whether the attribute is required or not etc
• The value table can get an extra column to indicate the sequence number of the value (set to 0 or 1 for all non multivalue attributes, otherwise, an incremented integer).

As said, these changes to the physical schema of the EAV store are not necessary, but they can be used to ensure that the data conforms to the (logical) schema as well as to maybe display the several values of a multivalue attributes in a particular order etc.

Edit: (details on implementing multi-value and/or composite ("object-like") attributes)
If you are absolutely positive that the multiple ["sub"-]values which constitute an attribute (or similarly the multiple parts which constitute an "object type" attribute), are completely atomic, i.e. will never be searched or displayed (or ...) individually, you can store such an attribute' value "sets", as a single record in the value table, by encoding the multiple values into a single string; For this purpose, JSON or XML-at-large comes to mind and seems particularly interesting for very expandable/generic, but any other format which you can parse in and out in a reliable fashion would also work (say delimited format).

A more "natural" way (EAV-wise) to store such "attribute value parts", is by storing them individually (in multiple records of the value table, possibly with a sequence field as hinted earlier). This approach allows handling the "sub parts", in some contexts, as if they were attributes.

In both cases, you need to alter the attribute table to add the necessary properties and types codes, to describe such multi-part attributes. Similarly to the approach of storing the data (in the value table), you can either make the attribute record such that all the info for a given [multi-part] attribute is stored in a single attribute record, or, [and this is typically easier and more flexible] you can create one attribute per part, plus one attribute to "tie them together" (for example with a property which contains a string delimted with each of the attribute ID values of the subparts.

For example:
A composite attribute for metallic pipe items, could be the diameter, made of two parts: a numeric value and a unit code (Millimeter, vs. Inches).
With the first approach:
- there would be one record in the attribute table, with a type indicating that this is a multi-value, and an extension property to contain the [ordered] list of types of the individual sub parts.
- there would be a single record in the value table, containing a coded value such as as say "0.75|Inch" (or <diam>0.75</diam><unit>Inch</unit>).
With the second approach:
- there would be 3 records in the attribute table: a record or type numeric and named say "diamvalue", a record of type string, named "unit" and a record of type composite name "Diameter"; this last record would somehow have a reference to the ID of the two other attributes (a simple comma delimited string comes to mind) - there would be two records in the value table, one each of the diamvalue and the unit attributes (such records would have an additional field, called say "parent" containing the AttributeID of the "Diameter" attribute. Optionally there could be also an value record for the "Diameter" attribute [I personally find this redundant with the "parent" property.

As indicated earlier, the main advantage of the second solution is that [when appropriate] one can query the catalog for a particular set of items based on the value of an attribute part, for example searching for all pipes which have a metric unit. Such queries are resolved at the level of SQL, whereby with the first approach, SQL would have to scan all attribute values for the attribute "Diameter" and parse the value to search for the unit code.

A picture's worth a thousand words ;-)
This diagram shows a possible layout with sample data for the "second approach".

Entity 
    id   | name           | description
    --   | ----           | ------------ 
    1    | configuration1 | configuration1

Attribute 
    id   | name      | type     | Required | Repeats | SubAttribIdList
    --   | ----      | ----     | -------- | ------- | ---------------
    1    | att1      | string   | N        | N       | null   (only applicable to composite types)
    2    | att2      | int      | Y        | N       | null
    3    | att3      | string   | Y        | Y       | null
    4    | DiamValue | numeric  | Y        | N       | null
    5    | Unit      | string   | Y        | N       | null
    6    | Diameter  | composite| N        | N       | 4,5

Value
    id  | entityId| attributeId  | ParentAttribId |SeqNr | value     
    --  | --------| -----------  | -------------- |----- | -----
    1   | 1       | 1            | null           | 1    | a    
    2   | 1       | 2            | null           | 1    | 1
    3   | 1       | 3            | null           | 1    | b  (this value and next show show a repeating attribute)
    4   | 1       | 3            | null           | 2    | c    
    5   | 1       | 3            | null           | 3    | d
    6   | 1       | 4            | 6              | 1    | 0.75   (this value and next one shows a composite attribute
    7   | 1       | 5            | 6              | 1    | Inches

A few notes:
- The SeqNr for the values ids 6 and 7 is 1, for both. Their order is implicit to the SubAttribIdList. If Attribute id 6 had been made a multi-value ("Repeats") attribute, an entity could have additional couplets of two values, sequenced, in pair, 2, 3 etc.
- The sequence Number for non repeatable Attributes is set to 1, systematically, could as well be NULL this this doesn't apply.
- The "Required" property of attribute doesn't figure in the multi-value or composite question; I just added it as it is commonly used to help the application (or the entity access layer) enforce various integrity rules.
- A few of the design choices in this layout imply a maximum of 1 level of inclusion for composite attributes (a composite cannot be a included in a composite), as well as prevent a composite to include a multi-value attribute. These limitations can be avoided with the proper structure (and with a bit of added complexity in the access layer), but the simpler schema is typically acceptable (attributes that would require such fancy structure are often a indicative of a flaw in the logical schema).

这篇关于我们可以在一个eav设计中有一个具有多个值的属性吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！