Проектирование базы изделий (ведомость состава / BOM)

60. Спроектировать схему БД для состава изделия (BOM)

Условие задачи:
Необходимо спроектировать схему БД для хранения состава изделия — BOM (Bill of Materials).

Пример структуры:

Датчик
  Корпус — 1 шт
    Винт — 8 шт
  Винт — 4 шт

Необходимо:

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

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

Подсказки

💡 Раздели нормативно-справочную информацию и структуру изделия.
💡 В таблице items храни сами изделия и детали.
💡 В таблице bom храни связь «родительское изделие → компонент» и количество.
💡 Уровень вложенности хранить необязательно — его можно определить при рекурсивном обходе BOM.
💡 Одна и та же запись из items может встречаться во множестве строк bom.

Решение

Для базовой модели достаточно двух таблиц:

CREATE TABLE items (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    item_code VARCHAR(50) UNIQUE,
    item_type VARCHAR(50),
    unit VARCHAR(20) NOT NULL DEFAULT 'шт',
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE bom (
    id BIGSERIAL PRIMARY KEY,
    parent_item_id BIGINT NOT NULL
        REFERENCES items(id),
    component_item_id BIGINT NOT NULL
        REFERENCES items(id),
    quantity NUMERIC(12, 3) NOT NULL
        CHECK (quantity > 0),
    position INT,

    CHECK (parent_item_id <> component_item_id)
);

items — единый справочник номенклатуры:

id | name    | item_type
---+---------+----------
1  | Датчик  | ASSEMBLY
2  | Корпус  | ASSEMBLY
3  | Винт    | PART

bom описывает непосредственный состав каждого изделия:

id | parent_item_id | component_item_id | quantity
---+----------------+-------------------+---------
1  | 1              | 2                 | 1
2  | 1              | 3                 | 4
3  | 2              | 3                 | 8

Например:

INSERT INTO bom (
    parent_item_id,
    component_item_id,
    quantity,
    position
)
VALUES
    (1, 2, 1, 1), -- Датчик -> Корпус: 1 шт
    (1, 3, 4, 2), -- Датчик -> Винт: 4 шт
    (2, 3, 8, 1); -- Корпус -> Винт: 8 шт

Одна и та же деталь Винт хранится в items только один раз, но может использоваться в разных строках bom с разным количеством.

Связи выглядят так:

items
  1 Датчик
       │
       ├── 1 × Корпус ─── 8 × Винт
       │
       └── 4 × Винт

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

Например, получить всё дерево изделия в PostgreSQL можно через рекурсивный CTE:

WITH RECURSIVE product_tree AS (
    SELECT
        b.parent_item_id,
        b.component_item_id,
        b.quantity,
        1 AS depth
    FROM bom b
    WHERE b.parent_item_id = 1

    UNION ALL

    SELECT
        b.parent_item_id,
        b.component_item_id,
        pt.quantity * b.quantity,
        pt.depth + 1
    FROM bom b
    JOIN product_tree pt
        ON b.parent_item_id = pt.component_item_id
)
SELECT
    i.id,
    i.name,
    pt.quantity,
    pt.depth
FROM product_tree pt
JOIN items i
    ON i.id = pt.component_item_id;

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

CREATE INDEX idx_bom_parent
    ON bom (parent_item_id);

CREATE INDEX idx_bom_component
    ON bom (component_item_id);

Итого:

items 1 ─── N bom N ─── 1 items
        parent      component

items отвечает на вопрос «что это за изделие или деталь», а bom — «из каких компонентов и в каком количестве оно состоит».