具有变体数据类型的SQL数据库设计 [英] SQL Database design with variant data types

问题描述

我正在开始一个新项目，但是在为数据库找到正确的设计时遇到了一些麻烦。

I'm starting a new project and I'm having some trouble finding the right design for the database.

数据库域中有许多与彼此，但有几个不同的领域。随着时间的流逝，此字段可能会更改，其中许多都是枚举。目的是设计一种数据库，使其可以通过管理仪表板控制字段及其可能的值。

The database domain have many entities that are similar to each other but have several distinct fields. This fields can change as the time goes by and many of them are enums. The objective is to design the database in a way that it's possible to control the fields and their possible values through an admin dashboard.

我的想法是让实体超级实体，用于存储所有实体之间的所有公共字段，并具有 EntityCharacteristics ，该实体将存储具有外键的实体特征 Characteristic 表将存储有关特征的信息，该信息将使使用该信息创建表单字段成为可能，例如

My idea is to have an entity Super entity that stores all the common fields between all entities, have an EntityCharacteristics which will store the entity caracteristics with foreign key to a Characteristic table which will store information about the characteristic that will make possible to create a form field with this information, imagine something like

CharacteristicName: Age
Placeholder: Enter your age...
InputType: text
CharacteristicType: Integer

有可能需要为selectbox的InputType存储可能的值，例如可能是枚举类型。
SuperEntity 还将有一个entity_type字段，该字段将连接到 EntityType 表，并且该表将连接到 PossibleEntityCharacteristics ，它将可能的特征存储到某个实体类型。

It needs to be possible to store possible values for an InputType of selectbox for example which would be an enum like type. Also the SuperEntity would have a entity_type field which would be connected to an EntityType table and this table would be connected to a PossibleEntityCharacteristics which would store the possible characteristics to a certain entity type.

我的问题是应该如何我将值存储在 EntityCharacteristics 中，因为它们可以是不同的类型，布尔值，文本，整数，枚举等。以及如何将可能的值存储在枚举类型中，和杰森在一起？还是使用另一个表中某个特性的可能值？我如何确定在 EntityCharacteristics 中插入的值是正确的类型，并且包含枚举的可能值？

My problem is how should I store the values in EntityCharacteristics as they can be of different types, boolean, text, integer, enum etc.. and also how would I store the possible values in enum types, with Json? Or with another table of possible values for a certain characteristic? How would I be certain that a value inserted in EntityCharacteristics would be of the correct type and would contain a possible value for an enum?

也许这根本不是一个好的设计，我不应该这样考虑，而只是将数据存储在具有许多字段的普通表中。但是，我想提供一种解决方案，它可以随时轻松更改不同相似实体的现有字段和字段值，而且似乎不必更改表架构。我打算用支持Json的PostgreSQL实现该功能，而PostgreSQL可能适合这里，但是由于我从未在Sql中使用此数据类型，所以我不知道这是否是个好主意。

Maybe this is bad design at all and I shouldn't be thinking like this and would just store the data in plain tables with many fields. But I would like to provide a solution that it is easy to change the existent fields and field values for the different similar entities at any time, and having to change the table schemas doesn't seem like a good idea for this. I plan to implement this with PostgreSQL which supports Json which may fit somewhere here, but as I never worked with this data type in Sql I dont know if it is a good idea.

我想知道您对此的看法，在此先感谢您。

I would like to know your opinion about this, and I thank you on advance.

注意：我正在考虑的数据库模型就是这样的 https://stackoverflow.com/a/7423459 ，但更为复杂且没有嵌套。

Note: the database model I'm thinking is something like this https://stackoverflow.com/a/7423459 but a bit more complex and without the nesting.

推荐答案

这有点基于意见，但是无论如何：

This is a bit opinion-based, but anyway:

我会去一张包含以下内容的表所有类型的对象共有的所有属性的列。然后再有一个 JSONB 列，用于存储在不同类型之间可能有所不同的各个属性。

I would go for a single table that contains columns for all attributes that are common across all types of objects. Then have an additional JSONB column that stores the individual attributes that might vary between the different types.

您可以更进一步，在第二个表中创建类型描述，该表定义了类型的允许属性。然后可以通过管理UI，也可以使用它来验证放入基表的动态属性中的数据。

You can take this a bit further and create a "type description" in a second table which defines the allowed attributes for a type. That can then be through the admin UI and you could also use it to verify the data that is put into the "dynamic" attributes of the base table.

类似这样的东西：

create table object_type
(
  id integer primary key,
  name text not null,
  allowed_attributes jsonb not null
);  

create table objects
(
  id integer primary key,
  name text not null,
  object_type_id integer not null references object_type,
  attributes jsonb
);

insert into object_type (id, name, allowed_attributes)
values
(1, 'spaceship', '{"seats": "integer", "color": "text"}'::jsonb),
(2, 'book', '{"number_of_pages": "integer", "color": "text"}'::jsonb);

insert into objects (id, name, object_type_id, attributes)
values
(1, 'Heart Of Gold', 1, '{"seats": 4, "color": "white"}'),
(2, 'H2G2', 2, '{"number_of_pages": 42, "color": "black", "published_in": 1979}');

在上面的示例中， published_in 是基于 object_type 中相应行的属性。这些行可以用例如以下查询：

Now in the above example the published_in is an attribute that is not allowed based on the corresponding row in object_type. These rows can be identified with e.g. the following query:

select *
from (
  select *,
         attributes - (select array_agg(t.k) 
                       from object_type ot, jsonb_object_keys(ot.allowed_attributes) as t(k)
                       where ot.id = o.object_type_id) as invalid_attributes
  from objects o
) t
where invalid_attributes <> '{}';

您甚至可以构建一个触发器，在插入或更新对象时进行这种检查。

You could even build a trigger that does this kind of checking when objects are inserted or updated.

使用 json_typeof（）函数，您还可以验证键的提供值是否与定义的数据类型匹配在 object_type

Using the json_typeof() function you could also validate if the supplied value of a key matches the data type defined in object_type

这篇关于具有变体数据类型的SQL数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！