寻找基于jsonb的正确的EAV结构 [英] Looking for a right EAV structure based on jsonb

问题描述

我想知道在jsonb上构建EAV的正确方法是什么. 我有Attribute-> Values表，就像在标准EAV中一样.

I wondering what will be the right approach to build EAV on jsonb. I have Attribute -> Values tables as like in standard 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
    );

创建的

表Attribute用于控制重复属性的类型，并更好地确定它是什么属性.例如，避免:{weight: 100}和{Weight: 100}或{weigh: 100}. Values用于处理唯一值，并包含可用的值列表，例如颜色(绿色，红色，白色等).可以预先加载值并将其用于常用搜索.

Tables Attribute created to control duplicate attributes their types and better determine what it's a attribute is. For example to avoid: {weight: 100} and {Weight: 100} or {weigh: 100}. Values for work with unique values and contain avaliable list of values like color (green, red, white etc.) Values can be preloaded and using for faseted search.

我看到几个选项:

1..存储格式如

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

其中，value_id将是custom value，例如文本，或者是Values表中的id.但是我不明白如何在这种格式上建立索引，例如Attribute 10将integer

where value_id will be custom value like text or id from Values table. But I can't understand how to build indexing on this format, for example if Attribute 10 will integer

2..仅保留Attribute表(用于控制属性name)，并存储以下数据:

2. leave only Attribute table (for controlling attribute name) and store data like:

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

.这种方法更适合索引

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

但是我在翻译文本属性和控制唯一值方面会遇到问题.我也不能像选项1中那样添加其他密钥:{"attribute_id":1, "value":5, "values": []}

but I will have problem with translation of text property and controlling of unique values. Also I can't add additional key like in option 1: {"attribute_id":1, "value":5, "values": []}

通过唯一的控件(用于唯一的属性)并有机会建立索引来存储额外字段的最佳方法是什么.

What will be the best approach to store extra field with unique control (for unique attributes) and with the opportunity to indexing.

推荐答案

目标:您要存储与给定实体相关的属性.

我不建议为属性值使用单独的表格，就像我们过去几年所做的那样.在适当的表上放置一个jsonb字段，并将其命名为Attributes.向其中添加一个GIN索引，以便您可以快速查询值.或使用其中描述的其他技术.

Objective: You want to store attribute related to a given entity.

I do not recommend a separate table for attribute values like we might have done in years gone by. Put a jsonb field right on the appropriate table and call it Attributes. Add a GIN index to it so you can query the values quickly. Or use the other techniques described within.

阅读此内容: https://dba.stackexchange.com/a/174421/7762

这里最大的问题是您是否打算预定义属性值.如果这样做，则有一种非常有效的方式来存储它们.如果没有，那么我建议使用标准的JSON对象.

The biggest question here is if you intend to pre-define attribute values. If you do, there is an extremely efficient way to store them. If not, then I recommend a standard JSON object.

这将为您提供最大的控制权，最快的速度，并且仍然提供灵活性.

创建具有以下字段的表Attribute:

Create a table Attribute which has these fields:

• AttributeID int4 unsigned not null primary key
• ParentAttributeID int4 unsigned null
• Name varchar(64) not null
• Deleted bool不为null默认为false
• 在ParentAttributeID
上添加索引
• 添加触发器以防止AttributeID更改
• 在删除操作上添加一条规则，而不要设置Deleted = True

• AttributeID int4 unsigned not null primary key
• ParentAttributeID int4 unsigned null
• Name varchar(64) not null
• Deleted bool not null default false
• Add an index on ParentAttributeID
• Add a trigger to prevent AttributeID from changing
• Add a rule on delete do instead set Deleted=True

然后在要归因的任何表中，添加以下字段:

Then in any table you want to attribute, add this field:

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

• AttributeSet" int[] not null default
• Add a GIN index on that array field
• Also enable the intarray extension from https://www.postgresql.org/docs/current/static/intarray.html

这完成了什么?

您已经创建了一个属性树.可能看起来像这样:

You've create a tree of attributes. It might look like this:

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:

Say you have a table called Items and on it you've added AttributeSet:

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

翻译后，这意味着它具有Color=Green属性和Size=Medium属性. 103和112足以存储此内容，但是有时可以说向我显示所有定义了任何尺寸的商品"，这就是很好的原因，这就是为什么要包含110的原因.

When translated, this means that it has the Color=Green attribute, and the Size=Medium attribute. 103 and 112 were enough to store that, but sometimes it's nice to be able to say "Show me all items that have any Size defined", that's why 110 was included.

您可以使此闪电快速而又灵活.

You can make this lightning fast and ultra flexible.

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

将返回所有具有Size=Medium和Color=Green

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

Or you can use the other operators on https://www.postgresql.org/docs/10/static/functions-array.html to come up with some awesome queries.

这将为您提供最大的速度，控制力，并且更加灵活.您可以根据需要标记新的属性以供审核.

您可以使用上述技术，并将值动态添加到Attribute表中(如果它们不存在).

You can use the above technique and just dynamically add values to the Attribute table if they don't exist.

这为您提供了最大的灵活性，但是却以控制为代价.

在这种情况下，只需将其添加到任何表中即可:

In this case just add this to any table:

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

编写代码以根据您的Attribute表验证值.如果它是一个单值或多值，则有一个指标...

Write code to validate the values against your Attribute table. Have an indicator there if it is a single or multi-value...

将这样存储在AttributeMap字段中:

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

请注意，类别是一个多属性.在Attribute表中，您应该有一个IsMulti bool not null字段，它将使您知道如何查询它.

Notice that Categories is a multi-attribute. In youe Attribute table you should have a field that is IsMulti bool not null which will allow you to know how to query for it.

这篇关于寻找基于jsonb的正确的EAV结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！