정규화와 함수 종속

정규형 이름을 순서대로 외우면 실제 테이블에서 무엇을 분리해야 하는지 판단하기 어렵습니다.

먼저 후보 키와 함수 종속을 쓰고, 한 사실을 수정·삽입·삭제할 때 다른 사실이 흔들리는 반례를 찾습니다.

관계 정규화는 이메일의 대소문자·공백을 맞추는 문자열 정규화와 다른 개념입니다.

한 업무 사실을 한곳에 저장하도록 테이블을 분해해 갱신·삽입·삭제 이상을 줄이는 설계 과정입니다.

예를 들어 아래 평면 데이터는 게시글 제목과 태그 이름을 연결 수만큼 반복합니다.

행의 최소 식별 조합은 (post_id, tag_id)이지만 post_title은 post_id 하나만 알면 정해지고, tag_name은 tag_id 하나만 알면 정해집니다.

이처럼 어떤 열 값이 다른 열 값을 하나로 결정하는 규칙을 함수 종속이라 하며 post_id → post_title처럼 씁니다.

게시글·태그·연결을 분리하면 제목과 태그 이름은 각각 한곳에만 남고 연결 테이블에는 두 FK만 남습니다.

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

태그 목록 CSV까지 있으면 1NF 이전 문제도 섞입니다.

부분 종속의 중복

같은 게시글 제목이 태그 수만큼 반복되고 같은 태그 이름이 게시글 수만큼 반복됩니다.

한 연결 행의 tag_name만 바꾸면 같은 tag_id에 두 이름이 생기고 마지막 연결 삭제가 태그 자체 정보를 지웁니다.

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) FROM post_tags_flat_bad GROUP BY tag_id;

tag_id 10이 두 현재 이름을 갖게 되어 부분 종속의 갱신 이상이 드러납니다. 다음은 가능한 표시 예이며, ORDER BY 없는 GROUP_CONCAT 내부 이름 순서는 보장되지 않습니다.

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

same determinant tag_id=10 → two tag_name values

1NF는 셀을 업무상 원자 값으로 두고 반복 그룹을 행으로 표현하는 출발점입니다.

현재 표는 셀은 원자적이지만 복합 후보 키 일부에 일반 속성이 종속되어 2NF를 위반합니다.

게시글을 분리한 뒤에도 분류 정보를 복사하면 post_id → category_id → 분류의 이름·그룹 이행 종속이 남습니다. 분해 DDL은 category_id → group_id → group_name으로 그룹을 참조합니다.

이 예제에서는 게시글 키가 아닌 분류 ID에 종속되는 분류 정보를 따로 둡니다.

함수 종속 위반을 행 변화로 찾기

1. 후보 키 — 한 행을 최소로 식별하는 열 집합을 씁니다.
2. 결정자 — 각 일반 속성을 결정하는 최소 열을 화살표로 적습니다.
3. 이상 — 결정자 한 값의 수정·삽입·삭제 반례를 실행합니다.
4. 분해 — 각 종속의 결정자가 후보 키가 되는 관계로 옮깁니다.

결정자와 정규형

어떤 후보 키에도 속하지 않는 속성을 비주요 속성이라 합니다. 2NF는 1NF이면서 비주요 속성이 어떤 후보 키의 진부분집합에도 종속되지 않는 조건입니다.

3NF는 모든 비자명 종속 X → A마다 X가 슈퍼 키이거나 A가 어떤 후보 키에 포함되는 주요 속성이어야 합니다. 후보 키가 여러 개인 경우까지 포함하므로 ‘비키 사이 이행 종속 제거’라는 요약만으로 판정하지 않습니다.

BCNF는 모든 비자명 함수 종속의 결정자가 슈퍼 키여야 한다는 더 강한 기준입니다.

예시

수강 관계의 속성을 (학생, 과목, 교사)로 두고, 학생은 한 과목에 교사 한 명만 배정받으며 교사는 한 과목만 담당한다고 가정합니다. 종속은 (학생, 과목) → 교사와 교사 → 과목입니다. (학생, 과목)은 교사까지 결정하고, (학생, 교사)도 교사의 과목을 알아내므로 두 조합 모두 후보 키입니다. 교사만으로는 학생을 알 수 없습니다.

