DB 설계 A to Z 6강 — ER → 관계형 변환

여기서부터 Part 2 "논리 설계"입니다. 1~5강에서 만든 개념 모델을 관계형 테이블로 옮깁니다.

1. 변환 규칙 요약

ER 요소	관계형 변환
강한 엔티티	테이블 1개, 식별자 → PK
약한 엔티티	테이블 1개, PK = 소유 엔티티 PK + 부분키
복합 속성	하위 속성을 각각 컬럼으로
다중값 속성	별도 테이블 (소유 PK + 값)
1:1 관계	한쪽에 FK + UNIQUE, 또는 병합
1:N 관계	N 쪽에 FK
N:M 관계	교차(연관) 테이블
삼항 이상 관계	참여 엔티티 PK를 모두 가진 테이블
재귀 관계	같은 테이블을 참조하는 FK

2. 1:N 관계

sql

-- 주문(1) : 주문항목(N) → N 쪽(주문항목)에 FK
CREATE TABLE orders (
    order_no   VARCHAR(10) PRIMARY KEY,
    order_type VARCHAR(20) NOT NULL
);
 
CREATE TABLE order_item (
    item_id   BIGINT PRIMARY KEY,
    order_no  VARCHAR(10) NOT NULL REFERENCES orders(order_no),  -- 필수 참여 → NOT NULL
    item_seq  INT         NOT NULL,                              -- 주문 내 항목 순번
    item_name VARCHAR(20) NOT NULL,
    UNIQUE (order_no, item_seq)
);

1 쪽(주문)에 주문항목 목록을 넣는 것은 다중값이므로 불가능합니다. FK는 항상 N 쪽에 둡니다.

3. N:M 관계

sql

CREATE TABLE stage (
    stage_code VARCHAR(10) PRIMARY KEY,
    stage_name VARCHAR(50) NOT NULL
);
 
CREATE TABLE item_stage (
    item_id     BIGINT      NOT NULL REFERENCES order_item(item_id),
    stage_code  VARCHAR(10) NOT NULL REFERENCES stage(stage_code),
    plan_date   DATE NOT NULL,
    actual_date DATE,
    PRIMARY KEY (item_id, stage_code)
);

관계의 속성(계획일, 실적일)은 교차 테이블의 컬럼이 됩니다.

PK 컬럼 순서 결정: (item_id, stage_code)는 "주문항목의 처리단계 목록" 조회에 유리합니다. "처리단계 기준 주문항목 목록" 조회가 많다면 (stage_code, item_id) 인덱스를 추가합니다. 둘 다 빈번하면 두 방향 인덱스가 모두 필요합니다.

4. 1:1 관계의 세 가지 변환

주문 ─ 결제가 1:1일 때:

방식	구조	적합한 경우
A. 병합	orders 테이블에 결제 컬럼 추가	양쪽 모두 필수, 항상 함께 조회
B. 선택 쪽에 FK	payment.order_no FK + UNIQUE	결제가 없는 주문이 존재 (부분 참여)
C. PK 공유	payment.order_no가 PK이자 FK	B와 유사, 조인 단순, 1:1이 구조적으로 보장

sql

-- 방식 C
CREATE TABLE payment (
    order_no      VARCHAR(10) PRIMARY KEY REFERENCES orders(order_no),
    paid_date     DATE NOT NULL,
    customer_name VARCHAR(100) NOT NULL
);

FK만 두고 UNIQUE를 빠뜨리면 1:N이 됩니다. 방식 B에서는 UNIQUE가 1:1을 보장하는 유일한 장치입니다.

분리를 선택하는 추가 이유:

크고 드물게 조회되는 컬럼(첨부 문서, 긴 텍스트) 분리 → 행 크기 감소
보안 등급이 다른 컬럼 분리 → 권한을 테이블 단위로 부여

5. 다중값 속성

sql

-- 담당자의 보유 취급 자격 (다중값)
CREATE TABLE picker_certificate (
    picker_id   BIGINT      NOT NULL REFERENCES picker(picker_id),
    cert_code   VARCHAR(20) NOT NULL,
    acquired_on DATE,
    PRIMARY KEY (picker_id, cert_code)
);

6. 약한 엔티티

재처리가 허용되는 항목처리 차수:

sql

CREATE TABLE item_stage_round (
    item_id     BIGINT      NOT NULL,
    stage_code  VARCHAR(10) NOT NULL,
    round_no    SMALLINT    NOT NULL,   -- 부분키
    started_at  TIMESTAMP,
    finished_at TIMESTAMP,
    PRIMARY KEY (item_id, stage_code, round_no),
    FOREIGN KEY (item_id, stage_code) REFERENCES item_stage (item_id, stage_code)
        ON DELETE CASCADE
);

약한 엔티티는 소유 엔티티 없이 존재할 수 없으므로 ON DELETE CASCADE가 자연스럽습니다.

7. 삼항 관계

sql

-- 담당자가 주문항목의 처리단계를 수행 (처리일 단위)
CREATE TABLE fulfillment_log (
    log_id     BIGINT PRIMARY KEY,
    picker_id  BIGINT      NOT NULL REFERENCES picker(picker_id),
    item_id    BIGINT      NOT NULL,
    stage_code VARCHAR(10) NOT NULL,
    work_date  DATE        NOT NULL,
    man_hours  NUMERIC(5,2) NOT NULL CHECK (man_hours > 0),
    FOREIGN KEY (item_id, stage_code) REFERENCES item_stage (item_id, stage_code),
    UNIQUE (picker_id, item_id, stage_code, work_date)
);

FK를 order_item과 stage에 각각 거는 대신 item_stage에 복합 FK를 걸었습니다. 이렇게 하면 "계획되지 않은 처리단계에 실적이 들어가는 것" 을 DB가 막아줍니다. 이런 선택이 논리 설계에서 업무 규칙을 구조로 표현하는 방법입니다.

8. 재귀 관계

sql

CREATE TABLE picker (
    picker_id  BIGINT PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    leader_id  BIGINT REFERENCES picker(picker_id)   -- (0,1) 이므로 NULL 허용
);

N:M 재귀(예: 세트/번들 상품 구성, 한 상품이 여러 상위 상품에 사용됨)는 교차 테이블로 변환합니다.

sql

CREATE TABLE bundle_bom (
    parent_product_id BIGINT NOT NULL REFERENCES product(product_id),
    child_product_id  BIGINT NOT NULL REFERENCES product(product_id),
    quantity          NUMERIC(10,3) NOT NULL,
    PRIMARY KEY (parent_product_id, child_product_id),
    CHECK (parent_product_id <> child_product_id)
);

6강 정리

FK는 N 쪽에, N:M은 교차 테이블로, 다중값 속성은 별도 테이블로 변환한다.
1:1은 병합·FK+UNIQUE·PK 공유 중 선택하고, UNIQUE 누락에 주의한다.
삼항 관계에서 복합 FK의 참조 대상을 선택하면 업무 규칙을 구조로 강제할 수 있다.