ER에서 테이블로 - 관계형 스키마 변환 규칙

이 장에서 배우는 것

앞 장에서는 온라인 서점의 요구사항을 읽고 개체와 관계를 뽑아 ER 다이어그램으로 그렸다. 이 장에서는 그 다이어그램을 실제로 데이터베이스에 만들 수 있는 CREATE TABLE 문으로 옮긴다. ER 다이어그램은 사람이 보고 토론하기 위한 그림이고, 테이블은 DBMS가 실제로 저장하고 검색하는 구조다. 이 둘을 잇는 규칙을 정확히 알아야 그림에서 합의한 내용을 코드에서 그대로 지킬 수 있다.

• 개체와 속성을 테이블과 열로 옮기는 기본 규칙을 안다
• 1:N 관계를 외래키(foreign key) 하나로 표현하는 방법을 안다
• M:N 관계를 교차 테이블(junction table)로 쪼개는 방법을 안다
• 다중값 속성과 자기 참조(self-reference) 계층을 테이블로 바꾸는 방법을 안다
• 변환 결과를 SQL 연구소의 온라인 서점 스키마와 하나씩 맞춰 본다

문제 상황

SQL 연구소 개발팀의 김주임은 앞 장에서 완성한 ER 다이어그램을 들고 바로 테이블을 만들기 시작했다. 회원, 도서, 저자, 출판사, 카테고리, 주문을 각각 테이블 하나씩으로 만드는 것까지는 어렵지 않았다. 문제는 관계였다.

첫 번째 실수는 도서와 저자 사이에서 나왔다. 김주임은 book 테이블에 author_id 열 하나를 추가했다. 단독 저서만 등록할 때는 문제가 없었지만, 공동 저자가 있는 책이나 번역서를 등록하려는 순간 막혔다. 저자를 한 명만 담을 수 있는 열로는 "저자 두 명이 함께 쓴 책", "원저자와 번역자가 각각 있는 책"을 표현할 방법이 없었다.

두 번째 실수는 카테고리에서 나왔다. 처음에는 category_name 열 하나만 두었는데, 기획팀에서 "대분류와 소분류를 나눠서 보여 달라"는 요구가 들어오자 category_lv1, category_lv2 열 두 개로 테이블을 다시 만들어야 했다. 나중에 3단계 분류가 필요해지면 또 열을 추가해야 하는 구조였다.

두 실수 모두 ER 다이어그램에 있던 관계의 종류(1:N, M:N, 계층)를 테이블로 옮기는 정해진 규칙을 몰라서 생긴 일이다. 이 규칙은 몇 가지뿐이고, 한 번 익혀 두면 어떤 도메인의 ER 다이어그램이든 같은 방식으로 옮길 수 있다.

개체는 테이블, 속성은 열이 된다

가장 단순한 규칙부터 시작한다. ER 다이어그램의 개체(entity) 하나는 테이블 하나가 되고, 그 개체가 가진 속성은 그대로 열이 된다. 개체를 식별하는 속성은 기본키(primary key)로 지정한다. 온라인 서점 스키마에서는 member, author, publisher, category, book이 모두 이렇게 만들어진 개체 테이블이다.

속성 하나가 값 하나만 가지는 한, 이 변환은 기계적이다. book 개체의 title, price, published_on, pages는 각각 book 테이블의 열이 되고, pages처럼 값이 없을 수 있는 속성은 NULL을 허용하는 열로 둔다. 관계가 끼어드는 순간부터 규칙이 갈라지는데, 이 장의 나머지 절은 그 갈림길을 다룬다.

1:N 관계는 외래키 하나로 흡수한다

1:N 관계는 "한 출판사가 여러 책을 낸다", "한 회원이 여러 주문을 한다"처럼 한쪽(1)이 다른 쪽(N) 여러 개와 연결되는 관계다. 이런 관계는 새 테이블을 만들 필요 없이, N쪽 테이블에 1쪽 테이블의 기본키를 가리키는 외래키 열 하나만 추가하면 된다.

