정규화

같은 사실을 한 곳에만 둡니다

실습 데이터베이스는 표 여섯 개로 나뉘어 있습니다. 글 목록 하나를 보려고 Board.POSTS · Board.BOARDS · Board.CATEGORIES · Member.USERS 를 이어야 합니다. 그냥 한 표에 다 넣어 두면 조인이 필요 없을 텐데 왜 나눴는지가 이 단원의 물음입니다.

정규화는 같은 사실이 여러 곳에 적히지 않게 표를 나누는 일입니다. "2번 게시판의 이름은 자유게시판" 이라는 사실은 한 곳에만 있어야 합니다. 그 사실이 507군데에 적혀 있으면 이름을 바꿀 때 507군데를 고쳐야 하고, 한 군데라도 빠뜨리면 데이터베이스 안에서 답이 갈립니다.

먼저 반대쪽을 만들어 보겠습니다. 조회 결과를 그대로 표로 만드는 SELECT … INTO 로 모든 것을 한 표에 넣은 POSTS_FLAT 을 만듭니다.

SELECT P.num, B.name AS board_name, B.code AS board_code,
       C.name AS category_name, U.nickname, U.email,
       P.title, P.hit_count, P.reg_date
INTO Board.POSTS_FLAT
FROM Board.POSTS P
    JOIN Board.BOARDS B ON B.num = P.board_num
    JOIN Member.USERS U ON U.num = P.user_num
    LEFT JOIN Board.CATEGORIES C ON C.num = P.category_num;

SELECT TOP 5 * FROM Board.POSTS_FLAT ORDER BY num;

조회는 편해졌습니다. 조인이 하나도 없습니다. 이제 이 표가 무엇을 잃었는지 하나씩 세어 보겠습니다.

한 칸에 값 하나

1정규형은 한 칸에 값을 하나만 두는 것입니다. 값 여러 개를 쉼표로 이어 한 칸에 넣는 방식이 이 규칙을 어깁니다. 흔히 보이는 모양이라 실제로 만들어 확인해 보겠습니다. 첨부 100건을 게시판별로 이어 붙입니다.

SELECT P.board_num,
       STRING_AGG(F.origin_name, ',') WITHIN GROUP (ORDER BY F.num) AS files
INTO Board.BOARD_FILES_CSV
FROM Board.FILES F
    JOIN Board.POSTS P ON P.num = F.post_num
GROUP BY P.board_num;

SELECT board_num, LEN(files) AS 글자수, LEFT(files, 40) AS 앞부분
FROM Board.BOARD_FILES_CSV ORDER BY board_num;

이제 png 첨부가 있는 게시판을 찾아봅니다. 두 방식이 다른 답을 냅니다.

-- 쉼표로 이어 붙인 열에서
SELECT board_num FROM Board.BOARD_FILES_CSV
WHERE files LIKE '%.png';

-- 나뉘어 있는 표에서
SELECT DISTINCT P.board_num
FROM Board.FILES F JOIN Board.POSTS P ON P.num = F.post_num
WHERE F.origin_name LIKE '%.png';

세 게시판 모두 png 첨부를 가지고 있는데 이어 붙인 열에서는 3번 하나만 나왔습니다. LIKE '%.png' 는 문자열의 끝을 보는데, 이어 붙인 값에서 끝은 마지막 첨부 하나뿐이기 때문입니다. 3번 게시판의 목록이 마침 png 로 끝났을 뿐입니다.

LIKE '%png%' 로 바꾸면 셋 다 나옵니다. 하지만 이번에는 이름 안에 png 가 들어간 다른 파일까지 걸립니다.

SELECT CASE WHEN N'첨부_1.zip,png_대본.zip' LIKE N'%png%'
            THEN N'찾힘' ELSE N'못 찾음' END AS 결과;

개수를 세는 것도 마찬가지입니다. 나뉘어 있으면 COUNT(*) 로 끝나지만, 이어 붙인 열에서는 쉼표를 세야 합니다 (LEN(files) - LEN(REPLACE(files, ',', '')) + 1). 실습 데이터에서는 두 방식이 35 · 55 · 10 으로 같은 값을 냅니다. 다만 파일 이름 자체에 쉼표가 들어가는 순간 이 식은 조용히 틀립니다. 보고서,1분기.xlsx 같은 이름은 실제로 만들 수 있습니다.

