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

Существует множество подходов к реализации таких требований. Это и nosql решения и механизмы работы с json в реляционных СУБД. До nosql эпохи, решать такие задачи приходилось средствами реляционных БД.

Основная причина по которой реляционные СУБД плохо подходят для решения таких задач это разнообразие характеристик товаров. Набор характеристик к примеру для одежды и смартфонов будет совершенно разный. В самом деле не создавать же для каждой категории товаров отдельную таблицу с со своим набором реквизитов.

По этой причине в большинстве случаев в реляционных БД используется EAV (Entity Attribute Value) модель данных в тех или иных вариациях.

Недостатков у такой модели множество. Чаще всего EAV модель критикуют чрезмерную сложность, так же за то, что по сути схема данных храниться в самих данных.

Есть мнение, что EAV вообще является анти паттерном, что тоже не лишено оснований, однако надо заметить, что есть и другое мнение, что лучше такая схема, чем вообще отсутствие таковой.

Рискуя навлечь на себя гнев сообщества хочу представить свой вариант реализации каталога. Это не совсем EAV, скорее его по мотивам.

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

Всё описанное далее предполагает использование СУБД Postgresql.

Довольно преамбул, вот упрощённая ER-диаграмма.

Из диаграммы видно, что товар в таблице products ссылается на категорию из таблицы product_categories.

Таблица product_categories может иметь иерархическую структуру.

На таблицу products ссылаются три таблицы:

1. Цены товаров  (product_prices)

2. Фото товаров (products_images)

3. Значения свойств характеристик товаров (property_values).

Все значения характеристик всех товаров хранятся целочисленном поле таблицы property_values.

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

Текстовое представление ссылочных значений хранятся в таблице property_values_references.

Эта таблица не принимает участие в основных выбирающих запросах и нужна только для формирования в клиентском приложении выпадающих списков возможных вариантов отбора.

Она ссылается на таблицу свойств категорий (product_category_properties) полем property_id.

Таблица свойств категорий (product_category_properties) полем parent_id ссылается на саму себя, что позволяет реализовать в клиентском приложении каскадные фильтры неограниченной вложенности.

BEGIN;

