ER 모델을 릴레이션으로 변환

ERD를 설계했다면 다음 단계는 개념적 모델을 실제 릴레이션 스키마로 옮기는 것입니다. 변환의 핵심은 개체를 테이블로 바꾸는 것에 그치지 않고, 식별자, 카디널리티, 참여 제약, 관계 속성, 생명주기 의존성을 PK/FK/UNIQUE/NOT NULL/CHECK 같은 제약으로 보존하는 것입니다.

이 절에서는 ER 모델을 릴레이션으로 변환하는 대표 규칙을 정리하고, 쇼핑몰 예제로 SQL DDL까지 연결합니다.

1단계: 강한 개체의 변환

강한 개체는 독립적으로 식별 가능한 개체입니다. 보통 하나의 테이블로 변환하고, 키 속성은 기본키(PK), 일반 속성은 컬럼이 됩니다.

복합 키를 가진 강한 개체라면 여러 컬럼을 묶어 기본키를 만들 수 있습니다.

강한 개체 변환 시 주의할 점입니다.

2단계: 약한 개체의 변환

약한 개체는 자체 키만으로 식별할 수 없고, 소유 개체의 키가 함께 필요합니다. 변환할 때는 소유 개체의 기본키를 외래키로 포함하고, 부분 키(Partial Key)와 합쳐서 기본키를 만듭니다.

약한 개체 변환 예시 (Oracle)

