정규화와 함수 종속

정규형의 이름을 순서대로 외워도, 실제 테이블에서 무엇을 떼어 내야 하는지는 판단하기 어렵습니다. 그래서 이 글은 먼저 후보 키와 함수 종속을 적고, 한 사실을 고치거나 넣거나 지울 때 다른 사실이 흔들리는 반례를 SQL로 찾는 순서로 진행합니다.

여기서 말하는 관계 정규화(normalization)는 이메일의 대소문자나 공백을 맞추는 문자열 정규화와 다른 개념입니다. 업무의 사실 하나를 한곳에만 저장하도록 테이블을 분해해, 갱신 이상(update anomaly)과 삽입 이상(insertion anomaly), 삭제 이상(deletion anomaly)을 줄이는 설계 과정입니다. 어떤 열의 값이 다른 열의 값을 하나로 정하는 규칙을 함수 종속(functional dependency)이라 하고, post_id → post_title처럼 화살표로 씁니다. 화살표의 왼쪽에 있는 열을 결정자(determinant)라고 부릅니다.

평면 테이블의 갱신 이상

예제는 다음 업무 규칙을 전제로 합니다. 게시글 ID는 제목과 작성자, 카테고리를 정하고, 태그 ID는 태그의 현재 이름을 정하며, 카테고리 ID는 카테고리 이름과 그룹을 정합니다. 서로 다른 그룹에 같은 이름의 카테고리가 있을 수 있으므로 category_name → group_name은 가정하지 않습니다.

이 사실들을 게시글과 태그의 연결 한 행에 모두 담으면, 같은 게시글의 제목은 태그 수만큼, 같은 태그의 이름은 게시글 수만큼 반복됩니다. 아래 코드는 연결 한 행의 태그 이름만 바꾸어, 그 반복이 무엇을 남기는지 봅니다.

DROP TABLE IF EXISTS post_tags_flat_bad;

CREATE TABLE post_tags_flat_bad (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  post_title VARCHAR(120) NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  category_name VARCHAR(100) NOT NULL,
  group_name VARCHAR(100) NOT NULL,
  tag_name VARCHAR(80) NOT NULL,
  PRIMARY KEY (post_id, tag_id)
);
INSERT INTO post_tags_flat_bad VALUES
  (1, 10, '인덱스', 1, 7, '데이터베이스', '백엔드', '성능'),
  (1, 11, '인덱스', 1, 7, '데이터베이스', '백엔드', 'MySQL'),
  (2, 10, '트랜잭션', 1, 7, '데이터베이스', '백엔드', '성능');

UPDATE post_tags_flat_bad
SET tag_name = '퍼포먼스'
WHERE post_id = 1 AND tag_id = 10;

SELECT tag_id,
       GROUP_CONCAT(DISTINCT tag_name ORDER BY tag_name) AS names
FROM post_tags_flat_bad
GROUP BY tag_id
ORDER BY tag_id;

tag_id | names
10     | 성능,퍼포먼스
11     | MySQL

결정자인 tag_id 10 하나에 현재 이름이 두 개 생겼습니다. 같은 사실을 여러 행에 복사해 두었기 때문에, 한 행만 고친 UPDATE가 모순을 남긴 것이며, 이것이 갱신 이상입니다. GROUP_CONCAT 안의 ORDER BY는 이름을 정해진 순서로 보여 주려고 붙였습니다.

삽입과 삭제에도 같은 문제가 있습니다. 아직 어떤 게시글에도 붙지 않은 태그는 기본 키의 post_id와 NOT NULL인 제목을 채울 수 없으므로 저장할 곳이 없는데, 이것이 삽입 이상입니다. 태그 11은 게시글 1에만 붙어 있어서, 그 연결 한 행을 지우면 'MySQL'이라는 태그 이름도 함께 사라지는데, 이것이 삭제 이상입니다.

1NF와 2NF, 3NF

제1정규형(1NF)은 셀에 업무상 원자 값을 두고, 반복되는 그룹을 행으로 나타내는 출발점입니다. 태그 목록을 '성능,MySQL' 같은 CSV 한 칸에 담았다면 정규화의 다른 단계보다 1NF부터 어긴 것입니다. 위의 평면 테이블은 셀이 원자적이므로 1NF는 지킵니다.