교사 → 과목의 결정자는 슈퍼 키가 아니지만 오른쪽 과목은 후보 키에 포함되는 주요 속성입니다. 이 관계는 3NF를 만족하면서 BCNF는 위반합니다. 교사의 과목을 수강 행마다 반복하는 문제를 줄이려고 (학생, 교사)와 (교사, 과목)으로 분해하면 공통 교사가 뒤 관계를 결정하므로 무손실입니다. 다만 두 테이블의 제약만으로 “학생별 한 과목에 한 교사”까지 자동 강제하지는 못합니다. 무손실·종속 보존과 3NF/BCNF의 기준

혼자 판단해 보기: 분해한 첫 표에 (민, 김), (민, 이), 둘째 표에 (김, DB), (이, DB)가 있다면 각 표의 키는 지키면서 원래 규칙을 깨는 부분은 무엇일까요?

분해한 관계를 JOIN해 원래 관계를 무손실로 복원할 수 있고 필요한 유일성·참조 규칙을 보존해야 합니다.

테이블 수 증가 자체가 목표가 아니라 사실을 한곳에 저장해 이상을 없애는 것이 목표입니다.

1NF에서 BCNF까지 분해 질문 세우기

1. 1NF — CSV 태그를 post_tags 여러 행으로 바꿉니다.
2. 2NF — 게시글 속성과 태그 속성을 조합 연결에서 각각 분리합니다.
3. 3NF — 카테고리와 분류의 독립 정체성을 분리합니다.
4. BCNF — 교사-과목-교실처럼 후보 키가 겹치는 별도 종속이 있는지 다시 봅니다.

엔티티와 연결 관계의 분해

회원, 카테고리 그룹, 카테고리, posts, 태그, post_tags로 분리합니다. 앞의 충돌하는 태그 10 이름은 이 fixture에서 한 원본 이름을 선택해 이관한 뒤 수정합니다. 모순된 두 현재 이름을 그대로 보존하는 무손실 변환은 아닙니다.

post_tags에는 두 FK와 관계 속성만 둡니다.

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;

아래 두 예상 실패 문장은 오류 하나가 다음 문장의 실행을 막지 않도록 각각 따로 실행합니다.

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

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

tag_name과 category_name은 각 원본 한 행에만 저장되고 연결 행은 식별 조합과 관계의 출처만 가집니다.

tag_id 10 source rows: 1 tag row
tag name updated rows: 1
post_tags rows after tag rename: unchanged
duplicate tag name: ERROR 1062
orphan post/tag link: ERROR 1452

category_group 분리는 분류가 독립 속성·정책을 갖는 요구일 때 적절합니다.

단순 표시 문자열이며 별도의 변경·참조가 없다면 과도한 분해일 수 있습니다.

함수 종속과 업무 수명을 함께 봅니다.

게시글 발행 당시 작성자 표시명처럼 의도한 스냅샷은 현재 원본의 중복이 아니라 시점 사실입니다.

컬럼 이름과 갱신 금지 규칙으로 정규화 위반과 구분합니다.

분해 뒤 무손실과 제약 보존 확인

1. 무손실 — 정규 테이블 JOIN 결과가 원래 정상 평면 행과 같은지 일치 여부를 확인합니다.
2. 제약 — 태그·카테고리 후보 키 중복과 고아 연결을 시도합니다.
3. 변경 — 태그 이름 한 행 변경 뒤 모든 게시글 조회가 새 값을 읽는지 봅니다.
4. 삭제 — 마지막 연결 삭제 후 태그 보존·삭제 정책을 요구와 비교합니다.

무손실 분해와 종속성 확인

정규화가 누락을 만들지 않았는지 동일 키 집합과 연결 수를 비교합니다.

원본 중복 문자열보다 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;

제시한 INSERT로 계산하면 JOIN은 세 연결의 게시글·태그·제목·태그명을 반환하고 두 진단은 0행입니다. 작성자·분류·그룹 등 나머지 속성을 이 쿼리가 비교하지는 않으므로 전체 분해의 무손실성 증명과는 구분합니다.

분해가 행을 곱하면 JOIN 조건 또는 후보 키가 잘못된 것입니다.

삽입·수정·삭제 반례 재실행

1. 종속 지도 — 각 열을 결정자→종속자 화살표로 연결합니다.
2. 부분 종속 — 복합 키의 한 열만으로 결정되는 속성을 찾습니다.
3. 이행 종속 — A→B와 B→C에서 C를 어디로 옮길지 판단합니다.
4. 무손실 — 분해 JOIN의 키·행 수·NULL을 원본과 비교합니다.

정규형과 업무 스냅샷을 구분하기

