DB 설계 A to Z 25강 — 종합 실습

[주문] ┼┼───┼< [주문항목]
[상품] ┼┼───○< [출고 단위] >○───┼┼ [주문]
                   │
              >○───┼┼ [주문항목]  (소속 주문항목)
              >○───┼┼ [출고 창고]  (현재 출고 창고, 가변)
[셀러] ┼┼───○< [포장 차수] >┼───┼┼ [출고 단위]
[포장 차수] ┼┼───○< [검수]
[상품] ── 슈퍼타입 {의류, 전자, 도서} (배타, 완전)

-- 입점 셀러 (코드 마스터, 자연키)
CREATE TABLE seller (
    seller_code VARCHAR(10) PRIMARY KEY,
    seller_name VARCHAR(100) NOT NULL
);
 
-- 출고 창고 (코드 마스터)
CREATE TABLE fulfillment_center (
    fc_id   INT PRIMARY KEY,
    fc_name VARCHAR(50) NOT NULL
);
 
CREATE TABLE orders (
    order_no   VARCHAR(10) PRIMARY KEY,
    order_type VARCHAR(20) NOT NULL
);
 
CREATE TABLE order_item (
    item_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no              VARCHAR(10) NOT NULL REFERENCES orders,
    item_seq              INT NOT NULL,                 -- 주문 내 항목 순번
    item_name             VARCHAR(20) NOT NULL,         -- 주문 한 줄에 표시되는 표기 — 상품명에 옵션·수량을 붙인 값. 상품명 단독 값은 아래 스냅샷이 갖는다
    product_name_snapshot VARCHAR(200) NOT NULL,        -- 주문 시점 스냅샷 (원본이 바뀌어도 갱신하지 않는다)
    unit_price_snapshot   NUMERIC(12,2) NOT NULL,       -- 주문 시점 스냅샷 (원본이 바뀌어도 갱신하지 않는다)
    UNIQUE (order_no, item_seq),
    UNIQUE (item_id, order_no)                          -- 복합 FK 대상
);
 
-- 슈퍼타입 + 서브타입 (11강 전략 C)
CREATE TABLE product (
    product_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku_code     VARCHAR(30) NOT NULL UNIQUE,           -- 자연키 보호
    product_type CHAR(2) NOT NULL CHECK (product_type IN ('AP','EL','BK')),  -- 유형코드(2자). 19·20강의 product_type은 유형명이다
    description  VARCHAR(200),
    UNIQUE (product_id, product_type)
);
 
CREATE TABLE apparel (
    product_id   BIGINT PRIMARY KEY,
    product_type CHAR(2) NOT NULL DEFAULT 'AP' CHECK (product_type = 'AP'),
    size_label   VARCHAR(10) NOT NULL,
    material     VARCHAR(20) NOT NULL,
    FOREIGN KEY (product_id, product_type) REFERENCES product (product_id, product_type)
);
 
CREATE TABLE electronics (
    product_id      BIGINT PRIMARY KEY,
    product_type    CHAR(2) NOT NULL DEFAULT 'EL' CHECK (product_type = 'EL'),
    warranty_months SMALLINT NOT NULL CHECK (warranty_months > 0),
    model_no        VARCHAR(30) NOT NULL,
    FOREIGN KEY (product_id, product_type) REFERENCES product (product_id, product_type)
);
 
CREATE TABLE book (
    product_id   BIGINT PRIMARY KEY,
    product_type CHAR(2) NOT NULL DEFAULT 'BK' CHECK (product_type = 'BK'),
    isbn         VARCHAR(13) NOT NULL,
    author       VARCHAR(100) NOT NULL,
    FOREIGN KEY (product_id, product_type) REFERENCES product (product_id, product_type)
);
 
-- 출고 단위
CREATE TABLE ship_unit (
    unit_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES product,
    order_no   VARCHAR(10) NOT NULL,
    serial_no  SMALLINT NOT NULL,
    item_id    BIGINT NOT NULL,
    fc_id      INT NOT NULL REFERENCES fulfillment_center,  -- 현재 출고 창고 (가변)
    plan_date  DATE NOT NULL,
    UNIQUE (product_id, order_no, serial_no),               -- 자연키
    FOREIGN KEY (item_id, order_no) REFERENCES order_item (item_id, order_no)  -- 주문항목이 같은 주문 소속임을 보장
);
CREATE INDEX ix_unit_item  ON ship_unit (item_id);
CREATE INDEX ix_unit_order ON ship_unit (order_no);
 
-- 포장 차수 (약한 엔티티, 월 파티셔닝)
CREATE TABLE pack_round (
    unit_id     BIGINT NOT NULL,
    round_no    SMALLINT NOT NULL,
    seller_code VARCHAR(10) NOT NULL REFERENCES seller,
    status      VARCHAR(10) NOT NULL
                CHECK (status IN ('ASSIGNED','PACKED','PASSED','FAILED')),
    packed_on   DATE,
    man_hours   NUMERIC(6,2) CHECK (man_hours >= 0),
    work_month  DATE NOT NULL,                          -- 파티션 키 (배정 월 1일)
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (unit_id, round_no, work_month),
    CHECK (EXTRACT(DAY FROM work_month) = 1),
    CHECK (status = 'ASSIGNED' OR packed_on IS NOT NULL)
) PARTITION BY RANGE (work_month);
 
CREATE INDEX ix_round_seller ON pack_round (seller_code, work_month);
CREATE INDEX ix_round_unit   ON pack_round (unit_id);

ALTER TABLE pack_round ENABLE ROW LEVEL SECURITY;
CREATE POLICY seller_own_rows ON pack_round
  FOR SELECT TO seller_role
  USING (seller_code = current_setting('app.seller_code'));

CREATE TABLE fact_pack_monthly_snapshot (
    month_key    INT NOT NULL,              -- 202609
    item_sk      BIGINT NOT NULL,           -- SCD Type 2 대리키
    product_type CHAR(2) NOT NULL,
    planned_cnt  INT NOT NULL,              -- 분모
    packed_cnt   INT NOT NULL,              -- 분자
    passed_cnt   INT NOT NULL,
    man_hours    NUMERIC(12,2) NOT NULL,
    closed_at    TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (month_key, item_sk, product_type)
);