CREATE TABLE orders (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE order_items (
    order_id   NUMBER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    line_no    NUMBER NOT NULL,
    product_id NUMBER NOT NULL,
    quantity   NUMBER NOT NULL CHECK (quantity > 0),
    unit_price NUMBER(10, 2) NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

실무에서는 조회 편의나 ORM 매핑 때문에 대리키를 추가하기도 합니다. 이 경우에도 약한 개체의 식별 규칙이 사라지지 않도록 (소유자 FK, 부분 키)에 UNIQUE 제약을 둡니다.

3단계: 1:1 관계의 변환

1:1 관계는 세 가지 방식으로 변환할 수 있습니다. 어떤 방식이 좋은지는 선택 참여인지, 전체 참여인지, 두 개체의 생명주기가 같은지에 따라 달라집니다.

가장 흔한 방식은 한쪽 테이블에 FK를 두고 UNIQUE 제약을 추가하는 것입니다. 전체 참여 쪽이 있다면 그쪽에 NOT NULL FK를 두는 것이 자연스럽습니다.

1:1 관계 변환 예시 (Oracle)

CREATE TABLE orders (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE shipping (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    NUMBER NOT NULL UNIQUE REFERENCES orders(id),
    status      VARCHAR2(20) DEFAULT 'preparing',
    tracking_no VARCHAR2(50)
);

4단계: 1:N 관계의 변환

1:N 관계는 N쪽 테이블에 1쪽의 기본키를 FK로 추가합니다. 관계에 속성이 있으면 보통 N쪽 테이블의 컬럼으로 함께 배치합니다. 다만 관계 자체가 여러 번 발생하거나 독립적인 이력을 가져야 한다면, 1:N처럼 보이더라도 별도 관계 테이블을 두는 편이 더 명확할 수 있습니다.

참여 제약도 컬럼 제약으로 옮겨야 합니다. N쪽 개체가 반드시 1쪽과 연결되어야 한다면 FK는 NOT NULL이 됩니다.

5단계: M:N 관계의 변환

M:N 관계는 직접 FK 하나로 표현할 수 없습니다. 양쪽 개체의 기본키를 FK로 포함하는 교차 테이블(Junction Table)을 생성해 해소합니다.

M:N 관계 변환 규칙

양쪽 개체의 PK를 FK로 포함하는 새 테이블 생성
  * 관계 속성이 있으면 교차 테이블의 컬럼으로 추가
  * PK = 양쪽 FK의 복합키 또는 대리키
  * 대리키를 쓰면 양쪽 FK 조합에 UNIQUE 제약 추가

6단계: 다중값 속성의 변환

다중값 속성(Multi-valued Attribute)은 하나의 행 안에 반복 값 목록으로 넣지 않고 별도 테이블로 분리합니다.

다중값 속성 변환 규칙

다중값 속성 → 새 테이블
  * 원래 개체의 PK를 FK로 포함
  * 다중값 속성을 일반 컬럼으로 저장
  * PK = {FK, 속성값} 또는 대리키 + UNIQUE(FK, 속성값)

JSON 배열이나 콤마 문자열이 항상 금지되는 것은 아니지만, 검색·중복 방지·참조 무결성·값별 속성이 필요한 데이터라면 릴레이션으로 분리하는 것이 안전합니다.

7단계: 복합 속성과 파생 속성

복합 속성은 실제 검색·정렬·검증에 필요한 하위 속성으로 분해합니다. 예를 들어 주소를 zip_code, address1, address2로 나누면 우편번호 검색이나 필수값 검증이 쉬워집니다.

파생 속성(Derived Attribute)은 다른 속성에서 계산되는 값입니다. 원칙적으로는 저장하지 않고 계산하지만, 주문 당시 금액처럼 시점 보존이 필요하거나 성능상 저장해야 하는 값은 저장할 수 있습니다. 이때는 원본과 파생 값의 일관성을 유지하는 규칙이 필요합니다.

상속(일반화/특수화) 관계의 변환

ER 모델에서 포괄·부분 관계(ISA 관계, Generalization/Specialization)는 여러 방식으로 릴레이션에 옮길 수 있습니다. 대표 전략은 단일 테이블, 상위/하위 테이블, 구체 타입별 테이블입니다.

예시: 사용자 상속 구조

ER 모델
           사용자(User)
          /       |      \
학생(Student) 교수(Professor) 직원(Staff)

전략 2: 상위/하위 테이블 (Table per Type)

users (
  id    NUMBER PRIMARY KEY,
  name  VARCHAR2(50),
  type  VARCHAR2(20) CHECK (type IN ('student','professor','staff'))
)

students (
  user_id    NUMBER PRIMARY KEY REFERENCES users(id),
  student_no VARCHAR2(20),
  gpa        NUMBER(3,2)
)

professors (
  user_id    NUMBER PRIMARY KEY REFERENCES users(id),
  prof_no    VARCHAR2(20),
  department VARCHAR2(50)
)

장점: NULL이 적고 공통 속성을 한 곳에 둠
단점: 타입별 상세 조회 시 조인 필요
적합: 공통 속성과 타입별 속성이 모두 중요한 경우

실습 — 쇼핑몰 ERD를 SQL로 변환

앞 절에서 설계한 쇼핑몰 ERD를 Oracle SQL로 구현합니다. 예제는 개념 변환을 보여주기 위한 최소 스키마이며, 실제 운영에서는 상태 코드 테이블, 인덱스, 감사 컬럼, 삭제 정책을 업무 규칙에 맞게 보강합니다.

아래 예시는 order_items를 약한 개체처럼 order_id + line_no로 식별하고, shipping은 주문당 최대 한 행만 허용하는 선택 1:1 관계로 둡니다. reviews의 UNIQUE(user_id, product_id)는 "회원은 상품당 리뷰 하나"라는 정책을 뜻하므로, 여러 번 리뷰를 허용하거나 구매 행 기준 리뷰가 필요하면 키와 FK 설계를 바꿔야 합니다.

쇼핑몰 스키마 생성 (Oracle)

CREATE TABLE categories (
    id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      VARCHAR2(100) NOT NULL,
    parent_id NUMBER REFERENCES categories(id)
);

CREATE TABLE users (
    id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       VARCHAR2(50)  NOT NULL,
    email      VARCHAR2(100) NOT NULL UNIQUE,
    phone      VARCHAR2(20),
    zip_code   VARCHAR2(10),
    address1   VARCHAR2(200),
    address2   VARCHAR2(200),
    created_at DATE DEFAULT SYSDATE NOT NULL
);

CREATE TABLE products (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id NUMBER NOT NULL REFERENCES categories(id),
    name        VARCHAR2(200) NOT NULL,
    price       NUMBER(10, 2) NOT NULL CHECK (price >= 0),
    stock       NUMBER DEFAULT 0 NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
    id           NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id      NUMBER NOT NULL REFERENCES users(id),
    order_date   DATE DEFAULT SYSDATE NOT NULL,
    status       VARCHAR2(20) DEFAULT 'pending' NOT NULL
                 CHECK (status IN ('pending','paid','shipped','delivered','cancelled')),
    total_amount NUMBER(12, 2) DEFAULT 0 NOT NULL CHECK (total_amount >= 0)
);

CREATE TABLE order_items (
    order_id   NUMBER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    line_no    NUMBER NOT NULL,
    product_id NUMBER NOT NULL REFERENCES products(id),
    quantity   NUMBER NOT NULL CHECK (quantity > 0),
    unit_price NUMBER(10, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, line_no)
);

CREATE TABLE reviews (
    id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    NUMBER NOT NULL REFERENCES users(id),
    product_id NUMBER NOT NULL REFERENCES products(id),
    rating     NUMBER(1) CHECK (rating BETWEEN 1 AND 5),
    content    VARCHAR2(2000),
    created_at DATE DEFAULT SYSDATE NOT NULL,
    UNIQUE (user_id, product_id)
);

CREATE TABLE shipping (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    NUMBER NOT NULL UNIQUE REFERENCES orders(id),
    status      VARCHAR2(20) DEFAULT 'preparing' NOT NULL
                CHECK (status IN ('preparing','shipped','delivered','returned')),
    tracking_no VARCHAR2(50),
    carrier     VARCHAR2(50),
    shipped_at  DATE
);

쇼핑몰 스키마 (MySQL 버전)

CREATE TABLE categories (
    id        BIGINT AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id BIGINT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
) ENGINE=InnoDB;

CREATE TABLE users (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    email      VARCHAR(100) NOT NULL UNIQUE,
    phone      VARCHAR(20),
    zip_code   VARCHAR(10),
    address1   VARCHAR(200),
    address2   VARCHAR(200),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
) ENGINE=InnoDB;

CREATE TABLE products (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL,
    name        VARCHAR(200) NOT NULL,
    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock       INT DEFAULT 0 NOT NULL CHECK (stock >= 0),
    FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;

CREATE TABLE orders (
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT NOT NULL,
    order_date   DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    status       VARCHAR(20) DEFAULT 'pending' NOT NULL,
    total_amount DECIMAL(12,2) DEFAULT 0 NOT NULL CHECK (total_amount >= 0),
    CHECK (status IN ('pending','paid','shipped','delivered','cancelled')),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    line_no    INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, line_no),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE reviews (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    rating     INT CHECK (rating BETWEEN 1 AND 5),
    content    VARCHAR(2000),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UNIQUE (user_id, product_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE shipping (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id    BIGINT NOT NULL UNIQUE,
    status      VARCHAR(20) DEFAULT 'preparing' NOT NULL,
    tracking_no VARCHAR(50),
    carrier     VARCHAR(50),
    shipped_at  DATETIME,
    CHECK (status IN ('preparing','shipped','delivered','returned')),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

orders.total_amount는 order_items에서 계산할 수 있는 파생 값이지만, 주문 당시 금액 스냅샷과 조회 성능 때문에 저장하는 설계입니다. 저장한다면 애플리케이션 트랜잭션, 트리거, 배치 검증처럼 원본 행과 합계가 어긋나지 않게 하는 규칙이 필요합니다.

MySQL의 CHECK 제약은 8.0.16 이전에는 파싱만 되고 실제로는 적용되지 않았습니다. 운영 대상 버전에서 제약 적용 여부를 확인하고, AUTO_INCREMENT, IDENTITY, 날짜 기본값, 시간대 처리, FK 인덱스 생성 방식처럼 DBMS마다 다른 부분도 함께 점검합니다.

변환 과정 검증 체크리스트

ER → 릴레이션 변환 검증 12항목

□  1. 모든 강한 개체가 테이블로 변환되었는가?
□  2. 약한 개체의 PK 또는 UNIQUE에 소유 개체의 PK가 포함되었는가?
□  3. 1:1 관계의 FK에 UNIQUE 제약이 있는가?
□  4. 전체 참여 관계가 NOT NULL로 표현되었는가?
□  5. 1:N 관계의 FK가 N쪽 테이블에 배치되었는가?
□  6. M:N 관계가 교차 테이블로 해소되었는가?
□  7. 관계 속성이 적절한 테이블에 배치되었는가?
□  8. 다중값 속성이 별도 테이블로 분리되었는가?
□  9. 복합/파생 속성의 저장 전략이 명확한가?
□ 10. ON DELETE 정책이 업무 규칙에 맞게 설정되었는가?
□ 11. DBMS별 IDENTITY/AUTO_INCREMENT/CHECK/날짜 기본값 차이를 확인했는가?
□ 12. UNIQUE 제약이 업무 정책을 과하게 제한하지 않는가?

변환 규칙 종합 정리

ER 모델을 릴레이션으로 변환할 때는 구조만 옮기는 것이 아니라 데이터 무결성을 보장하는 제약까지 함께 옮겨야 합니다. PK, FK, UNIQUE, CHECK, NOT NULL, ON DELETE 정책은 ERD의 의미를 데이터베이스가 실제로 지키게 만드는 장치입니다.

다음 장에서는 이렇게 만든 테이블이 올바른 구조인지 검증하는 정규화를 다루겠습니다.