이 테이블의 후보 키(candidate key), 곧 행을 최소로 식별하는 열 집합은 (post_id, tag_id)입니다. 그런데 post_title은 post_id 하나만으로, tag_name은 tag_id 하나만으로 정해집니다. 후보 키의 일부에만 종속되는 이런 관계를 부분 종속(partial dependency)이라 하며, 제2정규형(2NF)이 없애려는 대상입니다.

게시글을 떼어 낸 뒤에도 카테고리 이름과 그룹 이름을 게시글에 복사하면, post_id → category_id → category_name처럼 키가 아닌 열을 거쳐 정해지는 이행 종속(transitive dependency)이 남습니다. 제3정규형(3NF)은 이런 열을, 그 결정자가 키가 되는 관계로 옮기게 합니다. 분해한 DDL은 category_id → group_id → group_name의 순서로 그룹을 참조합니다.

종속의 위반은 다음 순서로 찾습니다.

결정자로 판정하는 3NF와 BCNF

정규형을 정확히 판정하려면 "키가 아닌 열 사이의 이행 종속을 없앤다"는 요약보다 정의가 필요합니다. 어떤 후보 키에도 속하지 않는 속성을 비주요 속성(non-prime attribute), 후보 키에 속하는 속성을 주요 속성(prime attribute)이라 합니다. 2NF는 1NF이면서, 비주요 속성이 어떤 후보 키의 진부분집합에도 종속되지 않는 조건입니다.

(post_id, tag_id) → post_id처럼 오른쪽이 왼쪽에 이미 들어 있어 어떤 데이터에서도 저절로 성립하는 종속을 자명한(trivial) 종속이라 하고, 그렇지 않은 종속을 비자명한 종속이라 합니다. 3NF는 비자명한 모든 종속 X → A에 대해, X가 슈퍼 키(superkey)이거나 A가 주요 속성이어야 한다는 조건입니다. BCNF(Boyce-Codd normal form)는 여기서 두 번째 조건을 없애, 비자명한 모든 함수 종속의 결정자가 슈퍼 키여야 한다는 더 강한 기준입니다. 두 기준의 차이는 후보 키가 여러 개이고 서로 겹칠 때 드러납니다.

수강 관계 (학생, 과목, 교사)에서 학생은 한 과목에 교사 한 명만 배정받고, 교사는 한 과목만 담당한다고 가정합니다. 종속은 (학생, 과목) → 교사와 교사 → 과목입니다. (학생, 과목)은 교사를 정하고 (학생, 교사)는 교사의 과목까지 알아내므로 둘 다 후보 키이며, 교사만으로는 학생을 알 수 없으므로 교사는 슈퍼 키가 아닙니다.

교사 → 과목의 결정자는 슈퍼 키가 아니지만, 오른쪽의 과목이 후보 키 (학생, 과목)에 속하는 주요 속성이므로 이 관계는 3NF를 만족하고 BCNF는 어깁니다. 교사의 과목이 수강 행마다 반복되는 것을 줄이려 (학생, 교사)와 (교사, 과목)으로 나누면, 공통 열인 교사가 뒤 관계의 키이므로 무손실 분해(lossless decomposition)입니다. 그러나 (학생, 과목) → 교사를 검사할 수 있는 관계가 사라져 종속 보존(dependency preservation)은 잃습니다(무손실·종속 보존과 3NF/BCNF의 기준).

종속을 잃는다는 것이 무엇인지는, 각 테이블의 키를 모두 지키는 데이터로 확인할 수 있습니다. 첫 관계에 (민, 김)과 (민, 이), 둘째 관계에 (김, DB)와 (이, DB)가 있으면 두 관계 모두 자기 키를 지킵니다. 그러나 교사로 JOIN한 결과는 다음과 같습니다.

민의 DB 과목에 교사가 둘이 되어 (학생, 과목) → 교사가 깨졌습니다. 무손실은 규칙을 지킨 관계를 나누었다가 되돌릴 수 있는 성질이고, 종속 보존은 나눈 뒤에도 그 규칙을 각 관계의 제약으로 검사할 수 있는 성질이므로 따로 판단합니다. 이 규칙이 중요하다면 3NF에 머물고, BCNF 위반으로 생기는 반복을 별도의 검사로 다루는 선택도 할 수 있습니다.

엔티티와 연결 관계의 분해

평면 테이블을 회원과 카테고리 그룹, 카테고리, 게시글, 태그, 게시글과 태그의 연결로 나눕니다. 연결 테이블 norm_post_tags에는 두 외래 키와 관계의 속성 source만 둡니다. 앞에서 충돌한 태그 10의 두 이름은, 이 예제에서 원래 이름 '성능' 하나를 골라 옮긴 뒤 고칩니다. 그러므로 서로 모순된 두 현재 이름을 모두 보존하는 변환은 아닙니다.