규칙의 핵심은 "N쪽에 FK를 둔다"는 방향이다. 반대로 1쪽에 N쪽 목록을 담으려 하면 한 행에 여러 값을 넣어야 하는 문제가 다시 생긴다. 서점 스키마의 세 가지 1:N 관계를 이 규칙에 대입해 보면 다음과 같다.

서점 스키마의 1:N 관계와 FK 위치

관계	1쪽 개체	N쪽 개체	FK 위치
출판사가 책을 낸다	publisher	book	book.publisher_id
회원이 주문한다	member	orders	orders.member_id
카테고리가 책을 분류한다	category	book	book.category_id

즉 publisher와 member는 자신을 가리키는 열을 갖지 않고, 대신 N쪽인 book과 orders가 각각 publisher_id, member_id 열을 갖는다. 이 열의 값이 NULL을 허용하는지 여부는 관계의 참여 조건에 달려 있다. 책은 반드시 출판사가 있어야 하므로 book.publisher_id는 NOT NULL이지만, 주문에 쿠폰을 반드시 적용할 필요는 없으므로 orders.coupon_id는 NULL을 허용한다.

M:N 관계와 다중값 속성, 계층은 별도 테이블로

1:N과 달리 M:N 관계, 다중값 속성, 계층 관계는 기존 테이블에 열 하나를 더하는 것으로 끝나지 않는다. 셋 다 "한 행이 여러 값을 가지려 한다"는 같은 문제에서 출발하고, 해결 방법도 결국 같은 원리로 수렴한다. 값을 담을 별도의 행을 만들고, 원래 테이블은 외래키로 참조한다.

다대다 관계는 교차 테이블로 쪼갠다

도서와 저자 사이의 관계는 한 책에 저자가 여러 명일 수 있고, 한 저자가 여러 책을 쓸 수도 있는 M:N이다. M:N 관계는 어느 쪽에도 FK 하나를 추가하는 방식으로 표현할 수 없다. 대신 관계 자체를 표현하는 교차 테이블을 새로 만들고, 그 테이블에 양쪽 개체의 기본키를 각각 FK로 넣는다.

서점 스키마의 book_author가 바로 이 교차 테이블이다. book_author(book_id, author_id, role)은 book.id와 author.id를 각각 참조하는 두 FK로 구성되고, 두 FK를 묶으면 "이 책에 이 저자가 참여했다"는 사실 한 건을 나타낸다. 여기에 더해 role 열을 두면 같은 조합이라도 저자로 참여했는지 번역자로 참여했는지까지 구분할 수 있다. 이처럼 관계 자체가 가진 추가 정보(role)는 교차 테이블의 일반 열로 넣으면 된다.

다중값 속성도 별도 테이블로 분리한다

다중값 속성(multivalued attribute)은 개체 하나가 같은 종류의 값을 여러 개 가지는 경우를 말한다. "도서의 참여자 목록"이 그 예다. 만약 book 테이블에 author_name 열 하나를 두면 저자가 한 명일 때만 표현할 수 있고, 문제 상황에서 본 것처럼 공저서를 등록할 수 없다.

다중값 속성의 변환 규칙은 M:N 관계와 같다. 값을 담을 새 테이블을 만들고, 원래 개체를 FK로 참조한다. 서점 스키마에서는 도서의 참여자 목록이라는 다중값 속성과 도서-저자 M:N 관계가 사실상 같은 대상을 가리키므로, book_author 테이블 하나가 두 요구를 동시에 만족한다. 한 책에 참여자가 몇 명이든 book_author에 행을 그만큼 추가하면 되고, 테이블 구조는 바뀌지 않는다.

계층 관계는 같은 테이블을 스스로 참조한다

카테고리의 대분류-소분류 관계는 조금 다른 문제다. 같은 개체(카테고리)끼리 상위-하위 관계를 맺기 때문이다. 이런 계층을 category_lv1, category_lv2처럼 단계별 열로 나누면, 문제 상황에서 겪은 것처럼 단계가 늘어날 때마다 테이블 구조를 바꿔야 한다.

