데이터베이스 정규화 (9): 정규화 절차와 역정규화, 나눈 뒤에 되돌리는 선택
kimenugi

데이터베이스 정규화 (9): 정규화 절차와 역정규화, 나눈 뒤에 되돌리는 선택

데이터베이스

데이터베이스정규화DB설계역정규화

1편은 정규화를 둘러싼 오해 세 가지에서 시작했습니다. 정규화를 하면 구조가 복잡해진다, 정규화하지 않은 상태가 곧 역정규화다, 테이블을 나누면 정규화다. 여덟 편에 걸쳐 정규형을 하나씩 정의했으니 이제 그 세 문장에 답할 수 있습니다.

이번 편이 마지막입니다. 1편부터 8편까지 흩어져 있던 절차를 한 줄기로 모으고, 그 절차를 거친 테이블에 중복을 다시 넣는 역정규화를 다룹니다. 역정규화가 정규화하지 않은 상태와 어디서 갈리는지는 MariaDB에서 돌려 확인했습니다.

시리즈 구성

  1. 데이터 무결성과 키
  2. 이상현상과 함수적 종속성
  3. 제1정규형 (1NF)
  4. 제2정규형 (2NF)
  5. 제3정규형 (3NF)
  6. 보이스-코드 정규형 (BCNF)
  7. 자연키와 대리키: 키 설계
  8. 제4정규형과 제5정규형, 함수적 종속 너머의 중복
  9. 정규화 절차와 역정규화 (이번 글)

정규화 절차: 종속을 찾아 테이블을 나눈다

종속을 찾고, 결정자를 키로 하는 테이블로 나눕니다. 편마다 찾는 종속이 달랐을 뿐 나누는 방법은 같았습니다.

단계찾는 것나누는 방법다룬 편
키 확정후보키와 기본키무엇이 행을 식별하는지 정한다1편, 7편
1NF한 칸에 든 여러 값, 반복 그룹값을 별도 테이블의 행으로 옮긴다3편
2NF부분 함수적 종속키의 일부가 결정하는 속성을 떼어낸다4편
3NF이행적 함수적 종속키가 아닌 속성이 결정하는 속성을 떼어낸다5편
BCNF결정자가 슈퍼키가 아닌 함수적 종속그 결정자를 키로 하는 테이블로 떼어낸다6편
4NF비자명한 다치 종속관계마다 테이블을 나눈다8편
5NF후보키에서 유도되지 않는 조인 종속셋 이상으로 나눈다8편

2NF부터 4NF까지 떼어내는 동작은 한 가지입니다. 종속 X → Y를 찾으면 X와 Y로 이루어진 테이블을 프로젝션(projection)으로 떼어내고, 원래 테이블에서는 Y를 빼고 X를 남깁니다. X가 새 테이블의 키가 되므로 두 테이블을 X로 조인하면 원래 테이블이 복원됩니다. 2편에서 무손실 조인이라고 부른 조건입니다. 다치 종속 X ↠ Y도 같은 모양으로 떼어냅니다. 5NF는 셋 이상으로 나누지만 조인하면 복원돼야 한다는 조건은 같습니다. 1NF만 종속이 아니라 값의 형태를 다루므로 이 동작에서 벗어납니다.

절차 전체가 향하는 곳은 하나입니다. 2편에서 인용한 Codd의 1971년 논문은 정규화의 목적을 바람직하지 않은 삽입, 갱신, 삭제 종속성을 제거하는 것이라고 밝힙니다. 종속을 떼어낼 때마다 한 사실이 한 곳에만 남고, 그 사실을 넣고 고치고 지우는 일이 한 행으로 끝납니다.

절차 적용 예시: 2편의 직원 테이블

절차를 한 테이블에 처음부터 끝까지 적용하면 어디서 멈추는지 보입니다. 2편의 직원 테이블을 다시 가져옵니다.

직원ID직원명부서ID부서명부서장
1김민준10영업부박부장
2이서연10영업부박부장
3박지호20개발부정부장

키 확정. 행을 식별하는 것은 직원ID 하나입니다. 함수적 종속은 둘입니다. 직원ID → 직원명, 부서ID와 부서ID → 부서명, 부서장입니다.

1NF. 모든 칸이 값 하나이고 반복 그룹이 없습니다.

