5-2.정규화

가.제 1 정규화

모델링의 한 과정인 정규화는 이미 만들어진 테이블을 재구성하여 효율성을 높이고 논리적 구조를 개선하는 절차이다. 정규화의 주된 원칙은 불필요하게 중복된 데이터를 제거하는 것이며 테이블이나 필드를 세분화하는 방식으로 구현된다. 중복이 제거되면 저장 공간이 절약될 뿐만 아니라 논리성이 증대되므로 전체적인 성능도 향상되고 관리 편의성도 좋아진다.

똑똑한 학자들이 오랜 기간에 걸쳐 다양한 모델링을 해 보고 숱한 시행 착오를 겪으면서 도출해낸 모델링 공식이 바로 정규화이다. 무조건적으로 따라야 하는 것은 아니지만 모델링의 가장 기초적인 이론이므로 일단은 배우고 따르는 것이 좋다. 정제된 이론이므로 정규화는 모델링의 감을 잡는데 큰 도움이 된다. 1~6까지 번호가 붙어 있고 이 외에도 몇 가지 정규화 이론들이 있는데 실무에서는 1~3까지의 정규화만 주로 사용된다.

제 1 정규화는 필드에 저장되는 데이터를 원자화(Atomicity)하는 것이다. 즉, 한 필드에는 오직 하나의 값만 들어가야 하며 필드끼리는 독립적이어야 한다. 여러 개의 값이 하나의 필드에 통합되어 있거나 한 테이블에 유사 필드가 여러 개 있을 경우 이 필드를 아예 다른 테이블로 나누어야 한다. 간단한 직원 정보 테이블을 가정해 보자. 편의상 동명이인은 없다고 가정하고 직원 이름을 기본키로 지정했다. 그리고 이름, 월급, 맡은 프로젝트 등의 정보를 저장하려고 한다. 테이블은 다음과 같이 디자인될 것이다.

이름	월급	주소	프로젝트
김상형	150	춘천시	홍콩 수출건, TV광고건, 매출 분석건
김기문	140	서울시	경영 혁신안 작성, 대리점 계획
김수동	145	삼척시	노조 협상건

한 직원의 월급이 두 건이 되거나 한 직원이 두 군데서 살지는 않으므로 월급이나 주소 필드는 하나만 있으면 되고 두 필드는 아무런 연관성이 없다. 이 필드들은 원자성을 확보한 것이다. 하지만 맡은 프로젝트는 회사 사정에 따라 한 사람이 여러 가지 일을 동시에 진행할 수도 있고 아예 프로젝트를 맡지 않는 직원도 있을 수 있다. 그래서 프로젝트 필드에 여러 가지 프로젝트를 콤마로 구분하여 나열했다. 이렇게 되면 정보를 저장하는 목적은 달성되지만 원자성이 없으므로 여러 가지 문제들이 나타난다.

우선 프로젝트 개수에 대한 제한이 없어 이 필드에 충분한 길이를 할당해야 하므로 저장 공간이 낭비된다. 또 여러 개의 값이 통합되어 있으므로 검색하기도 무척 어렵다. 필드에 저장된 값이 단일 값이 아니므로 = 연산자로 바로 비교할 수 없으며 클라이언트측에서 필드값을 읽어 부분 문자열 검색을 직접 해야 한다. 그나마도 문자열 검색 알고리즘의 한계상 검색된 결과가 정확하다고 보장하기도 어렵다.

수정은 더 골때리는데 TV 광고건 프로젝트를 김수동 직원에게 옮긴다고 해 보자. 김상형 직원의 프로젝트 필드 중간에 끼어 있는 'TV 광고건'이라는 문자열과 콤마까지 빼내서 김수동 직원의 프로젝트 필드 뒤쪽에 끼워 넣어야 한다. 문자열 조작을 해야 하므로 UPDATE나 DELETE 같은 쿼리로는 불가능하고 결국은 클라이언트에게 부담을 줄 수밖에 없다. 이렇게 번거로운 이유는 필드가 원자성이 없기 때문이다. 그렇다면 다음 테이블은 어떨까?