표준 해법은 자기 참조 외래키다. category 테이블에 parent_id 열을 두고, 이 열이 같은 category 테이블의 id를 가리키게 한다. 최상위 대분류는 parent_id가 NULL이고, 소분류는 parent_id에 자신이 속한 대분류의 id를 넣는다. 이 방식은 단계 수가 2단계든 5단계든 테이블 구조를 바꾸지 않고 그대로 수용한다.

완성 코드

-- bookstore_schema.sql
.headers on
.mode column

PRAGMA foreign_keys = ON;

CREATE TABLE publisher (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE category (
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    parent_id INTEGER REFERENCES category(id)
);

CREATE TABLE author (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    country TEXT
);

CREATE TABLE book (
    id           INTEGER PRIMARY KEY,
    isbn         TEXT NOT NULL UNIQUE,
    title        TEXT NOT NULL,
    publisher_id INTEGER NOT NULL REFERENCES publisher(id),
    category_id  INTEGER NOT NULL REFERENCES category(id),
    price        INTEGER NOT NULL,
    published_on TEXT NOT NULL,
    pages        INTEGER
);

CREATE TABLE book_author (
    book_id   INTEGER NOT NULL REFERENCES book(id),
    author_id INTEGER NOT NULL REFERENCES author(id),
    role      TEXT NOT NULL CHECK (role IN ('AUTHOR', 'TRANSLATOR')),
    PRIMARY KEY (book_id, author_id, role)
);

INSERT INTO publisher (id, name) VALUES (1, '한빛문고');

INSERT INTO category (id, name, parent_id) VALUES
    (1, '국내소설', NULL),
    (2, '한국소설', 1),
    (3, '외국소설', NULL),
    (4, '영미소설', 3);

INSERT INTO author (id, name, country) VALUES
    (1, '김도현', '한국'),
    (2, '박서연', '한국'),
    (3, 'Jane Smith', '미국');

INSERT INTO book (id, isbn, title, publisher_id, category_id, price, published_on, pages) VALUES
    (1, '979-11-00-00001-1', '여름의 온도', 1, 2, 15000, '2023-05-01', 320),
    (2, '979-11-00-00002-8', '이방인의 노래', 1, 4, 14000, '2024-02-10', 288);

INSERT INTO book_author (book_id, author_id, role) VALUES
    (1, 1, 'AUTHOR'),
    (2, 3, 'AUTHOR'),
    (2, 2, 'TRANSLATOR');

SELECT
    b.title AS 제목,
    pc.name AS 대분류,
    c.name  AS 소분류,
    GROUP_CONCAT(a.name || '(' || ba.role || ')', ', ') AS 참여자
FROM book b
JOIN category c ON c.id = b.category_id
LEFT JOIN category pc ON pc.id = c.parent_id
JOIN book_author ba ON ba.book_id = b.id
JOIN author a ON a.id = ba.author_id
GROUP BY b.id
ORDER BY b.id;

줄별 해설

.headers on과 .mode column은 sqlite3 명령줄 도구의 설정으로, 조회 결과를 열 제목과 함께 정렬된 표로 보여 준다. PRAGMA foreign_keys = ON;은 SQLite에서 기본으로 꺼져 있는 외래키 제약 검사를 켠다.

category 테이블의 parent_id INTEGER REFERENCES category(id)는 자기 참조 외래키다. 같은 테이블을 만드는 도중에 자신을 참조하므로, 다른 테이블을 참조할 때와 문법이 다르지 않다. NULL을 허용해 최상위 대분류를 표현할 수 있게 했다.

book 테이블의 publisher_id와 category_id는 1:N 관계에서 나온 FK다. 책은 반드시 출판사와 카테고리가 있어야 한다고 보고 둘 다 NOT NULL로 뒀다.

book_author 테이블은 M:N 관계와 다중값 속성을 함께 해결하는 교차 테이블이다. book_id와 author_id는 각각 book과 author를 가리키는 FK이고, role은 CHECK 제약으로 'AUTHOR' 또는 'TRANSLATOR'만 허용한다. 기본키를 (book_id, author_id, role) 세 열의 조합으로 잡은 이유는 줄 별 해설 다음 절인 "실무에서 자주 틀리는 것"에서 다룬다.

카테고리 INSERT 문에서 (2, '한국소설', 1)은 '한국소설'이 id=1인 '국내소설'의 하위 분류임을 나타내고, (4, '영미소설', 3)은 같은 방식으로 '외국소설' 아래에 '영미소설'을 둔다.

마지막 SELECT 문은 category 테이블을 c(책이 속한 소분류)와 pc(그 소분류의 상위 대분류)로 두 번 조인하는 자기 조인이다. 같은 테이블을 서로 다른 별칭으로 두 번 등장시켜 계층의 위아래를 한 행에서 함께 읽는다. book_author와 author를 추가로 조인해 GROUP_CONCAT으로 책 한 권에 딸린 참여자를 한 줄에 모은다.

실행 결과

아래는 위 스크립트를 그대로 실행했을 때 나오는 예시 결과다.

$ sqlite3 bookstore.db < bookstore_schema.sql
제목            대분류    소분류    참여자
--------------  --------  --------  --------------------------------------
여름의 온도      국내소설  한국소설  김도현(AUTHOR)
이방인의 노래    외국소설  영미소설  Jane Smith(AUTHOR), 박서연(TRANSLATOR)

첫 행은 book_author에 저자 한 명만 연결된 경우이고, 둘째 행은 원저자와 번역자가 함께 연결된 경우다. 같은 book_author 테이블 구조로 두 경우를 모두 표현했다는 점이 이 장에서 확인해야 할 핵심이다.

실무에서 자주 틀리는 것

M:N 관계를 FK 두 개로 욱여넣기

공저를 두 명까지만 허용한다고 가정하고 book 테이블에 FK를 늘리는 방식은 세 번째 저자가 나오는 순간 무너진다.

-- 틀린 설계
CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id_1 INTEGER REFERENCES author(id),
    author_id_2 INTEGER REFERENCES author(id)
);

