Проектирование таблиц «Продукты» и «Наличие товаров по магазинам» | IT Ментор

46. Спроектировать хранение товаров и остатков по магазинам

Условие задачи:
Необходимо спроектировать структуру таблиц для сети магазинов, чтобы можно было получить:

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

Спойлеры к решению

Подсказки

💡 Магазины и товары связаны отношением «многие ко многим».
💡 Для связи нужна промежуточная таблица с количеством товара.
💡 Пара (store_id, product_id) должна быть уникальной.
💡 Для поиска товара по всем магазинам пригодится отдельный индекс по product_id.
💡 Отсутствующим можно считать товар с quantity = 0 или без записи в таблице остатков.

Решение

Структура таблиц:

CREATE TABLE store (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    sku TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE store_inventory (
    store_id BIGINT NOT NULL
        REFERENCES store(id),
    product_id BIGINT NOT NULL
        REFERENCES product(id),
    quantity INTEGER NOT NULL
        CHECK (quantity >= 0),

    PRIMARY KEY (store_id, product_id)
);

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

CREATE INDEX idx_store_inventory_product
    ON store_inventory (product_id, store_id);

Товары, которые есть в конкретном магазине:

SELECT
    p.name,
    si.quantity
FROM store_inventory si
JOIN product p
    ON p.id = si.product_id
WHERE si.store_id = 1
  AND si.quantity > 0;

Магазины, в которых есть конкретный товар:

SELECT
    s.name,
    si.quantity
FROM store_inventory si
JOIN store s
    ON s.id = si.store_id
WHERE si.product_id = 2
  AND si.quantity > 0;

Общее количество каждого товара по всем магазинам:

SELECT
    p.id,
    p.name,
    COALESCE(SUM(si.quantity), 0) AS total_quantity
FROM product p
LEFT JOIN store_inventory si
    ON si.product_id = p.id
GROUP BY
    p.id,
    p.name;

Товары, отсутствующие в конкретном магазине:

SELECT
    p.id,
    p.name
FROM product p
LEFT JOIN store_inventory si
    ON si.product_id = p.id
   AND si.store_id = 1
WHERE COALESCE(si.quantity, 0) = 0;

Связь:

store 1 ─── N store_inventory N ─── 1 product

Таблица store_inventory хранит остаток конкретного товара в конкретном магазине. Составной первичный ключ не позволяет создать две записи для одной пары «магазин — товар».