이름	월급	주소	프로젝트1	프로젝트2	프로젝트3
김상형	150	춘천시	홍콩 수출건	TV광고건	매출 분석건
김기문	140	서울시	경영 혁신안 작성	대리점 계획	NULL
김수동	145	삼척시	노조 협상건	NULL	NULL

뭔가 달라진 것 같지만 이 구조도 별반 다를 바가 없다. 필드당 하나의 값만 들어가기는 하지만 오히려 직원 한명당 프로젝트를 세 개까지밖에 담당하지 못한다는 새로운 제약이 생겨 버렸다. 그렇다고 프로젝트4, 프로젝트5,... 프로젝트10 식으로 필드를 무한정 늘릴 수도 없다. 그래봐야 제한이 있는 것은 여전하고 공간 낭비만 더 심해진다. 하나의 업무만을 맡은 직원의 경우 9개의 필드가 NULL로 텅 비게 될 것이다.

문제의 핵심은 필드의 개수가 적다는 것이 아니라 개수가 고정되어 있다는 것이다. 기억 장소의 낭비없이 얼마든지 많은 프로젝트를 저장할 수 있어야 한다. 이런 경우의 정석이 바로 제 1 정규화이며 다음과 같이 두 개의 테이블로 분할하면 된다. 직원 테이블에는 이름, 월급, 주소 등의 직원 정보만을 두고 프로젝트 필드는 제거하였다. 그리고 프로젝트 테이블을 따로 만들어 누가 어떤 프로젝트를 담당했는지를 기록한다.

이렇게 하면 한 직원이 얼마든지 많은 프로젝트를 담당하더라도 두 테이블에 나누어 저장할 수 있으며 기억 장소의 낭비도 거의 없다. 신규 프로젝트가 시작되면 프로젝트 테이블에 레코드를 삽입하면 되고 완료되었으면 삭제하면 된다. 직원간에 프로젝트를 옮길 때도 담당자 필드만 수정하면 될 것이다. 필드가 원자성을 확보했으므로 UPDATE, DELETE 같은 쿼리문을 사용할 수 있다.

두 테이블은 각각의 기본키를 가지며 프로젝트 테이블은 담당자라는 외래키를 가진다. 이 외래키를 따라가면 프로젝트를 담당한 직원을 찾을 수 있고 이 직원에 대한 더 상세한 정보도 구할 수 있다. 특정 프로젝트를 누가 담당하고 있는지를 알고 싶다면 외래키가 참조하는 레코드를 읽으면 된다. 예를 들어 5번 프로젝트는 외래키를 통해 김기문 직원이 담당하고 있음을 알 수 있고 더불어 담당자의 월급이나 주소까지도 알 수 있다. 반대로 특정 직원이 맡고 있는 프로젝트를 알고 싶으면 프로젝트 테이블에서 외래키로 검색하면 된다. 예를 들어 ‘김상형’ 직원이 맡은 프로젝트를 알고 싶으면 프로젝트 테이블에서 담당자가 '김상형'인 레코드들을 조사하면 된다.

외부 테이블에 대한 참조를 가지므로 외래키라고 부르는 것이다. 이제 직원 테이블과 프로젝트 테이블간에는 "담당한다"는 관계가 성립하며 두 테이블은 담당자라는 외래키로 관련을 맺고 있다. 만약 담당자라는 외래키가 없다면 직원과 프로젝트는 완전히 따로 놀며 누가 어떤 프로젝트를 맡았는지 알 방법이 없어진다. 외래키라는 연결 고리를 통해 직원이 맡은 프로젝트를 찾거나 프로젝의 담당자를 찾을 수 있다.