DROP TABLE IF EXISTS norm_post_tags;
DROP TABLE IF EXISTS norm_posts;
DROP TABLE IF EXISTS norm_tags;
DROP TABLE IF EXISTS norm_categories;
DROP TABLE IF EXISTS norm_category_groups;
DROP TABLE IF EXISTS norm_members;

CREATE TABLE norm_members (
  id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(80) NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE norm_category_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id), UNIQUE (name)
);
CREATE TABLE norm_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  group_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id), UNIQUE (group_id, name),
  FOREIGN KEY (group_id) REFERENCES norm_category_groups (id)
);
CREATE TABLE norm_tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  PRIMARY KEY (id), UNIQUE (name)
);
CREATE TABLE norm_posts (
  id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(120) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (author_id) REFERENCES norm_members (id),
  FOREIGN KEY (category_id) REFERENCES norm_categories (id)
);
CREATE TABLE norm_post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(16) NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES norm_posts (id),
  FOREIGN KEY (tag_id) REFERENCES norm_tags (id)
);

INSERT INTO norm_members (id, name)
VALUES (1, '안동민');
INSERT INTO norm_category_groups (id, name)
VALUES (1, '백엔드');
INSERT INTO norm_categories (id, group_id, name)
VALUES (7, 1, '데이터베이스');
INSERT INTO norm_tags (id, name)
VALUES (10, '성능'), (11, 'MySQL');
INSERT INTO norm_posts (id, author_id, category_id, title)
VALUES
  (1, 1, 7, '인덱스'),
  (2, 1, 7, '트랜잭션');
INSERT INTO norm_post_tags (post_id, tag_id, source)
VALUES
  (1, 10, 'MANUAL'),
  (1, 11, 'MANUAL'),
  (2, 10, 'MANUAL');

UPDATE norm_tags
SET name = '퍼포먼스'
WHERE id = 10;
SELECT ROW_COUNT() AS tag_name_updated_rows;

tag_name_updated_rows
1

태그 이름은 norm_tags의 한 행에만 있으므로, 이름을 바꾸는 UPDATE는 그 태그를 쓰는 연결이 몇 개든 한 행만 바꿉니다. 연결 테이블의 세 행은 건드리지 않습니다.

후보 키의 중복과 고아 연결도 제약이 막는지 확인합니다. 앞 문장의 오류가 뒤 문장의 실행을 막지 않도록, 두 문장은 따로 실행합니다.

INSERT INTO norm_tags (id, name)
VALUES (12, '퍼포먼스');

ERROR 1062 (23000): Duplicate entry '퍼포먼스' for key 'norm_tags.name'

INSERT INTO norm_post_tags (post_id, tag_id, source)
VALUES (999, 10, 'MANUAL');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`norm_post_tags`, CONSTRAINT `norm_post_tags_ibfk_1` ...)

태그 이름과 카테고리 이름은 각자의 원래 행 하나에만 저장되고, 연결 행은 식별 조합과 관계의 출처만 가집니다. 다만 분해가 많을수록 좋은 것은 아닙니다. 카테고리 그룹을 따로 떼어 낸 것은 그룹이 자기만의 속성이나 정책을 갖는 요구일 때 알맞고, 바뀌지도 참조되지도 않는 표시용 문자열이라면 지나친 분해일 수 있습니다.

함수 종속과 함께 업무에서 그 값이 사는 기간도 봅니다. 게시글을 발행한 시점의 작성자 표시명처럼 의도해서 남기는 스냅샷은, 현재 회원 이름을 잘못 복사한 중복이 아니라 그 시점의 사실입니다. author_name_at_publish처럼 시점이 드러나는 열 이름과 갱신하지 않는다는 규칙으로 정규화 위반과 구분합니다.

무손실 분해의 확인

분해가 행을 잃거나 늘리지 않았는지, 연결의 키 집합과 행 수를 비교합니다. 반복된 문자열보다 ID의 관계를 기준으로 맞추어 봅니다.

SELECT st.post_id, st.tag_id, s.title, t.name AS tag_name
FROM norm_post_tags AS st
JOIN norm_posts AS s ON s.id = st.post_id
JOIN norm_tags AS t ON t.id = st.tag_id
ORDER BY st.post_id, st.tag_id;

