정규화 - 이상 현상을 없애는 설계

이 장에서 배우는 것

앞 장에서는 ER 다이어그램의 개체와 관계를 테이블로 바꾸는 규칙을 다뤘다. 그 규칙을 그대로 따라도 설계자가 편의상 정보를 한 테이블에 몰아넣으면 같은 값이 여러 행에 반복되고, 그 반복이 데이터를 고치거나 지울 때 예상치 못한 부작용을 낳는다. 정규화는 이런 반복과 부작용을 원리에 따라 없애는 절차다. 이 장에서는 SQL 연구소의 온라인 서점 데이터를 예로 삼아, 이상 현상이 왜 생기는지부터 그 이상 현상을 없애는 단계별 분해 방법, 그리고 정규화를 알면서도 의도적으로 되돌리는 반정규화까지 다룬다.

• 삽입·갱신·삭제 이상이 무엇이고 왜 생기는지 설명할 수 있다
• 함수 종속을 보고 결정자와 종속자를 구분할 수 있다
• 1NF부터 3NF까지 표를 단계별로 나눌 수 있다
• BCNF가 3NF와 다른 지점을 설명할 수 있다
• 반정규화를 언제, 왜 쓰는지 판단할 수 있다

문제 상황

SQL 연구소가 서비스 초기에 주문 내역을 order_flat이라는 표 하나에 담았다고 하자. 주문번호, 회원 이메일·이름·등급, 책 ISBN·제목·출판사명, 수량, 단가를 한 행에 다 적었다. 엑셀에 익숙한 담당자에게는 자연스러운 구성이었지만 곧 세 가지 문제가 나타났다. 첫째, 회원 이수진이 개명 신고를 했는데 그 회원이 과거에 주문한 행이 여러 개라 일부 행만 이름을 고치고 나머지는 그대로 남았다. 둘째, 아직 한 권도 팔리지 않은 신간은 주문 행 자체가 없으니 그 책의 출판사 정보를 어디에도 넣을 수 없었다. 셋째, 어떤 회원의 유일한 주문을 취소 처리 과정에서 행째로 지웠더니 그 회원의 이메일과 등급 정보까지 함께 사라졌다. 세 문제 모두 같은 원인에서 나온다. 서로 성격이 다른 정보(회원, 도서, 출판사, 주문 수량)를 한 행 단위로 묶어 둔 탓에, 한 종류의 정보를 고치거나 지우는 작업이 다른 종류의 정보에까지 번진 것이다.

함수 종속과 이상 현상

함수 종속(functional dependency)은 "A 값이 같으면 B 값도 항상 같다"는 관계를 말하며 A→B로 적는다. order_flat의 기본키를 (order_id, book_isbn)으로 두면 다음 종속이 보인다.

• (order_id, book_isbn) → qty, unit_price : 주문번호와 책이 정해지면 수량과 단가가 정해진다. 키 전체에 종속되므로 문제가 없다.
• order_id → member_email, 그리고 member_email → member_name, member_grade : 회원 정보는 book_isbn과 무관하게 order_id 하나만으로 정해지는데, order_id는 기본키의 일부일 뿐이다.
• book_isbn → book_title, publisher_name : 책 정보 역시 order_id와 무관하게 book_isbn 하나만으로 정해진다.

키 전체가 아니라 키의 일부에만 종속되는 뒤의 두 경우가 앞서 본 세 가지 이상 현상의 근본 원인이다.

이상 현상의 종류와 order_flat에서의 예

이상 현상	뜻	order_flat 예
갱신 이상	같은 값이 여러 행에 있어 일부만 고치면 모순이 생긴다	회원 이름을 한 행만 고치고 나머지 주문 행은 옛 이름 그대로 남는다
삽입 이상	다른 정보가 없으면 넣고 싶은 정보도 못 넣는다	주문이 하나도 없는 신간의 출판사 정보를 저장할 행이 없다
삭제 이상	한 정보를 지우려다 다른 정보까지 같이 사라진다	회원의 마지막 주문 행을 지우면 그 회원 정보까지 없어진다