BCNF와 3NF 차이는 후보 키가 여러 개 겹치는 관계에서 드러납니다.

무조건 BCNF로 분해해 중요한 제약이 한 테이블에서 표현되지 않으면 3NF와 별도 제약을 선택할 수 있습니다.

정규화는 쓰기 모델의 일관성 기준입니다.

화면용 읽기 모델을 별도로 평면화해도 단일 원본과 갱신 소유권을 분리하면 됩니다.

NULL·다중 후보 키·역정규화 예외

• NULL은 함수 종속 비교와 UNIQUE 동작을 복잡하게 하므로 후보 키 NOT NULL을 확인합니다.
• 다국어 태그 이름은 단일 이름이 아니라 로캘별 자식 행일 수 있습니다.
• 소프트 삭제된 자연 키를 재사용할지에 따라 UNIQUE 전략이 달라집니다.
• 통계 캐시는 원본 정규화와 별도의 수명·기준 시각을 가져야 합니다.

정규화 단계별 비용 비교

• 3NF 원본 — 갱신 이상 제거. 감수할 비용은 조회 JOIN 증가, 적합한 조건은 업무 쓰기 모델일 때.
• BCNF 분해 — 결정자 규칙 강화. 감수할 비용은 일부 제약 표현 어려움, 적합한 조건은 겹친 후보 키 이상이 있을 때.
• 스냅샷 컬럼 — 시점 사실 보존. 감수할 비용은 의도한 중복, 적합한 조건은 과거 값 자체가 요구일 때.
• 파생 읽기 모델 — 빠른 화면 조회. 감수할 비용은 동기화·신선도, 적합한 조건은 원본과 소유권이 분리될 때.

기존 중복을 안전하게 이관하기

중복 데이터 정리는 대표 값 규칙과 충돌 보고서를 먼저 만들고 FK를 새 ID로 백필한 뒤 제약을 추가합니다.

정규화 마이그레이션 동안 이전/신규 열 이중 읽기 기간을 짧게 두고 일치 검사 쿼리의 불일치가 0건일 때 이전 구조를 제거합니다.

정규화된 논리 모델을 다음 문서에서 MySQL 이름·타입·시간·FK 호환성으로 내리고, 이후 명시적 역정규화의 검증 규칙을 붙입니다.

정규화 판단 기준

정규형 번호보다 함수 종속과 이상 현상 증거를 리뷰합니다.

분해 이유와 다시 합치는 쿼리를 설명하지 못하면 테이블을 나눈 것만으로 품질이 높아지지 않습니다.

함수 종속 가정과 오염 데이터를 먼저 구분하기

“등급이 할인율 하나를 결정한다”는 업무 규칙을 선언하면서 같은 gold 등급에 20과 5를 동시에 저장한 표는 정상 함수 종속의 예가 아닙니다. 분해 전에 가정을 다시 정하거나 위반 데이터를 교정해야 합니다. 아래는 의도적으로 오염시킨 자체 fixture입니다.

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;

gold의 rates=2가 위반 증거입니다. 현재 등급별 할인이라는 가정을 유지하고 기준 gold=20으로 정했다면 교정 후 같은 진단이 0행이어야 합니다. 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는 예제에서 명시한 정책을 선택한 교정이며 모든 실제 데이터의 정답을 추론한 것이 아닙니다. 서로 모순된 원래 두 값을 모두 보존한 무손실 분해라고 기록하지 않습니다. 독립 다중값과 4NF/5NF의 JOIN 반례는 선택 심화에서 이어집니다.

직접 문제: 다음 달부터 gold 할인을 5로 바꾸되 지난달 주문의 20을 보존해야 하면 tier → 현재할인율 표 하나로 과거 주문까지 재계산해도 될까요?

연습 문제

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

CREATE TABLE 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 posts (id),
  FOREIGN KEY (attachment_id) REFERENCES attachments (id)
);

핵심 정리

• 정규화는 후보 키와 함수 종속에서 시작합니다.
• 비주요 속성의 부분 종속을 2NF에서, 남은 종속을 3NF의 결정자·주요 속성 조건으로 검토합니다.
• 분해는 무손실 JOIN과 제약 보존으로 검증합니다.
• 스냅샷과 파생 읽기 모델은 의도와 갱신 소유권을 명시합니다.

다음 문서에서는 정규화된 관계를 MySQL 8.4의 이름·타입·시간·제약으로 구체화합니다.