Task Livecoding SQL Bom Products Schema

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

Условие задачи:
📌 Нужно спроектировать схему БД для хранения состава изделия (BOM).
Есть изделие и его состав:

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

Задача: какие будут таблицы и столбцы, чтобы:

  • хранить все изделия/детали (нормативка);

  • хранить структуру: какое изделие из каких компонентов состоит и в каком количестве;

  • поддерживать повторное использование одной и той же детали (например, «Винт» в разных местах и в разном количестве).

Спойлеры к решению
Подсказки

💡 Обычно делаем 2 таблицы:

  1. items — справочник изделий/деталей (что это за номенклатура).

  2. bom (bill of materials) — связи «родитель → компонент + количество».

💡 В bom нужны:

  • parent_item_id — что собираем,

  • component_item_id — из чего,

  • quantity — сколько штук,

  • опционально level / position / unit.

Решение

1. Таблица изделий / деталей #

CREATE TABLE items (
    id           BIGINT PRIMARY KEY,           -- 1, 2, 3 ...
    name         VARCHAR(255) NOT NULL,        -- "Датчик", "Корпус", "Винт"
    item_code    VARCHAR(50),                  -- артикул / внутр. код (опционально)
    item_type    VARCHAR(50),                  -- тип: 'ASSEMBLY', 'PART', 'RAW' и т.п.
    unit         VARCHAR(20) NOT NULL DEFAULT 'шт', -- базовая единица измерения
    is_active    BOOLEAN NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMP NOT NULL DEFAULT now(),
    updated_at   TIMESTAMP NOT NULL DEFAULT now()
);

Примеры данных для твоего случая:

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

2. Таблица структуры изделия (BOM) #

CREATE TABLE bom (
    id                BIGINT PRIMARY KEY,
    parent_item_id    BIGINT NOT NULL,      -- изделие, которое собираем (например, "Датчик")
    component_item_id BIGINT NOT NULL,      -- компонент (например, "Корпус", "Винт")
    quantity          NUMERIC(12, 3) NOT NULL, -- количество компонента на 1 ед. родителя
    unit              VARCHAR(20) NOT NULL DEFAULT 'шт', -- ед. измерения (шт, кг, м и т.п.)
    position          INT,                 -- позиция в спецификации (для печатных форм)
    level_hint        INT,                 -- опционально: уровень вложенности (1,2,3 ...)
    CONSTRAINT fk_bom_parent
        FOREIGN KEY (parent_item_id) REFERENCES items(id),
    CONSTRAINT fk_bom_component
        FOREIGN KEY (component_item_id) REFERENCES items(id)
);

Данные для примера:

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

-- Корпус состоит из:
-- 3) Винт 8 шт
INSERT INTO bom (id, parent_item_id, component_item_id, quantity, unit, position, level_hint)
VALUES
(3, 2, 3, 8, 'шт', 1, 2);    -- Корпус -> Винт 8 шт

Как читается структура:

  • parent_item_id = 1 ("Датчик"), component_item_id = 2 ("Корпус"), quantity = 1 →
    На один датчик нужен один корпус.

  • parent_item_id = 1 ("Датчик"), component_item_id = 3 ("Винт"), quantity = 4 →
    На один датчик нужно 4 винта «верхнего уровня».

  • parent_item_id = 2 ("Корпус"), component_item_id = 3 ("Винт"), quantity = 8 →
    На один корпус нужно 8 винтов.

Почему именно так:

  • Одна таблица items — один раз описывает, что это за деталь/изделие.

  • Таблица bom — описывает, как одно изделие собирается из других (родитель → компоненты).

  • Одна и та же деталь (Винт, id=3) может фигурировать в разных BOM с разным количеством и на разных уровнях вложенности — это как раз то, что нужно для нормальной спецификации.

Такой дизайн — классический для систем управления составом изделия (BOM/ERP/PLM).

Edit this page