정규형 단계별 분해

1NF — 칸 하나에는 값 하나만

제1정규형(1NF)은 모든 칸이 더 쪼갤 수 없는 원자값 하나만 담아야 한다는 조건이다. 만약 order_flat에 author_names라는 칸을 두고 "김지은,박서준"처럼 콤마로 여러 저자를 나열했다면 1NF 위반이다. 이 경우 한 칸에서 특정 저자 한 명만 골라내는 일이 SQL로 번거로워진다. 실제 SQL 연구소 스키마는 처음부터 book_author(book_id, author_id, role) 테이블을 따로 두어 책 한 권에 저자가 여러 명이어도, 역할이 AUTHOR든 TRANSLATOR든 행을 하나씩 추가하는 방식으로 이 문제를 피해 간다.

2NF — 키 전체에 종속되지 않는 칸 분리

제2정규형(2NF)은 1NF를 만족하면서, 복합키의 일부에만 종속되는 칸을 없앤 상태다. order_flat에서 member_email·member_name·member_grade는 order_id에만 종속되고 book_isbn과는 무관하므로 부분 함수 종속이다. book_title·publisher_name도 book_isbn에만 종속되므로 마찬가지다. 이 둘을 각각 회원 관련 정보와 도서 관련 정보로 떼어 내면 남는 (order_id, book_isbn, qty, unit_price)만 키 전체에 완전히 종속된다.

3NF — 키가 아닌 칸끼리의 종속 제거

제3정규형(3NF)은 2NF를 만족하면서, 키가 아닌 칸이 또 다른 키가 아닌 칸에 종속되는 이행 함수 종속까지 없앤 상태다. 앞서 떼어 낸 회원 정보 표에서 member_email이 member_name과 member_grade를 결정하는 것은 문제가 없지만, 출판사 정보를 book 표 안에 publisher_name 문자열로 그대로 두면 book_isbn → publisher_name → (출판사 주소 같은 다른 속성)처럼 출판사 자체의 속성이 책을 거쳐 이행적으로 종속되는 구조가 남는다. 그래서 SQL 연구소 스키마는 출판사를 publisher(id, name)로 따로 떼고, book은 publisher_name 대신 publisher_id만 참조한다. 이렇게 하면 출판사 이름이 바뀔 때 publisher 테이블 한 행만 고치면 된다.

BCNF — 모든 결정자는 후보키여야 한다

보이스-코드 정규형(BCNF)은 3NF보다 조건이 하나 더 엄격하다. 3NF는 "키가 아닌 칸끼리 종속되면 안 된다"고 말하지만, 결정자가 후보키의 일부인 예외를 허용한다. BCNF는 이 예외마저 없애 모든 함수 종속의 결정자가 후보키여야 한다고 요구한다. 예를 들어 SQL 연구소가 카테고리별 추천 담당 큐레이터를 두고 (member_id, category_id, curator) 형태로 기록한다고 하자. 회원이 같은 카테고리를 중복 추천받지 않는다면 (member_id, category_id)가 후보키이고, 한 회원이 같은 큐레이터에게 중복 추천받지 않는다면 (member_id, curator)도 후보키다. 그런데 큐레이터 한 명이 담당 카테고리를 하나로 고정해 curator → category_id가 성립한다면, curator는 두 후보키 중 어느 쪽의 전체도 아니면서 category_id를 결정하므로 BCNF를 위반한다. 이런 사례는 실무에서는 드물고, 대부분의 트랜잭션 처리 테이블은 3NF까지만 정리해도 이상 현상이 사라진다.

반정규화는 언제 쓰나