SELECT id AS tag_id, COUNT(DISTINCT name) AS names
FROM norm_tags GROUP BY id HAVING names <> 1;

SELECT post_id, tag_id, COUNT(*) AS duplicates
FROM norm_post_tags GROUP BY post_id, tag_id HAVING duplicates > 1;

post_id | tag_id | title    | tag_name
1       | 10     | 인덱스   | 퍼포먼스
1       | 11     | 인덱스   | MySQL
2       | 10     | 트랜잭션 | 퍼포먼스

JOIN은 연결 세 행을 그대로 돌려주고, 태그 10을 쓰는 두 게시글이 모두 새 이름을 읽습니다. 뒤의 두 진단 쿼리는 빈 결과를 돌려줍니다. 이 JOIN은 게시글과 태그, 제목, 태그 이름만 비교하므로, 작성자와 카테고리, 그룹까지 포함한 전체 분해의 무손실을 확인한 것은 아닙니다(연습 문제 3). 분해한 뒤 JOIN의 행 수가 원래보다 늘어난다면 JOIN 조건이나 후보 키가 잘못된 것입니다.

정규형과 업무의 예외

모든 관계를 무조건 BCNF로 나누면, 중요한 제약 하나를 한 테이블에서 표현하지 못할 수 있습니다. 그럴 때는 3NF에 머물고 별도의 제약이나 검사를 두는 쪽을 고를 수 있습니다. 또 정규화는 쓰기 모델의 일관성을 위한 기준이므로, 화면용 읽기 모델을 따로 평면화하더라도 원래 데이터가 하나이고 갱신의 소유가 분리되어 있으면 됩니다.

정규형을 적용할 때 따로 살필 예외도 있습니다. NULL은 함수 종속의 비교와 UNIQUE의 동작을 복잡하게 하므로 후보 키의 열이 NOT NULL인지 확인합니다. 다국어의 태그 이름은 이름 하나가 아니라 로캘별 자식 행일 수 있고, 소프트 삭제한 자연 키를 다시 쓸지에 따라 UNIQUE의 전략이 달라지며, 통계 캐시는 원래의 데이터와 다른 수명과 기준 시각을 가져야 합니다.

이미 중복된 데이터를 정규화된 구조로 옮길 때는, 대표 값을 고르는 규칙과 충돌 보고서를 먼저 만들고, 외래 키를 새 ID로 채운 뒤 제약을 더합니다. 옛 열과 새 열을 함께 읽는 기간은 짧게 두고, 일치 확인 쿼리의 불일치가 0건일 때 옛 구조를 지웁니다.

함수 종속의 가정과 오염된 데이터

"등급이 할인율 하나를 정한다"는 업무 규칙을 선언하면서 같은 gold 등급에 20과 5를 함께 저장한 표는, 정상적인 함수 종속의 예가 아닙니다. 분해하기 전에 가정을 다시 정하거나 위반한 데이터를 고쳐야 합니다. 아래는 일부러 오염시킨 예제이며, board_lab을 바꾸지 않도록 cs_db_lab에 만듭니다.

CREATE DATABASE IF NOT EXISTS cs_db_lab;
DROP TABLE IF EXISTS cs_db_lab.cs_fd_discount;
CREATE TABLE cs_db_lab.cs_fd_discount (
  customer_id INT PRIMARY KEY,
  tier VARCHAR(20) NOT NULL,
  discount_percent INT NOT NULL
);
INSERT INTO cs_db_lab.cs_fd_discount VALUES
  (1, 'gold', 20), (2, 'gold', 5), (3, 'silver', 0);

SELECT tier, COUNT(DISTINCT discount_percent) AS rates
FROM cs_db_lab.cs_fd_discount
GROUP BY tier
HAVING COUNT(DISTINCT discount_percent) > 1;

tier | rates
gold | 2

gold의 rates가 2라는 것은 tier → discount_percent라는 가정을 데이터가 어기고 있다는 뜻입니다. 현재 등급별 할인이라는 가정을 유지하고 gold의 기준을 20으로 정했다면, 고친 뒤 같은 진단이 빈 결과를 돌려주어야 합니다. 20과 5가 서로 다른 유효 기간이나 고객별 조건의 사실이라면 어느 하나를 지우지 않고, 기간이나 조건을 결정자에 포함하는 모델을 다시 정합니다.

UPDATE cs_db_lab.cs_fd_discount
SET discount_percent = 20
WHERE tier = 'gold';