2NF. 키가 직원ID 하나라 복합키가 아닙니다. 부분 함수적 종속은 복합키에서만 생기므로 통과합니다.

3NF. 부서ID는 키가 아닌데 부서명과 부서장을 결정합니다. 직원ID → 부서ID → 부서명으로 이어지는 이행적 함수적 종속입니다. 부서ID를 X로 삼아 떼어냅니다.

  • 직원 테이블: 직원ID(PK), 직원명, 부서ID(FK)
  • 부서 테이블: 부서ID(PK), 부서명, 부서장

BCNF. 나뉜 두 테이블에서 결정자는 직원ID와 부서ID뿐이고 둘 다 각 테이블의 키입니다. 통과합니다. 후보키가 하나뿐인 테이블은 3NF를 만족하면 BCNF도 만족한다고 6편에 적었는데, 이 테이블이 그 경우입니다.

4NF와 5NF. 두 테이블 모두 키 하나에 나머지 속성이 함수적으로 종속됩니다. 키가 정하는 값이 하나씩이라 값의 집합을 정하는 다치 종속이 끼어들 자리가 없고, 셋으로 나눠야 복원되는 조인 종속도 없습니다.

절차는 3NF 분해 한 번으로 끝났습니다. 이 테이블처럼 후보키가 하나이고 그 키가 나머지를 다 결정하는 테이블은 3NF 분해 뒤에 더 나눌 종속이 남지 않습니다.

정규화를 멈추는 기준: 종속 보존과 업무 규칙

3NF에서 멈추는 테이블만 있는 것은 아닙니다. 더 나눌 수 있는데 일부러 멈추는 자리가 두 곳 있습니다. 둘 다 앞 편에서 다음 편으로 미뤄 둔 이야기입니다.

첫째는 BCNF입니다. 6편의 학생, 과목, 교수 테이블에는 {학생, 과목} → 교수와 교수 → 과목 두 종속이 있었습니다. 교수가 키가 아닌 결정자라 BCNF로 가려면 (교수, 과목)과 (학생, 교수)로 나눠야 합니다. 나누고 나면 {학생, 과목} → 교수를 검사할 테이블이 없습니다. 학생과 과목이 한 테이블에 같이 있지 않기 때문입니다. 한 학생이 한 과목을 두 교수에게 듣는 행을 막으려면 두 테이블을 조인해서 확인해야 합니다. 이런 분해를 함수적 종속을 보존하지 않는다고 합니다. 3NF까지는 종속을 보존하면서 나눌 수 있지만 BCNF는 그렇지 못한 경우가 있어, 그 종속을 제약으로 걸어 두는 쪽을 택하면 3NF에서 멈춥니다.

둘째는 5NF입니다. 8편에서 본 것처럼 조인 종속은 데이터에 드러나지 않습니다. 세 조건이 맞으면 나머지 조합이 반드시 있어야 한다는 업무 규칙이 있을 때만 셋으로 나눕니다. 규칙이 없는데 나누면 조인에서 없던 행이 생깁니다.

멈추는 기준은 정규형의 번호가 아닙니다. 이상현상이 사라졌고, 남은 종속을 한 테이블 안에서 검사할 수 있으면 거기서 멈춥니다. 앞의 직원 테이블처럼 후보키가 하나인 테이블은 3NF에서 이 조건을 만족하고, 4NF는 무관한 목록 둘을 한 테이블에 담지 않는 것으로 자연히 맞춰집니다.

역정규화의 정의

여기까지가 정규화입니다. 남은 것은 2편이 9편으로 미뤄 둔 역정규화입니다.

위키백과의 역정규화 항목은 역정규화를 중복 데이터를 더하거나 데이터를 묶어서, 쓰기 성능 일부를 잃는 대신 읽기 성능을 높이려는 과정으로 정의합니다. 같은 항목은 역정규화가 정규화하지 않은 상태와 다르다고 적습니다. 정규화를 마치고 제약을 걸어 둔 뒤에 하는 일이라는 것입니다.