정규화를 마치면 주문 총액은 order_item의 qty와 unit_price를 곱해 더하면 언제든 구할 수 있으므로 orders 테이블에 따로 저장할 이유가 없다. 그런데 주문 목록 화면을 열 때마다 이 합계를 매번 계산하면, 주문이 많은 회원일수록 화면이 느려진다. 이럴 때 orders에 total_amount 칸을 미리 만들어 두고 주문이 확정될 때 값을 채워 넣는 방법을 반정규화(denormalization)라고 한다. 반정규화는 정규화를 몰라서 처음부터 안 나누는 것과 다르다. 정규화된 설계를 알면서, 조회 성능을 위해 중복을 의도적으로 되돌리는 것이다. 대신 order_item이 바뀔 때 total_amount도 같이 바뀌도록 트리거 같은 장치로 직접 관리해야 하고, 그 관리를 빼먹으면 orders.total_amount와 order_item 합계가 어긋나는 새로운 이상 현상이 생긴다. 즉 반정규화는 이상 현상을 없애는 대가로 얻은 일관성을, 성능을 위해 다시 위험에 노출시키는 거래다.

완성 코드

-- (1) 비정규화 상태를 눈으로 보기 위한 예시 테이블
CREATE TABLE order_flat (
    order_id       INTEGER NOT NULL,
    book_isbn      TEXT    NOT NULL,
    book_title     TEXT    NOT NULL,
    publisher_name TEXT    NOT NULL,
    member_email   TEXT    NOT NULL,
    member_name    TEXT    NOT NULL,
    member_grade   TEXT    NOT NULL,
    unit_price     INTEGER NOT NULL,
    qty            INTEGER NOT NULL,
    PRIMARY KEY (order_id, book_isbn)
);

INSERT INTO order_flat VALUES
    (1001, '979-11-0000-001-1', 'SQL 첫걸음',        '한빛서재', 'sujin@example.com', '이수진', 'GOLD',   21000, 1),
    (1001, '979-11-0000-002-8', '데이터 모델링 실무', '한빛서재', 'sujin@example.com', '이수진', 'GOLD',   26000, 1),
    (1002, '979-11-0000-001-1', 'SQL 첫걸음',        '한빛서재', 'minho@example.com', '박민호', 'SILVER', 21000, 2);

-- (2) 회원 이름을 한 행만 고쳐서 갱신 이상을 재현한다
UPDATE order_flat
   SET member_name = '이수진(개명)'
 WHERE order_id = 1001 AND book_isbn = '979-11-0000-001-1';

SELECT order_id, book_isbn, member_name
  FROM order_flat
 WHERE member_email = 'sujin@example.com';

-- (3) SQL 연구소가 이미 나눠 둔 테이블 목록을 확인한다
SELECT name
  FROM sqlite_master
 WHERE type = 'table'
   AND name IN ('member', 'orders', 'order_item', 'book', 'publisher');

-- (4) 반정규화: 주문 총액을 orders에 미리 저장해 둔다
ALTER TABLE orders ADD COLUMN total_amount INTEGER;

CREATE TRIGGER trg_order_item_after_insert
AFTER INSERT ON order_item
BEGIN
    UPDATE orders
       SET total_amount = (
           SELECT SUM(qty * unit_price)
             FROM order_item
            WHERE order_id = NEW.order_id
       )
     WHERE id = NEW.order_id;
END;

줄별 해설

(1)의 CREATE TABLE은 order_flat을 (order_id, book_isbn) 복합키로 만든다. 회원 정보 세 칸과 도서·출판사 정보 두 칸이 키의 일부에만 종속되도록 일부러 구성해, 앞서 표에서 설명한 부분 함수 종속을 재현한다. INSERT 세 줄 중 두 줄(주문 1001)이 같은 회원 정보를 반복해서 담고, 두 줄(book_isbn 979-11-0000-001-1)이 같은 책 정보를 반복해서 담는다.

(2)의 UPDATE는 주문 1001의 첫 번째 책 행만 골라 member_name을 고친다. 실무에서 회원 정보 수정 화면이 "이 주문 행"만 갱신하도록 짜여 있으면 흔히 벌어지는 실수다. 뒤이은 SELECT는 sujin@example.com의 모든 행을 다시 조회해, 한 행은 "이수진(개명)"이고 다른 행은 여전히 "이수진"인 모순을 보여준다.

(3)의 SELECT는 SQL 연구소 플랫폼에 이미 올라가 있는 member, orders, order_item, book, publisher 다섯 테이블의 존재를 확인한다. order_flat을 정규화 규칙대로 끝까지 나누면 바로 이 다섯 테이블 구조가 나온다는 점을 확인하는 절차다.