SELECT tier, COUNT(DISTINCT discount_percent) AS rates
FROM cs_db_lab.cs_fd_discount
GROUP BY tier
HAVING COUNT(DISTINCT discount_percent) > 1;

UPDATE는 gold 두 행과 일치하지만, 값이 실제로 바뀌는 행은 고객 2 하나입니다. 이어지는 진단은 빈 결과를 돌려줍니다. 이 UPDATE는 예제에서 밝힌 정책을 고른 교정일 뿐, 실제 데이터의 정답을 추론한 것이 아니므로 모순된 두 값을 모두 보존한 분해로 기록하지 않습니다. 서로 독립된 다중값과 4NF, 5NF의 JOIN 반례는 선택 심화에서 이어집니다.

정규화의 판단 기준

리뷰에서는 정규형의 번호보다 함수 종속과 이상 현상의 반례를 봅니다. 분해한 이유와 다시 합치는 쿼리를 설명하지 못한다면, 테이블을 나눈 것만으로 설계가 나아지지는 않습니다.

정리

• 정규화는 후보 키와 함수 종속을 적는 데서 시작합니다.
• 2NF는 비주요 속성의 부분 종속을, 3NF는 결정자가 슈퍼 키가 아니고 오른쪽이 주요 속성도 아닌 종속을 없앱니다.
• BCNF는 모든 결정자가 슈퍼 키이길 요구하며, 그 분해는 무손실이어도 종속 보존을 잃을 수 있습니다.
• 분해는 무손실 JOIN과 제약의 거절로 확인합니다.
• 스냅샷과 파생 읽기 모델은 의도와 갱신의 소유를 밝혀 정규화 위반과 구분합니다.

연습 문제

1. 첨부파일 평면 테이블의 분해

post_id, attachment_no, post_title, author_email, attachment_url, attachment_title 평면 테이블의 후보 키와 종속을 쓰고 3NF로 분해해 보세요. 게시글별 첨부 순번은 유일하고, 저장 URL 하나에는 현재 첨부파일 제목 하나가 대응하며, 작성자의 이메일은 현재 회원 정보로 관리한다고 가정합니다.

DROP TABLE IF EXISTS norm_post_attachments;
DROP TABLE IF EXISTS norm_attachments;

CREATE TABLE norm_attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  canonical_url VARCHAR(768) NOT NULL,
  title VARCHAR(200) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (canonical_url)
);
CREATE TABLE norm_post_attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  attachment_no SMALLINT UNSIGNED NOT NULL,
  attachment_id BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE (post_id, attachment_no),
  UNIQUE (post_id, attachment_id),
  FOREIGN KEY (post_id) REFERENCES norm_posts (id),
  FOREIGN KEY (attachment_id) REFERENCES norm_attachments (id)
);

2. 할인율을 바꾸면서 과거 주문을 지키기

다음 달부터 gold의 할인을 5로 바꾸되, 지난달 주문에 적용한 20은 보존해야 합니다. tier → 현재 할인율 표 하나로 과거 주문의 금액까지 다시 계산해도 될까요?

3. 모든 속성으로 무손실 확인하기

본문의 JOIN은 네 열만 비교했습니다. 정규화한 테이블을 모두 JOIN해 평면 테이블의 여덟 열을 되살리고, 갱신 이상을 겪은 post_tags_flat_bad와 차집합으로 비교해 보세요. 어떤 행이 다르게 나오고, 그 차이는 무엇을 뜻하나요?

SELECT pt.post_id, pt.tag_id, p.title AS post_title, p.author_id,
       p.category_id, c.name AS category_name,
       g.name AS group_name, t.name AS tag_name
FROM norm_post_tags AS pt
JOIN norm_posts AS p ON p.id = pt.post_id
JOIN norm_categories AS c ON c.id = p.category_id
JOIN norm_category_groups AS g ON g.id = c.group_id
JOIN norm_tags AS t ON t.id = pt.tag_id
EXCEPT
SELECT post_id, tag_id, post_title, author_id,
       category_id, category_name, group_name, tag_name
FROM post_tags_flat_bad;

post_id | tag_id | post_title | author_id | category_id | category_name | group_name | tag_name
2       | 10     | 트랜잭션   | 1         | 7           | 데이터베이스  | 백엔드     | 퍼포먼스

다음 글 논리 모델의 물리 설계에서는 정규화한 관계를 MySQL 8.4의 이름과 타입, 시간, 제약으로 구체화합니다.