寻找一个基于jsonb的正确EAV结构

Question

寻找一个基于jsonb的正确EAV结构

5

我想知道在jsonb上构建EAV的正确方法是什么。我有一个像标准EAV中的属性 -> 值表。

CREATE TABLE attribute_values
(
  id           INTEGER,
  attribute_id INTEGER,
  value        VARCHAR(255)
);

CREATE TABLE attributes
(
  id   INTEGER,
  name VARCHAR(255)
);

值将保存在Entity的attributes字段中。

 CREATE TABLE entity
    (
      id   INTEGER,
      title TEXT,
      attributes JSONB
    );

表格 属性 创建用于控制重复属性及其类型，更好地确定属性是什么。例如，避免：{weight: 100} 和 {Weight: 100} 或 {weigh: 100}。 值 用于处理唯一值，并包含可用值列表，如颜色（绿色，红色，白色等）。可以预加载并用于快速搜索。

我看到几个选项： 1. 存储格式如下：

[{"attribute_id":1, "value":5},{"attribute_id":1, value:"text"}]

其中value_id将是自定义值，如文本或来自Values表的id。但我不知道如何在这种格式上建立索引，例如如果属性10是整数。

2. 仅保留Attribute表（用于控制属性名称），并存储数据如下：

{"price": 105, "weight": 100, "color": "white"}

这种方法更适合索引

CREATE INDEX entity_index ON entity (((attributes ->> 'price')::int));

但我遇到了文本属性的翻译和唯一值的控制问题。而且我不能像选项1中那样添加其他键：{"attribute_id":1, "value":5, "values": []}

什么是存储具有唯一控制（用于唯一属性）且具有索引机会的额外字段的最佳方法？

- Ivanov

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gahooa · Accepted Answer

目标：你想要存储与给定实体相关的属性。

我不建议像我们过去做的那样为属性值创建一个单独的表。在适当的表上放置一个名为Attributes的jsonb字段。添加一个GIN索引，以便可以快速查询值。或者使用其他描述中提到的技术。

阅读此文：https://dba.stackexchange.com/a/174421/7762

最大的问题是，您是否打算预定义属性值。如果是，则有一种极其有效的方法来存储它们。否则，我建议使用标准JSON对象。

如果您可以预定义属性名称和值：

这样可以给您带来最大的控制、速度和灵活性。

创建一个名为Attribute的表，其中包含以下字段：

AttributeID int4 unsigned not null primary key
ParentAttributeID int4 unsigned null
Name varchar(64) not null
Deleted bool not null default false
在ParentAttributeID上添加索引
添加触发器以防止更改AttributeID
添加规则，在删除时代替设置Deleted=True

然后在任何要进行属性的表中，添加此字段：

AttributeSet" int[] not null default
在该数组字段上添加GIN索引
还要从https://www.postgresql.org/docs/current/static/intarray.html启用intarray扩展。

这样做有什么好处？

您已经创建了一棵属性树。它可能看起来像这样：

ID   Parent  Name
----------------------------
100  NULL    Color
101  100       Blue
102  100       Red
103  100       Green
110  NULL    Size
111  110       Large
112  110       Medium 
113  110       Small

假设您有一个名为Items的表，您已经添加了AttributeSet：

      ItemID: 1234
        Name: Tee Shirt
AttributeSet: [100, 103, 110, 112]

当被翻译时，这意味着它具有Color=Green属性和Size=Medium属性。 103和112足以存储，但有时候能够说“显示所有已定义任何大小的项目”很好，这就是为什么包括110的原因。

您可以使其快速且灵活无比。

SELECT
  "ItemID", "Name"
FROM
  "Items"
WHERE "AttributeMap" @> ARRAY[103,112]

将返回所有具有 Size=Medium 和 Color=Green 的项目。

或者您可以使用https://www.postgresql.org/docs/10/static/functions-array.html上的其他运算符来创建一些很棒的查询。

当您不知道属性值但它是一个小集合时：

这会给您最快的速度、控制力，甚至更加灵活。如果需要，您可以为新属性标记审核。

您可以使用上述技术，只需在不存在的情况下动态添加值到Attribute表中。

当您不知道属性值且值是多样化的时候：

这会给您最大的灵活性，但会牺牲控制。

在这种情况下，只需将以下内容添加到任何表中：

AttributeMap jsonb not null default '{}'::jsonb
在该字段上添加GIN索引

编写代码以验证值是否与您的Attribute表匹配。在那里设置指示器，以确定它是单个值还是多个值...

将其存储在AttributeMap字段中，如下所示：

{
    "Color": "Green", 
    "Size": "Medium", 
    "Categories": ["Sports", "Leisure"]
}

请注意，"Categories" 是一个多属性。在您的 "Attribute" 表中，应该有一个字段是 "IsMulti bool not null"，这将允许您知道如何查询它。