(4)의 ALTER TABLE은 orders에 total_amount 칸을 반정규화 목적으로 추가한다. 뒤이은 CREATE TRIGGER는 order_item에 새 행이 들어올 때마다(AFTER INSERT) 그 주문의 order_id로 order_item 전체 합을 다시 구해 orders.total_amount에 써 넣는다. NEW.order_id는 방금 들어온 order_item 행의 order_id 값을 가리킨다.

실행 결과

$ sqlite3 bookstore.db < normalize.sql

(2)의 SELECT 결과는 order_flat이 우리가 직접 넣고 고친 데이터이므로 항상 다음과 같다.

order_id  book_isbn            member_name
1001      979-11-0000-001-1    이수진(개명)
1001      979-11-0000-002-8    이수진

같은 회원인데 이름이 서로 다른 두 값으로 갈라져 있다. (3)의 SELECT 결과는 SQL 연구소가 미리 채워 둔 스키마를 보여주므로 아래는 예시 결과이며, 행이 나오는 순서는 실행마다 달라질 수 있다.

name
member
orders
order_item
book
publisher

(4)의 ALTER TABLE과 CREATE TRIGGER는 성공하면 별도 출력 없이 다음 문장으로 넘어간다.

실무에서 자주 틀리는 것

부분 함수 종속을 UPDATE로 땜질한다

키의 일부에만 종속되는 칸을 그대로 두고, 값이 바뀔 때마다 관련된 모든 행을 찾아 UPDATE하는 방식은 언젠가 한 행을 빠뜨린다.

-- 틀린 방식: 출판사명이 바뀔 때마다 관련 행을 일일이 찾아 고친다
UPDATE order_flat SET publisher_name = '한빛서재(새이름)'
 WHERE book_isbn = '979-11-0000-001-1';

-- 고친 방식: 출판사를 별도 테이블로 분리해 한 행만 고친다
UPDATE publisher SET name = '한빛서재(새이름)' WHERE id = 1;

NULL 허용 칸을 무조건 별도 테이블로 뺀다

book.pages는 아직 쪽수가 확정되지 않은 예약 도서 때문에 NULL을 허용한다. 이를 정규화 위반으로 오해해 굳이 별도 테이블로 떼면 조인만 늘어난다. NULL 허용은 함수 종속과는 다른 문제다.

-- 틀린 방식: NULL 허용을 이유로 불필요하게 분리한다
CREATE TABLE book_detail (book_id INTEGER PRIMARY KEY, pages INTEGER);

-- 고친 방식: pages는 book 테이블에 NULL 허용 칸으로 그대로 둔다
CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    isbn TEXT NOT NULL,
    title TEXT NOT NULL,
    publisher_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    price INTEGER NOT NULL,
    published_on TEXT NOT NULL,
    pages INTEGER
);

반정규화한 칸을 트리거 없이 애플리케이션에서만 관리한다

total_amount 같은 반정규화 칸을 애플리케이션 코드에서만 갱신하면, 배치 작업이나 관리자 화면처럼 그 코드를 거치지 않는 경로에서 값이 낡은 채로 남는다.

-- 틀린 방식: order_item만 넣고 orders.total_amount는 그대로 둔다
INSERT INTO order_item (order_id, line_no, book_id, qty, unit_price)
VALUES (2001, 1, 3, 2, 21000);

-- 고친 방식: 위에서 만든 트리거가 INSERT 직후 total_amount를 자동으로 맞춘다
-- (trg_order_item_after_insert가 이미 걸려 있으면 별도 코드가 필요 없다)

반정규화와 미정규화를 혼동한다

처음부터 테이블을 나누지 않고 문자열 칸에 다른 테이블의 이름을 박아 두는 것은 반정규화가 아니라 정규화를 아예 하지 않은 것이다.

-- 틀린 방식: 설계 단계부터 publisher 테이블 없이 이름만 저장한다
CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT, publisher_name TEXT);