복합 키의 일부에만 딸린 열

2정규형은 기본 키가 여러 열로 이루어졌을 때, 그 일부에만 딸린 열을 떼어 내는 것입니다. 키가 열 하나면 애초에 해당하지 않습니다.

게시판과 말머리로 글 수를 집계한 표를 만들어 봅니다. 기본 키는 (board_num, category_num) 두 열입니다.

SELECT C.board_num, C.num AS category_num,
       B.name AS board_name, C.name AS category_name,
       COUNT(P.num) AS post_count
INTO Board.POST_STAT
FROM Board.CATEGORIES C
    JOIN Board.BOARDS B ON B.num = C.board_num
    LEFT JOIN Board.POSTS P ON P.category_num = C.num
GROUP BY C.board_num, C.num, B.name, C.name;

SELECT * FROM Board.POST_STAT ORDER BY board_num, category_num;

post_count 는 두 열이 함께 있어야 정해집니다. 제자리입니다. 그런데 board_name 은 board_num 하나만 보면 정해집니다. 키의 절반에만 딸려 있습니다. 그래서 같은 이름이 다섯 번씩 반복됩니다.

대가는 갱신할 때 드러납니다. 게시판 이름 하나를 바꾸는 데 다섯 행을 고쳐야 합니다.

SELECT COUNT(*) AS 고칠행 FROM Board.POST_STAT
WHERE board_name = N'자유게시판';

SELECT COUNT(*) AS 고칠행 FROM Board.BOARDS
WHERE name = N'자유게시판';

고칠 것은 board_name 열을 지우고 필요할 때 Board.BOARDS 를 잇는 것입니다. 실습 데이터베이스의 Board.CATEGORIES 가 이미 그렇게 되어 있습니다. 게시판 이름을 담지 않고 board_num 만 가지고 있습니다.

키가 아닌 열에 딸린 열

3정규형은 키가 아닌 열에 딸려 있는 열을 떼어 내는 것입니다. 앞에서 만든 POSTS_FLAT 안에 이 경우가 들어 있습니다. 기본 키는 num(글 번호)인데, board_name 은 글 번호가 아니라 board_code 를 보면 정해집니다.

SELECT board_code, COUNT(DISTINCT board_name) AS 이름수, COUNT(*) AS 행수
FROM Board.POSTS_FLAT
GROUP BY board_code
ORDER BY board_code;

이름수가 모두 1 입니다. 코드 하나에 이름 하나가 딸려 있다는 뜻입니다. 글 번호 → 게시판 코드 → 게시판 이름으로 이어지는 이 관계를 이행 종속이라 부릅니다. 이름은 글에 딸린 사실이 아니라 게시판에 딸린 사실이므로 Board.BOARDS 에 있어야 합니다.

이름수가 1 이 아니라면 그 자체로 이미 깨진 자료입니다. 같은 free 인데 어떤 행은 "자유게시판", 어떤 행은 "자유마당" 으로 적혀 있다는 뜻이고, 어느 쪽이 맞는지 데이터베이스는 알려 주지 못합니다. 합친 표에서 이름을 고치다 몇 행을 빠뜨리면 정확히 이 상태가 됩니다.

세 가지가 무너집니다

규칙을 어겼을 때 실제로 무엇이 잘못되는지가 중요합니다. 셋으로 나뉩니다.

갱신 이상

한 사실이 여러 행에 적혀 있으면 고칠 때 그 수만큼 손댑니다.

-- 한소망 님이 닉네임을 바꾼다면
SELECT COUNT(*) AS 고칠행 FROM Board.POSTS_FLAT WHERE nickname = N'한소망';
SELECT COUNT(*) AS 고칠행 FROM Member.USERS   WHERE nickname = N'한소망';

-- 게시판 이름을 바꾼다면
SELECT COUNT(*) AS 고칠행 FROM Board.POSTS_FLAT WHERE board_name = N'자유게시판';
SELECT COUNT(*) AS 고칠행 FROM Board.BOARDS     WHERE name = N'자유게시판';

삽입 이상

합친 표에서는 글이 있어야만 게시판과 말머리가 존재할 수 있습니다. 글이 하나도 없는 말머리는 적을 자리가 없습니다. 실습 데이터에 실제로 그런 말머리가 있습니다.

