Хранение свойств растений без схемы

В этой статье описано почему реляционная схема для хранения свойств растений оказалась не такой уж и удобной и как ее можно улучшить.

Проблемы реляционной схемы?

Проблема, собственно, со схемой. Ниже на схеме представлена предыдущая версия, которая была описана в Seed storage model

seed storage logical schema

sql для создания схемы (github)

В этой версии свойства растений были разложены на множество табличек что сделало ее очень тяжёлой и совсем нем понятной.

Свойства растений размазаны по табличкам из-за того, что значения растений имеют сильно разную структуру, набор значений и их типы этих значений.

Пример того, что мы должны предоставить клиентам:

{
    "plantId": "...",
    "properties": [
        {"type": "color", "value": "green"},
        {"type": "perennial", "value": true},
        {"type": "max_height", "value": 3.3, "units": "metre"},
        {"type": "flowering_period", "from": 5, "to": 6, "units": "month"}
    ]
}

В результате, чтобы использовать данные нам нужно держать в голове все таблицы свойств и связи, что довольно не просто. Даже наличие документации не дает возможности быстро внести изменения в эту структуру или просто понять, как все работает. Код получается довольно сильно привязан к структуре данных что вызывает множество изменений при доработке схемы.

Хранилище без схемы

Для упрощения кода и упрощения структуры мы можем реализовать схему, которая основана на JSON и не имеет строгой структуры в базе данных.

Несмотря на то, что JSON будет без схемы, мы можем использовать constraints для предоставления гарантий консистентности данных.

Для хранения свойств будем использовать такую схему:

CREATE TABLE IF NOT EXISTS plant_properties (
    id UUID NOT NULL PRIMARY KEY,
    plant_id UUID NOT NULL,
    property jsonb NOT NULL
);

INSERT INTO plant_properties (id, plant_id, property) VALUES
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "color", "value": "green"}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "perennial", "value": true}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "max_height", "value": 1.1, "units": "metre"}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "flowering_period", "from": 5, "to": 6, "units": "month"}')
;

В такой схеме мы сможем:

• максимально изолировать код, который специфичен для конкретного типа свойств
• упростить понимание того, как хранятся данные в DB
• гарантировать консистентность данных на столько на сколько это возможно с JSON

Есть альтернативный вариант хранения JSON, и он мне не нравится

CREATE TABLE IF NOT EXISTS plant_properties (
    plant_id UUID NOT NULL PRIMARY KEY,
    payload jsonb NOT NULL
);

Не нравится мне это решение:

• поле payload будет расти со временем
• любое обновление payload вызывает блокировку всех свойств растения
• чтение одного свойства приводит к чтению всех свойств растения

В результате схема с JSON, где одна запись - это одно свойство, выглядит более привлекательным и надежной, хотя и немного усложняет чтение всех свойств одного растения.

Новая схема для свойств растений

DROP INDEX IF EXISTS property_json_idx;
DROP TABLE IF EXISTS plant_properties;
DROP TABLE IF EXISTS property_type;
DROP TABLE IF EXISTS property_group;
DROP TABLE IF EXISTS pool_names;

CREATE TABLE IF NOT EXISTS pool_names
(
    id uuid NOT NULL,
    ru character(255),
    en character(255),
    CONSTRAINT pool_names_pkey PRIMARY KEY (id),
    CONSTRAINT any_name_must_exists CHECK (
        ru IS NOT NULL AND char_length(ru) > 0 OR
        en IS NOT NULL AND char_length(en) > 0
    )
)

CREATE TABLE IF NOT EXISTS property_group
(
    id uuid NOT NULL,
    name_id uuid NOT NULL,
    CONSTRAINT property_group_pkey PRIMARY KEY (id),
    CONSTRAINT property_group_name_id_fkey FOREIGN KEY (name_id)
        REFERENCES pool_names (id)
)

CREATE TABLE IF NOT EXISTS property_type
(
    id uuid NOT NULL,
    name_id uuid NOT NULL,
    group_id uuid,
    
    details jsonb NOT NULL,

    type_details_json_schema text,
    property_json_schema text,

    CONSTRAINT property_type_pkey PRIMARY KEY (id),

    CONSTRAINT property_type_group_id_fkey FOREIGN KEY (group_id)
        REFERENCES property_group (id),

    CONSTRAINT property_type_name_id_fkey FOREIGN KEY (name_id)
        REFERENCES pool_names (id)
)

CREATE TABLE IF NOT EXISTS plant_properties
(
    id uuid NOT NULL,
    plant_id uuid NOT NULL,
    property_type_id uuid NOT NULL,
    
    property jsonb NOT NULL,

    CONSTRAINT plant_properties_pkey PRIMARY KEY (id),
    
    CONSTRAINT plant_properties_property_type_id_fkey FOREIGN KEY (property_type_id)
        REFERENCES property_type (id),
    
    CONSTRAINT type_must_exists CHECK (
        (property -> 'type_id') IS NOT NULL
    ),

    CONSTRAINT type_is_string CHECK (
        jsonb_typeof(property -> 'type_id') = 'string'
    ),
    
    CONSTRAINT type_same_as_in_table CHECK (
        property_type_id = ((property ->> 'type_id')::uuid)
    ),

    -- CONSTRAINT property_validation CHECK (
    --     jsonb_typeof(property -> 'min_value') = 'number' AND
    --     jsonb_typeof(property -> 'max_value') = 'number' AND
    --     (property -> 'max_value') >= (property -> 'min_value')
    -- )
)

CREATE INDEX IF NOT EXISTS property_json_idx
	ON plant_properties
	USING gin (property jsonb_path_ops);