-- 고친 방식: 먼저 정규화된 구조로 설계하고, 필요할 때만 반정규화 칸을 추가한다
CREATE TABLE publisher (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT, publisher_id INTEGER NOT NULL);

한눈에 보기

정규형 단계가 없애는 문제

단계	조건	없애는 문제	예
1NF	모든 칸이 원자값	한 칸에 여러 값이 섞이는 것	author_names를 book_author로 분리
2NF	1NF + 부분 함수 종속 제거	복합키 일부에만 종속된 칸의 반복	member, book 정보를 order_item에서 분리
3NF	2NF + 이행 함수 종속 제거	키가 아닌 칸끼리의 종속	publisher_name을 publisher_id 참조로 변경
BCNF	모든 결정자가 후보키	3NF가 허용하는 예외적 종속	curator → category_id 같은 드문 사례

연습 문제

1. book 테이블이 publisher_id 대신 publisher_name을 문자열로 직접 저장했다면 어떤 이상 현상이 생길지 서술하고, 같은 출판사명이 여러 행에 반복되는지 확인할 SELECT 문을 SQL 연구소에서 작성해 실행해 보라.
2. order_item 테이블만 보고 order_id → book_id가 함수 종속인지 판단하고, 그 판단의 근거가 되는 데이터를 확인하는 SELECT 문을 작성해 보라.
3. orders에 total_amount를 반정규화로 추가했다고 할 때, order_item의 실제 합계와 total_amount 값이 어긋나는 행을 찾으려면 어떤 조건으로 비교해야 하는지 서술하라.
4. member.region이 NULL을 허용하는 것이 정규화 위반이 아닌 이유를 함수 종속의 정의를 들어 설명하라.

정답과 해설

1. publisher_name을 문자열로 직접 저장하면 같은 출판사의 책이 여러 권일 때 출판사명이 여러 행에 반복된다. 출판사명이 바뀌면 그 출판사의 책 수만큼 행을 고쳐야 하고, 한 행이라도 빠뜨리면 같은 출판사인데 이름이 서로 다른 상태가 된다. 반복 여부는 다음처럼 확인한다.

SELECT publisher_id FROM book WHERE publisher_id = 1;

같은 publisher_id를 가진 행이 두 개 이상이면, 문자열로 저장했을 경우 그 행 수만큼 이름이 중복 저장됐을 것이라는 뜻이다.

2. order_id → book_id는 함수 종속이 아니다. 주문 하나에 책이 여러 권 담길 수 있으므로 같은 order_id에 서로 다른 book_id가 여러 행 나올 수 있다. 다음 SELECT로 특정 주문의 book_id가 하나 이상인지 확인할 수 있다.

SELECT order_id, book_id FROM order_item WHERE order_id = 2001;

이 조회에서 order_id가 같은 행이 두 개 이상이고 book_id 값이 서로 다르면, order_id 하나만으로는 book_id가 정해지지 않는다는 뜻이므로 함수 종속이 아니라고 판단한다.

3. order_id별로 order_item의 qty × unit_price를 모두 더한 값과, 그 order_id에 해당하는 orders.total_amount를 나란히 놓고 비교해 두 값이 다른 order_id를 찾으면 된다. 여러 order_item 행의 값을 하나로 모아 비교하는 방법은 집계와 조인을 다루는 장에서 실제 SQL 문으로 완성한다. 지금 단계에서는 "합이 서로 다른 order_id가 있는가"라는 조건 자체를 정확히 세울 수 있으면 충분하다.

4. 함수 종속은 "어떤 칸의 값이 같으면 다른 칸의 값도 항상 같다"는 관계다. member.id가 같으면 region 값이 항상 같은 값(또는 항상 NULL)이라는 사실 자체는 종속 관계와 무관하다. NULL은 "아직 모른다" 또는 "해당 없음"을 나타내는 값이지 다른 칸에 대한 종속 여부를 바꾸지 않는다. 그러므로 region이 NULL을 허용하는 것은 정규화 규칙과는 별개의 설계 선택이며 위반이 아니다.