SELECT (SELECT COUNT(*) FROM Board.CATEGORIES) AS 말머리표,
       (SELECT COUNT(DISTINCT category_name) FROM Board.POSTS_FLAT
        WHERE category_name IS NOT NULL) AS 합친표에보이는것;

SELECT C.num, C.name FROM Board.CATEGORIES C
WHERE NOT EXISTS (
    SELECT 1 FROM Board.POSTS_FLAT F WHERE F.category_name = C.name
)
ORDER BY C.num;

말머리를 미리 만들어 두고 글을 받는 것이 정상인데, 합친 표에서는 그 순서가 불가능합니다. 새 말머리를 넣으려면 가짜 글을 하나 넣어야 합니다.

삭제 이상

반대 방향도 있습니다. 글을 지웠을 뿐인데 말머리가 함께 사라집니다. 되돌릴 수 있게 트랜잭션 안에서 확인합니다.

BEGIN TRAN;

DELETE FROM Board.POSTS_FLAT WHERE category_name = N'모집';

SELECT COUNT(DISTINCT category_name) AS 남은말머리
FROM Board.POSTS_FLAT WHERE category_name IS NOT NULL;

ROLLBACK;

나뉘어 있으면 이런 일이 없습니다. Board.POSTS 에서 글을 지워도 Board.CATEGORIES 의 "모집" 은 그대로 남습니다. 글과 말머리는 서로 다른 사실이기 때문입니다.

글자만 세어도 37배입니다

합친 표가 얼마나 같은 말을 되풀이하는지 바이트로 재 봅니다. DATALENGTH 는 값이 차지하는 실제 바이트를 냅니다.

SELECT SUM(DATALENGTH(board_name) + DATALENGTH(nickname)
         + ISNULL(DATALENGTH(email), 0)
         + ISNULL(DATALENGTH(category_name), 0)) AS 합친표
FROM Board.POSTS_FLAT;

SELECT (SELECT SUM(DATALENGTH(name)) FROM Board.BOARDS)
     + (SELECT SUM(DATALENGTH(nickname) + ISNULL(DATALENGTH(email), 0)) FROM Member.USERS)
     + (SELECT SUM(DATALENGTH(name)) FROM Board.CATEGORIES) AS 나뉜표;

507행에서 16KB 대 0.4KB 입니다. 크지 않아 보이지만 이 비율은 행이 늘어도 그대로입니다. 507만 행이면 16GB 와 0.4KB 가 됩니다. 그리고 공간보다 중요한 것은 앞에서 본 갱신 이상입니다. 되풀이된 값은 언제든 서로 어긋날 수 있습니다.

3정규형까지가 기준선입니다

정규형은 6정규형까지 있지만 실무에서 목표로 삼는 곳은 3정규형입니다. 그 위(BCNF · 4NF · 5NF)는 특별한 모양의 자료에서만 문제가 되고, 대부분의 업무 자료는 3정규형에서 이상 현상이 사라집니다.

실습 데이터베이스가 그 상태입니다. 게시판 이름은 Board.BOARDS 에만, 닉네임은 Member.USERS 에만, 말머리 이름은 Board.CATEGORIES 에만 있습니다. 글은 그 번호만 가지고 있습니다.

반대로 일부러 되돌리는 경우도 있습니다. 목록 화면마다 댓글 수를 세는 것이 부담이면 POSTS 에 comment_count 를 두는 식입니다. 이것을 반정규화라고 합니다. 조회는 빨라지지만 댓글을 넣고 지울 때마다 그 값을 함께 맞춰야 하고, 맞추는 것을 잊으면 틀린 수가 화면에 남습니다.

순서가 중요합니다. 먼저 정규화한 뒤, 느린 것이 측정된 자리만 되돌립니다. 처음부터 합쳐 놓고 시작하면 무엇이 느린지 알기 전에 이상 현상부터 만납니다. 무엇을 근거로 되돌릴지는 5부(성능)에서 실행 계획을 보고 판단합니다.

직접 해보기

이 단원에서 만든 표 셋은 여기서만 사용합니다. 다음 단원으로 넘어가기 전에 지우십시오.
DROP TABLE Board.POSTS_FLAT, Board.BOARD_FILES_CSV, Board.POST_STAT;