-- 고친 설계
CREATE TABLE book_author (
    book_id   INTEGER NOT NULL REFERENCES book(id),
    author_id INTEGER NOT NULL REFERENCES author(id),
    role      TEXT NOT NULL CHECK (role IN ('AUTHOR', 'TRANSLATOR')),
    PRIMARY KEY (book_id, author_id, role)
);

다중값을 콤마로 이어 붙인 문자열에 저장하기

참여자 이름을 한 열에 콤마로 이어 붙이면 특정 저자가 쓴 책을 찾는 조회가 문자열 검색이 되어 버리고, 인덱스도 제대로 쓸 수 없다.

-- 틀린 설계
CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    authors TEXT  -- '김도현,박서연' 같은 값
);

-- 고친 설계: book_author에 행을 나눠 저장
INSERT INTO book_author (book_id, author_id, role) VALUES
    (1, 1, 'AUTHOR'),
    (1, 2, 'AUTHOR');

계층을 단계별 열로 나열하기

대분류와 소분류를 각각 열로 두면 3단계 분류가 필요해질 때 테이블 구조 자체를 바꿔야 한다.

-- 틀린 설계
CREATE TABLE category (
    id INTEGER PRIMARY KEY,
    lv1_name TEXT,
    lv2_name TEXT
);

-- 고친 설계: 단계 수와 무관하게 동작
CREATE TABLE category (
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    parent_id INTEGER REFERENCES category(id)
);

교차 테이블에 기본키를 지정하지 않기

book_author에 기본키가 없으면 같은 (book_id, author_id, role) 조합이 실수로 여러 번 들어가도 DB가 막지 못한다.

