时间戳

首页 > 解决方案 > 具有变体数据类型的 SQL 数据库设计

sql - 具有变体数据类型的 SQL 数据库设计

问题描述

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

数据库域有许多彼此相似但有几个不同字段的实体。这个字段可以随着时间的推移而改变，其中许多是枚举。目标是设计数据库，使其可以通过管理仪表板控制字段及其可能的值。

我的想法是有一个实体Super entity，它存储所有实体之间的所有公共字段，有一个EntityCharacteristics将带有外键的实体特征存储到一个Characteristic表中，该表将存储有关特征的信息，这将使创建一个带有此信息的表单字段成为可能，想象一下

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

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

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

也许这是一个糟糕的设计，我不应该这样想，而只是将数据存储在具有许多字段的普通表中。但是我想提供一个解决方案，可以随时轻松更改不同相似实体的现有字段和字段值，并且不得不更改表模式似乎不是一个好主意。我计划用支持 Json 的 PostgreSQL 来实现这个，它可能适合这里的某个地方，但是由于我从未在 Sql 中使用过这种数据类型，我不知道这是否是个好主意。

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

注意：我正在考虑的数据库模型类似于https://stackoverflow.com/a/7423459但有点复杂并且没有嵌套。

标签： sqlpostgresqldatabase-design

解决方案

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

我会选择一个表，其中包含所有类型的对象共有的所有属性的列。然后有一个额外的JSONB列来存储可能在不同类型之间变化的各个属性。

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

像这样的东西：

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. 这些行可以用例如以下查询来识别：

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 <> '{}';

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

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

推荐阅读