두 테이블이 관계를 맺으면 부모, 자식 관계가 되는데 누가 업무의 중심인가에 따라 부모, 자식이 결정된다. 업무의 주체가 되는 쪽을 부모 또는 마스터(Master)라고 하며 반대쪽을 자식 또는 디테일(Detail)이라고 한다. 이 경우 직원이 프로젝트를 수행하는 것이므로 직원 테이블이 부모가 되고 프로젝트 테이블이 자식이 된다.

정규화에 의해 테이블이 분할되면 두 테이블에서 원하는 정보를 얻는 방법이 조금 번거로와 보인다. 하지만 걱정할 필요는 없다. 분할된 테이블에서 관련 정보를 검색하는 조인(Join)이라는 훌륭한 방법이 제공되며 다음 장에서 배울 것이다.

나.제 2 정규화

제 2 정규화는 기본키가 아닌 모든 필드는 기본키에 종속되도록 하는 것이다. 여기서 종속된다는 표현은 기본키를 알면 나머지 정보도 알 수 있다는 뜻이다. 익숙한 tblCountry 테이블에서 종속성의 의미를 관찰해 보자. 기본키인 국가의 이름과 나머지 수도, 대륙 등의 필드는 종속 관계인데 이름이 대한민국이면 수도는 당연히 서울이고 대한민국은 의심할 여지없이 아시아에 있다.

그러나 대륙과 수도, 면적, 인구 등의 일반 필드끼리는 종속 관계가 없으며 상호 독립적이다. 아시아에 있다고 해서 수도가 서울이어야 한다는 법은 없고 면적을 안다고 해서 인구를 알 수는 없다. tblCountry는 모든 필드가 기본키에 종속적이므로 제 2 정규화를 만족하는 것이다.

제 2 정규화는 기본키에 따라 표현하고자 하는 엔터티의 인스턴스가 결정되고 나머지 필드들은 이 엔터티의 추가 속성들을 표현하도록 하는 것이다. 직원 이름에 대해 월급과 주소가 종속적이며 프로젝트 번호에 따라 담당자와 프로젝트 이름이 종속적이다. 그러나 만약 프로젝트 테이블을 다음과 같이 만들었다면 어떨까?

프로젝트 번호	프로젝트	담당자	담당자 주소	월급
1	홍콩 수출건	김상형	춘천시	150
2	TV광고건	김상형	춘천시	150
3	매출 분석건	김상형	춘천시	150
4	경영 혁신안 작성	김기문	서울시	140
5	대리점 계획	김기문	서울시	140
6	노조 협상건	김수동	삼척시	145

프로젝트 번호는 프로젝트를 대표하므로 이 둘은 종속 관계라고 할 수 있다. 그러나 담당자의 주소나 월급은 프로젝트 자체와는 아무런 상관이 없다. 프로젝트 테이블은 누가 어떤 프로젝트를 맡았는가를 저장하면 그만이지 프로젝트 담당자가 어디에 사는가를 저장하는 것은 아니다. 담당자의 주소나 월급은 담당자를 먼저 조사하고 직원 테이블을 참조하면 언제든지 조사할 수 있으므로 이 필드들은 삭제해야 한다.

위 예는 어처구니 없어 보이고 이런 실수를 할 리가 없어 보이겠지만 복합키를 가지는 테이블에서는 이런 구조가 종종 만들어지기도 한다. 기본키가 복합키라면 모든 필드는 복합키 전체에 종속되어야 한다. 복합키의 일부에만 종속적인 필드가 있다면 별도의 테이블로 분리해야 한다. 말이 좀 어려운데 실례를 들어 보도록 하자. 다음 테이블은 주문 처리 내역을 기록하는 주문 테이블이다.

고객명	주문번호	주문일자	품목	개수	주소
김상형	1	5월 5일	하드 디스크	1	춘천
김상형	2	5월 6일	노트북	2	춘천
박미영	1	5월 7일	팥빙수	1	사직동
박미영	2	5월 8일	카네이션	2	사직동
박미영	3	5월 15일	수박	5	사직동
김상형	3	5월 28일	분유	1	춘천