-- 틀린 설계: 같은 조합이 중복 삽입돼도 통과한다
CREATE TABLE book_author (
    book_id   INTEGER NOT NULL REFERENCES book(id),
    author_id INTEGER NOT NULL REFERENCES author(id),
    role      TEXT NOT NULL
);

-- 고친 설계: 복합 기본키로 중복을 막는다
CREATE TABLE book_author (
    book_id   INTEGER NOT NULL REFERENCES book(id),
    author_id INTEGER NOT NULL REFERENCES author(id),
    role      TEXT NOT NULL CHECK (role IN ('AUTHOR', 'TRANSLATOR')),
    PRIMARY KEY (book_id, author_id, role)
);

한눈에 보기

ER 요소를 테이블로 옮기는 규칙

ER 요소	변환 규칙	서점 스키마 예
개체	테이블 하나, 속성은 열	book, author, category
1:N 관계	N쪽 테이블에 FK 열 추가	orders.member_id
M:N 관계	교차 테이블을 만들고 두 FK를 묶어 PK로	book_author(book_id, author_id)
다중값 속성	별도 테이블로 분리, 원 개체를 FK로 참조	book_author.role
계층(재귀) 관계	같은 테이블을 스스로 참조하는 FK	category.parent_id → category.id

연습 문제

1. member와 orders 사이의 1:N 관계를 테이블로 옮길 때 FK를 어느 테이블의 어느 열에 두어야 하는지 쓰고, 반대로 두면 어떤 문제가 생기는지 설명하라.
2. book_author 테이블의 기본키를 (book_id, author_id) 두 열이 아니라 (book_id, author_id, role) 세 열로 잡은 이유를 서점 데이터의 예를 들어 설명하라.
3. SQL 연구소의 category 데이터에서 이름이 '한국소설'인 소분류가 속한 대분류 이름을 구하는 SELECT 문을 자기 조인으로 작성하라.
4. 카테고리를 category_lv1, category_lv2 열로 설계한 팀이 있다고 하자. 이 설계의 문제점을 두 가지 이상 들고, parent_id 방식으로 바꿀 때 필요한 변경을 설명하라.

정답과 해설

1번 FK는 N쪽인 orders 테이블의 member_id 열에 둔다. 한 회원이 여러 주문을 할 수 있으므로, 반대로 member 테이블에 주문 목록을 담으려 하면 한 행에 여러 개의 주문 번호를 넣어야 하는 다중값 문제가 생긴다. N쪽 한 행은 1쪽을 하나만 가리키므로 열 하나로 충분하다.

2번 서점 데이터에서 '이방인의 노래'는 Jane Smith가 AUTHOR로, 박서연이 TRANSLATOR로 참여한다. 만약 기본키가 (book_id, author_id)뿐이라면, 같은 저자가 한 책에 저자이면서 동시에 번역자로도 참여하는 경우(예: 저자가 자신의 책을 직접 번역한 경우) (book_id, author_id) 조합이 중복돼 두 번째 행을 넣을 수 없다. role까지 기본키에 포함하면 이런 경우도 구분해서 저장할 수 있다.

3번

SELECT pc.name AS 대분류, c.name AS 소분류
FROM category c
JOIN category pc ON pc.id = c.parent_id
WHERE c.name = '한국소설';

같은 category 테이블을 c(소분류)와 pc(대분류) 두 별칭으로 조인해, c.parent_id = pc.id 조건으로 상위 분류를 찾는다.

4번 문제점은 두 가지다. 첫째, 3단계 이상으로 분류가 늘어나면 category_lv3 열을 또 추가해야 하고 이미 있는 데이터를 옮겨야 한다. 둘째, 대분류 이름이 여러 소분류 행에 반복 저장돼 오타나 수정 누락이 생기기 쉽다. parent_id 방식으로 바꾸려면 category 테이블에 parent_id 열을 추가하고, 기존 lv1_name 값을 가진 대분류 행을 parent_id IS NULL인 행으로, lv2_name 값을 가진 소분류 행을 해당 대분류를 가리키는 parent_id 값을 가진 행으로 변환해야 한다.