CREATE TABLE IF NOT EXISTS product_categories
(
    id serial NOT NULL,
    parent_id integer REFERENCES product_categories (id),
    title character varying(64),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS products
(
    id serial NOT NULL,
    category_id integer NOT NULL REFERENCES product_categories (id),
    title character varying(128) NOT NULL,
    description text NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS id_and_category_id
    ON products USING btree
    (id ASC NULLS LAST, category_id ASC NULLS LAST);
    
DO $$ /* вот так, поскольку CREATE TYPE не поддерживает IF NOT EXISTS параметр */
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'property_value_type') THEN
        CREATE TYPE property_value_type AS enum ('boolean', 'integer', 'reference');
    END IF;
END
$$;

CREATE TABLE IF NOT EXISTS product_category_properties
(
    id serial NOT NULL,
    parent_id integer REFERENCES product_category_properties (id),
    category_id integer NOT NULL REFERENCES product_categories (id),
    title character varying(64) NOT NULL,
    property_type property_value_type NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS property_values
(
    property_id integer NOT NULL REFERENCES product_category_properties (id),
    product_id integer NOT NULL REFERENCES products (id),
    value smallint NOT NULL,
    PRIMARY KEY (property_id, product_id)
);

CREATE INDEX IF NOT EXISTS property_id_and_value_and_product_id
    ON property_values USING btree
    (property_id ASC NULLS LAST, value ASC NULLS LAST, product_id ASC NULLS LAST);

CREATE TABLE IF NOT EXISTS product_prices
(
    product_id integer NOT NULL REFERENCES products (id),
    period timestamp NOT NULL,
    price integer NOT NULL,
    PRIMARY KEY (product_id, period)
);

CREATE INDEX IF NOT EXISTS product_id_and_price_and_period
    ON product_prices USING btree
    (product_id ASC NULLS LAST, price ASC NULLS LAST, period ASC NULLS LAST);

CREATE TABLE IF NOT EXISTS product_images
(
    id serial NOT NULL,
    product_id integer NOT NULL REFERENCES products (id),
    title character varying(64) NOT NULL,
    filename character varying(64) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS property_value_references
(   
    id integer NOT NULL,
    property_id integer NOT NULL REFERENCES product_category_properties (id),
    value character varying(128) NOT NULL,
    PRIMARY KEY (id, property_id)
);

END;

У предложенной схемы есть положительный side-эффект, ничто не мешает нам наследовать свойства вышестоящих категорий.

К примеру, у корневой или любой категории в иерархии есть некоторые свойства.

Следующий запрос вернёт все свойства указанной категории плюс свойства всех его родительских категорий.

Будет полезен при формировании формы фильтра на клиенте.

WITH RECURSIVE r AS (
    SELECT * FROM product_categories
    WHERE id = ?
		UNION ALL
    SELECT product_categories.*
    FROM product_categories JOIN r ON r.parent_id = product_categories.id
)
SELECT pcp.id, pcp.parent_id, pcp.category_id, pcp.property_type, pcp.title
FROM product_category_properties pcp
WHERE pcp.category_id IN (SELECT id FROM r)

Следующий запрос вернёт все свойства, как заполненные значениями так и пустые.

Будет полезен при динамическом формировании формы создания, редактирования и просмотра карточки товара.

WITH RECURSIVE r AS (
    SELECT * FROM product_categories
		WHERE id = (
    		SELECT category_id FROM products
    		WHERE id = ?
		)
		UNION ALL
    SELECT product_categories.*
    FROM product_categories JOIN r ON r.parent_id = product_categories.id
)
SELECT pcp.id, pcp.parent_id, pcp.category_id, pcp.value_type, pcp.title, pv.value
FROM product_category_properties pcp
LEFT JOIN property_values pv ON pcp.id = pv.property_id AND product_id = ?
WHERE pcp.category_id IN (SELECT id FROM r)

Следующий запрос вернёт наименование, описание и последнюю цену товара.

SELECT id, title, description, price
FROM products p
LEFT JOIN product_prices pp ON p.id = pp.product_id AND pp.period = (
    SELECT MAX(period)
    FROM product_prices
    WHERE pp.product_id = product_prices.product_id
) 
WHERE p.id = ?

Вариантов основного для каталога выбирающего запроса, множество, но все они - разновидности двух подходов: правильного, и не правильного :).

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

SELECT id
FROM products
WHERE id IN (
    SELECT product_id
    FROM product_prices pp
    WHERE period = (
        SELECT max(period)
        FROM product_prices
        WHERE pp.product_id = product_prices.product_id 
    ) AND ("price" >= ? AND "price" <= ?) AND product_id IN (
        SELECT product_id 
        FROM property_values
        WHERE (property_id = ? AND value = ?) 
        OR (property_id = ? AND value = ?) 
        OR (property_id = ? AND value = ?) 
        OR (property_id = ? AND value = ?)
        OR (property_id = ? AND value = ?)
        OR (property_id = ? AND value = ?)
        OR (property_id = ? AND value = ?)
        GROUP BY product_id HAVING COUNT(*) = ? /* здесь количество условий, в данном случае должно быть 7 */
    )
) AND category_id = ?

На первый взгляд выглядит элегантным, однако, такой запрос не может полноценно использовать индекс и выполняется не самым быстрым образом. Следующий вариант гораздо быстрее.

SELECT id 
FROM products p
JOIN property_values pv ON (p.id = pv.product_id AND pv.property_id = 1 AND pv.value >= ?) 
JOIN property_values pv2 ON (pv.product_id = pv2.product_id AND pv2.property_id = ? AND pv2.value = ?)
JOIN property_values pv3 ON (pv.product_id = pv3.product_id AND pv3.property_id = ? AND pv3.value = ?)
JOIN property_values pv4 ON (pv.product_id = pv4.product_id AND pv4.property_id = ? AND pv4.value = ?)
JOIN property_values pv5 ON (pv.product_id = pv5.product_id AND pv5.property_id = ? AND pv5.value = ?) 
JOIN property_values pv6 ON (pv.product_id = pv6.product_id AND pv6.property_id = ? AND pv6.value = ?)
JOIN property_values pv7 ON (pv.product_id = pv7.product_id AND pv7.property_id = ? AND pv7.value = ?)
JOIN product_prices pp ON p.id = pp.product_id AND pp.period = (
    SELECT MAX(period)
    FROM product_prices
    WHERE pp.product_id = product_prices.product_id AND "price" >= ? AND "price" <= ?
) 
WHERE p.category_id = ?

Но самым быстрым оказался следующий вариант запроса.

SELECT id 
FROM products p
JOIN (
    SELECT pv.product_id 
    FROM property_values pv 
    JOIN property_values pv2 ON pv.product_id = pv2.product_id AND pv2.property_id = ? AND pv2.value = ? 
    JOIN property_values pv3 ON pv.product_id = pv3.product_id AND pv3.property_id = ? AND pv3.value = ?
    JOIN property_values pv4 ON pv.product_id = pv4.product_id AND pv4.property_id = ? AND pv4.value = ?
    JOIN property_values pv5 ON pv.product_id = pv5.product_id AND pv5.property_id = ? AND pv5.value = ? 
    JOIN property_values pv6 ON pv.product_id = pv6.product_id AND pv6.property_id = ? AND pv6.value = ?
    JOIN property_values pv7 ON pv.product_id = pv7.product_id AND pv7.property_id = ? AND pv7.value = ?
    WHERE pv.property_id = ? AND pv.value >= ? 
) AS pv ON p.id = pv.product_id 
JOIN product_prices pp ON p.id = pp.product_id AND pp.period = (
    SELECT MAX(period)
    FROM product_prices
    WHERE pp.product_id = product_prices.product_id AND "price" >= ? AND "price" <= ?
) 
WHERE category_id = ?

Очень просто, не правда ли?

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

Такой запрос легко генерируется алгоритмически используя параметры из url вида:

?filter[price][gte]=100&filter[price][lte]=1000&filter[property][1][eq]=1&filter[property][2][gte]=1&filter[property][3][lte]=1 и т.д. и позволяет накладывать любые фильтры ( >, <, >=, <=, =, <>, in, not in) в любых комбинациях.

Используя индексы созданные по полям property_id, value, product_id таблицы property_values и product_id, price, period таблицы product_prices получилось не только избавиться от полного сканирования таблиц, а добиться "index scan only" на всех этапах составления плана запроса оптимизатором.

Правда тут есть нюанс, при большом количестве условий выборки, возрастает время оптимизации плана запроса планировщиком и уже после 6 условий, время планирования превышает время исполнения запроса.

Поэтому, чтобы указать планировщику, что бы он не пытался искать оптимальный порядок соединений можно уменьшить join_collapse_limit параметр конфигурации postgresql, по умолчанию он равен 8.

Тестирование

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

-- Сгенерировать 5 корневых категорий
TRUNCATE product_categories  RESTART IDENTITY CASCADE;
INSERT INTO product_categories(id, parent_id, title)
VALUES (1, null, 'Product category 1'),
       (2, null, 'Product category 2'),
       (3, null, 'Product category 3'),
       (4, null, 'Product category 4'),
       (5, null, 'Product category 5');
       
-- 25 дочерних категорий 
INSERT INTO product_categories(id, parent_id, title)
SELECT id+1, (id/5), 'Product category ' || id+1
FROM generate_series(5, 29) id;

-- 300 свойств категорий
TRUNCATE product_category_properties  RESTART IDENTITY CASCADE;
INSERT INTO product_category_properties(id, title, parent_id, category_id, value_type)
SELECT id+1, 'Product category property ' || id+1, null, (SELECT * FROM generate_series(6, 30, 1) LIMIT 1 OFFSET (id/12)),
(array['boolean', 'boolean', 'boolean', 'boolean', 'boolean', 'integer', 'reference'])[ceil(random() * 7)]::property_value_type
FROM generate_series(0, 299) id;

-- 1 млн. товаров
TRUNCATE products  RESTART IDENTITY CASCADE;
INSERT INTO products(id, title, description, category_id)
SELECT id+1 as id, 'Product title ' || id+1, 'Product title ' || id+1, (SELECT * FROM generate_series(6, 30, 1) LIMIT 1 OFFSET id/40000) as category_id
FROM generate_series(0, 999999) id;

-- 2 млн. цен товаров
TRUNCATE product_prices;
INSERT INTO product_prices(product_id, price, period)
SELECT (id/2)+1, floor(random()*(10000-1+1))+1, now() -  (
    interval '1 day' * round(random() * 100) + interval '1 hours' * round(random() * 100) +    interval '1 minutes' * round(random() * 100) +
    interval '1 seconds' * round(random() * 100) +    interval '1 milliseconds' * round(random() * 100)) as timestamp
FROM generate_series(0, 1999999) id;

/* 12 млн. значений свойств товаров */
TRUNCATE property_values;
INSERT INTO property_values(product_id, property_id, value)
SELECT  
    (
        SELECT id FROM products WHERE id = 
        (
            SELECT CASE 
                WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
                WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
                ELSE pid+1
            END
        )
    ) AS product_id,
    (
        SELECT id FROM product_category_properties WHERE category_id = ( 
            SELECT category_id FROM products WHERE id = (
                SELECT CASE 
                    WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
                    WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
                    ELSE pid+1
                END
            )
        ) ORDER BY id LIMIT 1 OFFSET pid/1000000
    ) AS property_id,
    (
        SELECT 
            CASE 
                WHEN (
                    SELECT value_type FROM product_category_properties WHERE category_id = ( 
                        SELECT category_id FROM products WHERE id = (
                            SELECT CASE 
                                WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
                                WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
                                ELSE pid+1
                            END
                        )
                    ) ORDER BY id LIMIT 1 OFFSET pid/1000000
                ) = 'reference' THEN ceil(random()*10)
                WHEN (
                    SELECT value_type FROM product_category_properties WHERE category_id = ( 
                        SELECT category_id FROM products WHERE id = (
                            SELECT CASE 
                                WHEN mod((pid+1)::numeric, (1000000)::numeric) = 0 THEN 1000000
                                WHEN (pid+1/1000000)::numeric > 0 THEN ((((pid+1)::numeric) / (1000000::numeric) - (pid+1)/1000000) * 1000000)::integer
                                ELSE pid+1
                            END
                        )
                    ) ORDER BY id LIMIT 1 OFFSET pid/1000000
                ) = 'integer' THEN ceil(random()*100)
                ELSE 1
            END
    ) AS value
FROM generate_series(0, 11999999) pid;
-- Таким образом все товары в одинаковых категориях имеют полный набор значений всех доступных свойств

-- Теперь удалить половину всех значений свойств, чтобы обеспечить правдоподобное распределение
DELETE FROM property_values WHERE ctid = ANY (
    SELECT ctid FROM property_values TABLESAMPLE BERNOULLI(50)
);

Выводы

На базе заполненной случайными данными в количестве: 1 млн. товаров в 25 категориях (+5 корневые), 300 свойств категорий, 6 млн. значений свойств категорий и весьма скромной машине Intel Pentium G6400 4GHz + 16Gb + HDD, такой запрос отрабатывает за 35 - 60 ms.

На самом деле, на практике мне не приходилось иметь дело с таким количеством товаров в каталоге. От нескольких сотен до полутора тысяч максимум, но длинная изоляция сподвигла на тест и статью.

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