고객 한명이 여러 건의 주문을 낼 수 있으므로 주문내역은 고객명과 주문번호가 복합키로 설정되어 있다. 주문 레코드는 어떤 고객이 몇번째 낸 주문이라는 식으로 구분된다. 이 테이블은 복합키 두개와 주문한 일자, 품목, 개수 그리고 고객의 주소로 구성되어 있는데 제대로 된 것 같지만 자세히 보면 중복되는 정보가 있다.

주문일자나 품목, 개수는 주문 내역인 고객명+주문번호에 완전히 종속된다. 즉 누가 몇번째 낸 주문인가에 따라 날짜, 품목, 개수가 서로 다르다. 물론 우연히 같은 값을 가질 수도 있지만 원칙적으로 이 필드들은 복합키에 종속되어 있다. 그러나 주소 필드는 복합키에 완전히 종속되어 있지 않고 고객명에만 종속되어 있으며 누가 주문을 냈는가에 따라 값이 달라진다. 주문번호에는 종속되지 않으므로 주문번호가 달라진다고 해서 주소가 달라지는 것은 아니다.

이렇게 되면 주소 필드는 테이블의 기억 장소를 불필요하게 낭비하는 꼴이 된다. 만약 '김상형' 고객이 100건의 주문을 냈다면 이 사람의 주소는 똑 같은 값이 100번이나 중복될 것이다. 김상형 고객의 주소인 '춘천'은 같을 수밖에 없는 정보이므로 여러 번 테이블에 기록할 필요가 전혀 없다. 기본키 전체에 종속되지 않는 필드는 별도의 테이블로 분리하여 제 2 정규화를 한다. 결과는 다음과 같다.

고객명	주문번호	주문일자	품목	개수
김상형	1	5월 5일	하드 디스크	1
김상형	2	5월 6일	노트북	2
박미영	1	5월 7일	팥빙수	1
박미영	2	5월 8일	카네이션	2
박미영	3	5월 15일	수박	5
김상형	3	5월 28일	분유	1

고객명	주소
김상형	춘천
박미영	사직동

주문 내역 테이블에는 복합키에 종속되는 필드만 포함시켰으며 고객에 대한 정보는 별도의 고객 테이블을 구성하였다. 이제 한 고객이 수천번의 주문을 낸다 하더라도 주소 정보는 딱 하나만 있으면 된다. 기억 장소가 절약됨은 물론이고 모든 면에서 훨씬 더 논리적이다.

다.제 3 정규화

제 3 정규화는 기본키가 아닌 일반 필드끼리는 서로 종속되지 않도록 하는 것이다. 일반 필드끼리는 독립적이어야 한다는 규칙이며 상호 종속적인 필드는 분리해야 한다. 역시 글만 읽어서는 잘 감이 오지 않는데 실례를 통해 직감적인 이해를 시도해 보자. 이번에는 각 직원이 가입한 사내 동호회 정보를 DB화하였다. 직원 테이블에 동호회에 관련된 필드들이 추가되어야 할 것이다. 동호회 이름과 방번호, 총 회원수 등을 필드로 추가해 보자.

직원명	월급	동호회	방번호	회원수
김상형	150	독서	203	32
김기문	140	독서	203	32
박대희	145	게임	306	18
박미영	200	독서	203	32
최봉우	180	게임	306	18

테이블의 모양을 보면 아직 이론은 잘 몰라도 뭔가 어색하다는 감이 올 것이다. 이 테이블의 기본키는 직원명이다. 월급이나 가입한 동호회 정보들은 기본키인 직원명에 종속적이다. 하지만 동호회의 방번호와 회원수는 기본키가 아닌 동호회 필드에 종속된다. 따라서 같은 동호회에 가입한 직원들끼리는 방번호, 회원수 등의 중복된 정보를 가지는 좋지 않은 구조가 된다. 정규화는 중복 정보를 없애는 것이 목적인데 왜 굳이 중복을 제거하려고 하는지 그 문제점을 연구해 보자.