같은 항목이 드는 방법은 세 가지입니다. 조인 상대 테이블의 속성을 복사해 두기, 일대다 관계에서 개수나 합계를 미리 저장해 두기, 집계 결과를 별도 테이블로 만들어 두기입니다. 복사본을 누가 유지하느냐로 둘로 나뉩니다. 하나는 DBMS가 복사본을 유지하는 쪽으로, Oracle과 PostgreSQL의 물리화 뷰(materialized view)가 그렇습니다. 다른 하나는 설계자가 트리거나 응용 코드로 유지하는 쪽입니다.

얼마나 빨라지는지는 여기서 재지 않았습니다. 조인 하나를 없애는 효과는 행 수와 인덱스와 쿼리 모양에 따라 달라지므로 한 번 잰 숫자를 옮길 수 없습니다. 대신 중복을 넣는 순간 무엇이 돌아오는지를 쟀습니다.

역정규화의 문제 1: 갱신 이상이 다시 생긴다

고객 테이블과 주문 테이블을 만들고, 주문 테이블에 고객명을 복사해 두었습니다. 주문 목록을 보여줄 때 고객 테이블과 조인하지 않으려는 흔한 형태입니다.

CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  customer_name VARCHAR(50) NOT NULL,
  amount INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customer(id)
);
INSERT INTO customer VALUES (1, '김철수'), (2, '이영희');
INSERT INTO orders VALUES
  (10, 1, '김철수', 1000), (11, 1, '김철수', 2000), (12, 2, '이영희', 500);

고객 1의 이름을 바꿨습니다.

UPDATE customer SET name = '김철수2' WHERE id = 1;
SELECT o.id, o.customer_name, c.name AS current_name
FROM orders o JOIN customer c ON c.id = o.customer_id;
idcustomer_namecurrent_name
10김철수김철수2
11김철수김철수2
12이영희이영희

주문 두 행이 옛 이름을 들고 있습니다. 2편에서 본 갱신 이상 그대로입니다. 한 사실이 여러 행에 적혀 있어 한 곳만 고치면 나머지가 어긋납니다. 외래키는 이것을 막지 못합니다. 참조 무결성은 customer_id가 가리키는 고객이 존재하는지만 보고, 복사한 이름이 맞는지는 보지 않습니다.

역정규화의 문제 2: 동기화를 트리거와 응용 코드가 맡는다

어긋난 두 행을 맞추려면 누군가 고객 테이블의 변경을 주문 테이블로 옮겨야 합니다. 트리거를 걸어 봤습니다.

CREATE TRIGGER customer_name_sync AFTER UPDATE ON customer FOR EACH ROW
BEGIN
  IF NEW.name <> OLD.name THEN
    UPDATE orders SET customer_name = NEW.name WHERE customer_id = NEW.id;
  END IF;
END;

UPDATE customer SET name = '김철수3' WHERE id = 1;
SELECT COUNT(*) FROM orders o JOIN customer c ON c.id = o.customer_id
WHERE o.customer_name <> c.name;
-- 0

어긋난 행이 0이 됐습니다. 대신 고객 이름을 한 번 바꾸는 UPDATE가 그 고객의 주문 행 수만큼의 UPDATE를 끌고 다닙니다. 위키백과 항목이 쓰기 성능 일부를 잃는다고 적은 자리가 여기입니다.

집계값을 미리 저장하는 쪽도 같은 모양입니다. 고객 테이블에 주문 합계 컬럼을 두고 채운 뒤, 트리거 없이 주문을 하나 넣었습니다.

ALTER TABLE customer ADD COLUMN order_total INT NOT NULL DEFAULT 0;
UPDATE customer c
SET order_total = (SELECT COALESCE(SUM(amount), 0) FROM orders o WHERE o.customer_id = c.id);

INSERT INTO orders VALUES (13, 2, '이영희', 700);
SELECT c.id, c.order_total,
       (SELECT SUM(amount) FROM orders o WHERE o.customer_id = c.id) AS real_total
FROM customer c;
idorder_totalreal_total
130003000
25001200

주문을 넣는 쪽이 합계를 갱신하지 않으면 그 자리에서 어긋납니다.

정규화된 스키마에서는 1편의 기본키, 외래키, 유일 제약이 무결성을 지켰습니다. 복사한 컬럼과 미리 계산한 집계값은 어떤 제약도 지켜 주지 않습니다. 그 값이 맞는지는 트리거나 응용 코드나 배치가 책임집니다. 역정규화가 정규화하지 않은 상태와 갈리는 자리가 여기입니다. 역정규화한 스키마에는 어떤 중복을 왜 넣었고 누가 맞추는지가 정해져 있습니다. 정규화하지 않은 스키마에는 어떤 중복이 있는지도 적혀 있지 않습니다.