① 기억 장소가 낭비된다. 예제라 간단하게 필드를 구성해서 위 테이블에서는 낭비되는 필드가 눈에 띄게 많지 않아 보이지만 만약 동호회 회장 이름, 설립년도, 예산 등등의 정보들이 추가되고 동호회별로 회원수가 수십명이라면 낭비가 엄청나게 심해질 것이다.

② 이 테이블의 엔터티는 직원이지 동호회가 아니다. 전체 직원 목록이 테이블에 저장되지만 모든 직원들이 다 동호회에 강제로 가입해야하는 것은 아니다. 동호회에 가입하지 않은 사람은 동호회, 방번호, 회원수 필드가 모두 NULL로 비어 있어 역시 공간이 낭비된다. 뿐만 아니라 동호회 엔터티가 직원 테이블에 셋방 살이를 하다 보니 가입 회원이 전혀 없는 신규 동호회는 표현할 방법이 없다.

③ 중복된 정보는 변경하기 번거롭다. 예를 들어 독서 동호회의 방번호가 203호에서 505호로 바뀌었다고 해 보자. 203호라는 방 번호가 여러 번 중복되어 있기 때문에 이 동호회에 속한 모든 회원의 정보를 다 수정해야 한다. 중복이 없다면 딱 하나의 필드만 수정하면 되므로 훨씬 더 쉬울 것이다.

④ 중복으로 인한 가장 심각한 문제는 정보의 불일치가 발생할 수 있다는 점이다. 같은 독서 동호회인데도 '김상형' 직원의 총 회원수는 32로 기록되고 '김기문' 직원의 총 회원수는 31로 기록될 수도 있다. 실수건 고의건 또는 에러에 의한 사고건 동일한 정보가 여러 군데 있다 보니 이런 불일치가 발생할 여지가 항상 있는 것이다. 불일치는 DB의 무결성을 헤치는 치명적인 문제이다.

이런 여러 가지 골치아픈 문제가 발생하는 이유는 분석 단계의 엔터티 추출에서부터 문제가 있었기 때문이다. 따라서 모델링도 잘못될 수밖에 없다. 문제를 해결하려면 정규화를 통해 테이블을 분할해야 한다. 이 테이블을 구성하는 각 필드끼리의 관계를 분석해 보자. 아래 그림에서 실선은 종속 관계이고 점선은 독립 관계이다.

월급은 각 직원마다 다르므로 월급은 기본키인 직원에 대해 종속적이다. 또한 각 직원들은 개별적으로 동호회를 선택하므로 직원과 동호회도 종속적이다. 하지만 일반 필드인 월급과 동호회와는 아무런 상관이 없으며 독립적이다. 월급이 얼마인가를 안다고 해서 이 직원의 동호회를 알아낼 수는 없다. 따라서 이 둘의 관계는 정규화를 위반하지 않았다.

하지만 동호회와 방번호, 회원수의 관계는 종속적이다. 동호회를 알면 방번호나 회원수도 알 수 있다. 이들은 모두 일반키이므로 독립적이어야 하는데 그렇지가 못하기 때문에 중복이 발생하는 것이다. 이럴 경우 직원 테이블과 동호회 테이블을 따로 분리하여 중복을 제거한다.

직원명	월급	동호회
김상형	150	독서
김기문	140	독서
한성안	145	게임
박미영	200	독서
최봉우	180	게임

동호회	방번호	회원수
독서	203	32
게임	306	18

직원 테이블에는 소속 동호회의 이름만 기록해 두고 각 동호회에 대한 정보는 별도의 테이블로 분리하였다. 직원 테이블에 남은 월급과 동호회는 기본키인 직원명에 종속적이다. 직원에 대한 정보를 얻기 위해 꼭 필요한 필드만 남았다. 새로 분할된 동회회 테이블에서 방번호와 회원수는 기본키인 동호회에 종속적이다. 따라서 둘 다 3 정규화가 완료된 것이다.

이제 중복으로 인한 모든 문제들이 깔끔하게 해결된다. 동호회의 방이 바뀌거나 회원수가 증감해도 직원 테이블은 건드릴 필요없이 동호회 테이블의 정보만 수정하면 되고 동호회 관련 정보가 딱 한번만 나타나므로 데이터의 불일치성이 발생할 여지도 없다. 가입 회원이 전혀 없는 신규 동호회도 표현할 수 있다. 물론 중복성이 제거되었으므로 기억 장소도 절약된다. 직원 테이블의 동호회 필드는 동호회 테이블을 참조하는 외래키라는 것을 알겠는가?

라.역정규화

정규화란 중복을 제거함으로써 기억 장소를 절약하고 데이터의 일치성을 확보하며 업데이트가 간편해지는 효과가 있다. 테이블의 구조가 훨씬 더 논리적으로 치밀해지므로 여러 가지 이점이 있다. 그러나 안타깝게도 정규화에 의해 두 테이블에 걸쳐 있는 정보를 읽는 속도는 조금 느려질 수 있다. 프로그래밍의 대원칙 중 하나는 속도와 크기는 항상 반비례한다는 것이다. 중복을 제거하여 용량상의 이득을 취했으면 속도상의 손해를 어느 정도는 감수해야 한다.

정규화는 주로 테이블을 분할하는 방식으로 이루어지는데 분할된 테이블에서 정보를 얻으려면 외래키를 구하고 외래키를 참조하여 다른 테이블을 다시 읽어야 한다. 아무래도 데이터를 읽는 속도가 느려질 수밖에 없다. 다행히 SQL은 이런 과정을 한방에 처리하는 조인(Join)이라는 효율적인 방법을 제공하므로 속도 감소의 정도는 심하지 않다. 약간의 속도 감소보다는 구조적인 깔끔함을 취하는 것이 일반적으로 훨씬 이득이며 그래서 정규화를 한다.

그러나 예외도 있다. 정규화를 너무 과하게 하다 보면 테이블이 여러 개로 쪼개져 이중 삼중의 조인을 해야만 원하는 정보를 구할 수 있게 되며 이때는 결코 무시할 수 없는 속도상의 손실이 발생한다. 심할 경우 5중, 6중 조인을 해야하는 경우도 있다. 이럴 때는 의도적으로 데이터를 중복시킴으로써 속도 향상을 꾀할 수 있는데 이를 역정규화라고 한다. 역정규화는 정규화의 완전히 반대되는 개념으로써 모델링 단계에서보다는 구현 단계의 튜닝 과정에서 주로 수행된다.

제 2 정규화에서 예를 든 주문 정보 테이블을 보자. 이 테이블에서 특정 품목에 대한 배송주소를 구하려면 먼저 주문자가 누구인지를 주문 내역 테이블에서 구하고 다시 고객 테이블에서 주문자의 주소를 조사하는 번거로운 과정을 거쳐야 한다. 또는 이를 한번에 구하기 위해 조인을 쓸 수도 있는데 이 방법도 테이블에서 정보를 바로 구하는 것보다는 역시 느리다. 이럴 때 주소 필드를 주문 내역 테이블에 다시 포함시키면 곧바로 배송지 주소를 구할 수 있다. 기억장소가 낭비되고 관리가 조금 번거로운 면이 있지만 분명히 속도는 향상된다.

특정 필드에 대해 정규화를 할 것인가, 역정규화를 할 것인가는 충분한 테스트를 한 후 결정해야 하는데 만약 아주 빈번히 읽어야 하고 속도가 지극히 중요하다면 역정규화를 해야 하고 그렇지 않다면 정규화해야 한다. 논리적으로는 정규화를 하는 것이 더 깔끔하지만 필요할 경우 비정규화를 할 수도 있다. 앞에서 예로 든 주문 정보 테이블의 경우는 역정규화를 할만한 정도는 아니다.