1편의 오해 세 가지에 대한 답

정규화를 하면 구조가 복잡해진다: 테이블 수는 늘고, 2편에 적은 대로 조회할 때 조인도 늡니다. 다만 각 테이블은 한 종류의 사실만 담고 한 사실은 한 곳에만 있습니다. 복잡해지는 것은 조회 쿼리이고, 단순해지는 것은 갱신과 규칙 검사입니다. 어느 쪽을 택할지는 그 테이블을 읽는 일과 고치는 일 중 무엇이 더 잦고 무엇이 틀리면 더 곤란한지로 정합니다.

정규화하지 않은 상태가 곧 역정규화다: 다릅니다. 역정규화는 정규화된 스키마에서 출발해 조회를 위해 중복을 골라 넣고, 그 중복의 동기화 책임을 정한 상태입니다. 정규화하지 않은 상태는 어떤 종속이 한 테이블에 섞여 있는지 확인한 적이 없는 상태입니다. 앞의 주문 테이블에서 트리거를 걸고 안 걸고의 차이가 아니라, 복사한 컬럼이라는 사실을 알고 있는지의 차이입니다.

테이블을 나누면 정규화다: 아닙니다. 종속을 근거로, 조인하면 원래 테이블이 복원되게 나눠야 정규화입니다. 8편에서 규칙 없는 테이블을 셋으로 나누자 조인에서 없던 행이 생겼습니다. 근거 없이 나눈 테이블은 정규화한 것이 아니라 복원이 안 되는 조각입니다.

정리

  • 정규화는 종속을 찾아 결정자를 키로 하는 테이블로 떼어내는 한 절차다. 2NF부터 5NF까지 찾는 종속만 다르고 떼어내는 동작은 같으며, 조인하면 원래 테이블이 복원된다
  • 후보키가 하나이고 그 키가 나머지를 다 결정하는 테이블은 3NF 분해 뒤에 더 나눌 종속이 남지 않는다
  • 멈추는 기준은 정규형 번호가 아니다. 이상현상이 사라졌고 남은 종속을 한 테이블에서 검사할 수 있으면 멈춘다. BCNF는 종속 보존과, 5NF는 업무 규칙과 맞바꾼다
  • 역정규화는 정규화한 스키마에 읽기를 위해 중복을 다시 넣는 선택이다. 복사한 컬럼과 미리 계산한 집계값이 대표적이다
  • 복사한 컬럼은 갱신 이상을 다시 불러온다. 외래키는 복사본이 맞는지 보지 않는다
  • 복사본의 정확성은 제약이 아니라 트리거, 응용 코드, 배치, 또는 DBMS의 물리화 뷰가 책임진다. 쓰기 한 번이 복사본 수만큼의 쓰기로 늘어난다
  • 역정규화와 정규화하지 않은 상태의 차이는 어떤 중복을 왜 넣었고 누가 맞추는지가 정해져 있는지에 있다

부록. 실험환경

본문의 갱신 이상과 트리거 동작은 MariaDB 11.4.2, InnoDB, 기본 문자셋 utf8mb4에서 확인했습니다. 트리거는 DELIMITER //로 구분자를 바꾼 뒤 만들었고, 본문 코드에는 구분자 변경을 생략했습니다. 어긋난 행 수는 아래 쿼리로 셌습니다.

SELECT COUNT(*) AS stale_rows
FROM orders o JOIN customer c ON c.id = o.customer_id
WHERE o.customer_name <> c.name;

참고 문헌

  • E.F. Codd, Further Normalization of the Data Base Relational Model, IBM Research Report RJ909, 1971. (정규화의 목적을 바람직하지 않은 삽입, 갱신, 삭제 종속성의 제거로 밝힌 논문)
  • Denormalization, Wikipedia: 역정규화의 정의, 정규화하지 않은 상태와의 구분, DBMS가 유지하는 방식과 설계자가 유지하는 방식.
  • Database normalization, Wikipedia: 앞 단계를 만족해야 다음 정규형으로 갈 수 있다는 절차의 성격과 단계별 분해 예제.