으랏차

데이터 모델 정규화/반정규화의 실전 프로젝트 적용

정규화를 잘 이해하여 데이터 모델링을 해야 하는 프로젝트 모델러가 이를 정확하게 이해하지 못하는 경우가 종종 있다. 검증되어 있고 체계화된 이론적 기반 위에 데이터베이스라는 기초를 건축하지 않으면 그 데이터베이스는 모래 위에 세운 집처럼 금방 무너지고 말 것이다. 정규화의 이론은 건축물의 기초공사를 해야 하는 사상에 해당한다. 그저 어렴풋이, 알듯 모를듯 희미한 기억의 지식으로 튼튼하고 견고한 데이터 모델을 만들어 낼 수 없다.

“붕어빵에 붕어가 없다!”고 한다. 데이터 모델링을 학교나 학원에서 배운 사람이나 시스템 구축 프로젝트에서 데이터 모델링을 경험한 사람치고 정규화에 대한 이야기를 듣거나 이야기하지 않은 사람은 없을 것이다. 그만큼 정규화의 이론은 데이터를 분석하여 데이터 모델로 만들고 그것을 다시 데이터베이스화하는 이론의 뿌리가 되는 중요한 것이다. 그러나 붕어빵에 붕어가 없듯이 정규화에 대한 언급은 누구나 하지만 정규화에 대한 내용을 정확하게 이해하고 실전에 적용할 수 있는 사람은 의외로 극히 드물다는 사실을 즉시해야 하고 사태의 심각성을 인식할 필요가 있다.

정규화에 관련된 이론을 배운다고 하면 대부분 과목, 수강신청, 교수 등 항상 정해진 샘플 사례에 표시 방법도 과목코드->과목명과 같이 설명되어 실전 프로젝트에서 사용하는 표기법(notation)과 동 떨어져 있다. 따라서 학습할 때는 개념적으로 이해한다고 하더라도 혹은 적어도 시험문제가 출제되면 100점은 맞는다고 하더라도, 실전 프로젝트에서는 무엇을 어떻게 왜 그렇게 해야 하는지 도무지 이해하지 못하는 경우가 대부분이다. 잘 정리된 이론은 실세계에서 응용되어 다른 창조물을 도출할 수 있을 때 비로소 지식가치의 효용이 있다. 하지만 불행히도 정규화의 이론은 그 내용이 너무 훌륭함에도 불구하고 실전에 반영하는 방법을 정확하게 알지 못해 그의 가치를 제대로 적용하지 못하는 경우가 자주 나타나는 것이다. 이제 우리의 눈을 희미하게 하는 이론의 틀을 깨고 개념을 명확하게 하여 실전에서 곧 바로 적용할 수 있는 참된 지식가치로 정규화의 이론을 활용해 보자.

정규화 규칙은 어디에 쓰는 물건인가?

“그런데 실전 프로젝트에서는 정규화를 적용한 적이 없습니다!”라고 반문하는 독자도 있을 것이다. 맞는 이야기이다. 프로젝트에서는 정규화라고 하는 태스크(task)로 일을 진행하지는 않는다. 다만, 프로젝트에서 데이터 모델링을 할 때는 논리 모델->물리 모델 2단계로 수행하거나 개념 모델->논리 모델->물리 모델 3단계 또는 객체지향 분석설계에서는 클래스 다이어그램->OR(객체-관계형) 맵핑->물리 모델로 하거나 업무가 익숙하고 시스템의 규모가 작은 경우 곧 바로 물리적인 데이터 모델링을 하는 경우로 진행한다. “그렇다면 데이터베이스에서 그렇게 중요하다고 하는 정규화 방법은 활용되지 않는가?”라고 반문할 수 있다.

정규화 규칙은 실제 프로젝트에서 두 가지 성격으로 중요하게 반영이 된다. 

첫 번째는 엔티티 타입을 오브젝트 분석 방법에 의해 도출할지라도 분석 방법의 배경에는 이미 중복 제거 및 주식별자에 의한 종속과 속성에 의한 종속 등 제3정규화 규칙이 모델링 작업의 기초에 관여한다고 봐도 된다. 즉 숙련된 데이터 모델러는 이미 정규화에 대한 개념이 확보된 상태에서 각각의 오브젝트를 엔티티 타입으로 선정하며 새로운 엔티티 타입으로 분리될 때도 각 속성의 집합 개념과 종속성의 개념을 적용하여 분리시켜 나간다.

두 번째는 정규화 방법을 프로젝트에서 적절하게 활용하기 위해서는 오브젝트별로 엔티티 타입을 분석해가면서 각각의 오브젝트가 적절하게 도출이 되었는지 또는 더 분리되어야 해야 하는지를 정규화 규칙에 대입하며 검증하는 것이다. 또한 단계별로 작업이 수행된 이후에 정규화 규칙에 의해 모든 엔티티 타입에 대해서 검증하는 작업이 필요하고 이상이 있는 경우에는 정규화 규칙을 적용하여 엔티티 타입을 정제해 나가도록 한다.

정규화의 의미

그러면, 데이터 모델링에서 정규화는 무엇을 의미하는가? 1970년 6월 E.F Code 박사는 ‘대규모 데이터 저장을 위한 관계형 데이터 모델(A Relational Model of Data for Large Shared Databanks)’이라는 연구에서 새로운 관계형 모델을 발표했다. 수학자인 Code 박사에 의해 제안된 정규화의 이론은 실세계에서 발생하는 데이터들을 수학적인 방법에 의해 구조화시켜 체계적으로 데이터를 관리할 수 있도록 하였다. 처음에는 1차 정규화, 2차 정규화, 3차 정규화가 제시되었으나 이후에 보이스-코드 정규화가 제시되었고, 이후 4차 정규화, 5차 정규화의 이론이 발표되었다.

정규화(normalization)란 다양한 유형의 데이터 값 검사를 통해 데이터 모델을 더 구조화시키고 개선시켜 나가는 절차에 관련된 이론이다. 정규화가 프로세스를 나타내는 의미라면 정규형(normalform)은 정규화가 완성된 이후의 엔티티 타입(테이블)을 지칭하는 용어이다. 정규화를 이해하기 위해서는 이론적인 기반이 되는 함수 종속성을 이해할 필요가 있다. 함수의 종속성(functional dependency)은 데이터들이 어떤 기준 값에 의해 종속되는 현상을 지칭하는 것이다. 이 때 기준 값을 결정자(determinant)라 하고 종속되는 값을 종속자/의존자(dependent)라고 한다.

<그림 1> 함수의 종속성

<그림 1>을 보면 사람이라는 엔티티 타입에는 주민등록번호, 이름, 출생지, 호주라는 속성이 존재한다. 여기에서 이름, 출생지, 호주라는 속성은 주민등록번호 속성에 종속된다. 만약 어떤 사람의 주민등록번호가 신고되면 그 사람의 이름, 출생지, 호주가 생성되어 단지 하나의 값만을 가지게 된다. 이를 기호로 표시하면 다음과 같다.

주민등록번호 -> (이름, 출생지, 호주)

즉 ‘주민등록번호가 이름, 출생지, 호주를 함수적으로 결정한다’라고 말할 수 있다. 실세계의 데이터들은 대부분 이러한 함수 종속성을 가지고 있다. 함수의 종속성은 데이터가 가지고 있는 근본적인 속성으로 인식되고 있다. 정규화의 궁극적인 목적은 반복적인 데이터를 분리하고 각 데이터가 종속된 테이블에 적절하게(프로세스에 의해 데이터의 정합성이 지켜질 수 있어야 함) 배치되도록 하는 것이므로 이 함수의 종속성을 이용하여 정규화 작업이나 각 오브젝트에 속성을 배치하는 작업을 한다.

• 정규화는 적절한 엔티티 타입에 각각의 속성들을 배치하고 엔티티 타입을 충분히 도출해가는 단계적인 분석 방법이다.

• 정규화 기술은 엔티티 타입에 속성들이 상호 종속적인 관계를 갖는 것을 배경으로 종속 관계를 이용하여 엔티티 타입을 정제하는 방법이다.

• 각각의 속성들이 데이터 모델에 포함될 수 있는 정규화의 원리를 이용하여 데이터를 분석하는 방법에서 활용될 수 있다.

• 정규화는 현재 데이터를 검증할 수 있고 엔티티 타입을 데이터가 표현하는 관점에서 정의하는데 이용할 수 있다.

• 정규화는 엔티티 타입을 분석하는 관점이 오브젝트별 분석하는 방법이 아닌 개별 데이터를 이용한 수학적인 접근방법을 통해 분석하는 방법이다.

정규화에 대한 실전 프로젝트 적용 사례

<표 1>은 1차 정규화, 2차 정규화, 3차 정규화와 보이스-코드정규화 그리고 4차와 5차 정규화에 대한 정리이다. 정규화의 정의를 이용하여 실전 프로젝트에서는 어떻게 적용할 수 있는지 살펴보자.

1차 정규화(복수의 속성 값을 갖는 속성의 분리)

1차 정규화(first normalization)는 복수의 속성 값을 가진 속성을 분리한다. 즉 테이블 하나의 컬럼에는 여러 개의 데이터 값이 중복되어 나타나지 않아야 한다는 것이다. 이는 각 속성에 값이 반복 집단이 없는 원자 값(atomic value)으로만 구성되어 있어야 한다는 것을 의미한다. 이를 다시 정의하면, “모든 엔티티 타입의 속성에는 하나의 속성 값만을 가지고 있어야 하며 반복되는 속성 값의 집단은 별도의 엔티티 타입으로 분리한다”로 정의할 수 있다. 이 때 전제조건은 결정자에 의존하는 의존자의 반복성을 나타낸다. 실전 프로젝트에서 나타나는 데이터 모델의 표기법을 이용한 사례를 보도록 하자.

1차 정규화 사례 1
‘한 번의 주문에 여러 개의 제품을 주문한다’는 업무 규칙이 있는데 <그림 2>의 왼쪽 편과 같이 데이터 모델링을 했다고 가정해 보자. 왼쪽의 엔티티 타입은 하나의 주문에 여러 개의 제품이 존재하므로 주문번호, 주문일자, 배송요청일자의 동일한 속성 값이 주문한 제품의 수만큼 반복해서 저장될 것이다. 따라서 오른쪽과 같이 1차 정규화를 적용하여 중복속성 값을 제거한다.

<그림 2> 1차 정규화의 응용 1

이 사례의 특징은 주문의 PK(Primary Key)인 주문번호가 중복 속성 값을 가지기 때문에 PK를 가진 데이터베이스 테이블 생성이 불가능하다는 특징이 있다.

1차 정규화 사례 2
로우(Row) 단위로 1차 정규화가 안 된 모델은 PK의 유일성이 확보되지 않으므로 인해 실전 프로젝트에서는 거의 찾아보기가 힘들다. 반면 로우 단위로 중복된 내용을 컬럼 단위로 펼쳐 중복하는 경우가 아주 많이 발견된다. 1차 정규화의 응용이 된 형태로 볼 수 있다. 계층형 데이터베이스에서 이와 같은 형식의 모델링을 많이 했는데 관계형 데이터베이스에서도 이러한 형식으로 모델링을 진행하는 경우가 많이 발견된다.

<그림 3> 1차 정규화의 응용 2

<그림 3>의 모델을 보면 왼쪽 모델의 일재고 엔티티 타입에는 3개월 분에 대한 장기재고 수량, 주문수량, 금액, 주문금액이 차례대로 기술되어 있다. 이렇게 되면 장기재고 관리가 4개월 이상으로 늘어날 때 모델을 변경해야 하는 치명적이 결함이 있다. 따라서 오른쪽과 같이 1차 정규화를 통해 모델을 분리함으로써 업무 변형에 따른 데이터 모델의 확장성을 확보하도록 해야 한다.

2차 정규화(주식별자에 종속적이지 않은 속성의 분리)

1차 정규화를 진행했지만 속성 중에 주식별자에 종속적이지 않고 주식별자를 구성하는 속성의 일부에 종속적인 속성인, 부분종속 속성(PARTIAL DEPENDENCY ATTRIBUTE) 을 분리하는 것이 2차 정규화(SECOND NORMALIZATION)이다. 2차 정규화는 반드시 자신의 테이블을 주식별자를 구성하는 속성이 복합 식별자일 경우에만 대상이 되고 단일 식별자일 경우에는 2차 정규화 대상이 아니다.

2차 정규화 사례
여러 개의 속성이 주식별자로 구성되어 있을 때 일반속성 중에서 주식별자에 일부에만 종속적인 속성이 있을 경우 2차 정규화를 적용하여 엔티티 타입을 분리하도록 한다.

<그림 4> 2차 정규화 응용

<그림 4>의 모델은 고객번호에 종속적이지 않은 속성들을 분리하여 고객점포라는 새로운 엔티티 타입을 생성하였다. 실전 프로젝트에서는 코드 유형의 엔티티 타입들이 2차 정규화가 되지 않고 하나의 엔티티 타입으로 표현되는 경우가 많이 발견된다. 이 모델에서 함수종속 관계 표기법으로 표기하자면 고객번호 -> (고객명)으로 표시하여 별도의 엔티티 타입으로 분리할 수 있다.

3차 정규화(속성에 종속적인 속성 분리)

3차 정규화(third normalization)는 속성에 종속적인 속성을 분리하는 것이다. 즉 1차 정규화나 2차 정규화를 통해 분리된 테이블에서 속성 중 주식별자에 의해 종속적인 속성 중에서 다시 속성 간에 종속 관계가 발생되는 경우에 3차 정규화를 진행한다. 3차 정규화의 대상이 되는 속성들을 이전 종속(transitive dependence) 관계 속성이라고 한다. 이것은 곧 주식별자에 의해 종속적인 속성 중에서 다시 다른 속성을 결정하는 결정자가 존재하여 다른 속성이 이 결정자 속성에 종속적인 관계를 나타낸다.

3차 정규화 실전 적용
결정자 역할을 하는 일반 속성이 존재하고, 결정자 역할 속성에 의존하는 의존자가 존재하는 엔티티 타입은 3차 정규화의 대상이 된다.

<그림 5> 3차 정규화 응용

<그림 5>의 모델은 고객 엔티티 타입에 등록카드에 대한 정보가 포함되어 있는 모습이다. 등록카드번호가 결정자 역할을 하고 있고 등록카드사명과 등록카드유효일자가 의존자 역할을 하는 속성 간의 종속적인 속성이 발견되었으므로 3차 정규화의 대상이 되는 모델이다. 따라서 등록카드에 대한 내용에 대해 별도의 엔티티 타입을 도출한 오른쪽 모델로 만듦으로서 3차 정규화를 완성하였다. 실전 프로젝트에서는 1:1관계의 엔티티 타입이 하나로 통합이 되었거나 업무분석 과정에서 하나의 엔티티 타입에 많은 속성이 포함되어 있을 때 3차 정규화의 대상이 되는 경우가 많이 나타난다. 이 모델에서 함수종속 관계 표기법으로 표기하자면 등록카드번호 -> (등록카드사명, 등록카드유효일자)으로 표시하여 별도의 엔티티 타입으로 분리할 수 있다.

보이스-코드 정규화

1차 정규화, 2차 정규화, 3차 정규화는 모두 하나의 주식별자를 가졌을 때를 가정하여 진행하였다. 만약 하나의 테이블에 여러 개의 식별자가 존재하면 비록 1, 2, 3 정규형을 모두 만족하더라도 데이터를 조작하는 데 문제가 발생될 수 있다. 복잡한 식별자 관계에 의해 발생되는 문제를 해결하기 위해 3차 정규화를 보완한 보이스-코드 정규화(boyce-code normalization)를 진행한다.
보이스-코드 정규화란 테이블에 존재하는 식별자가 여러 개 존재할 경우 식별자가 중복되어 나타나는 현상을 제거하기 위해 정규화 작업을 진행한다.

BCNF 실전 적용
납품 엔티티 타입의 주식별자는 부품번호, 부품이름, 납품번호 세 개의 속성의 구성이 되어 있고 세 개의 속성을 구성한 주식별자는 납품수량, 납품단가에 대해 결정자 역할을 한다. 그런데 부품번호+납품번호 만으로도 납품수량, 납품단가에 대해 결정자 역할을 할 수도 있고 부품이름+납품번호 만으로도 납품수량, 납품단가에 대해 결정자 역할을 할 수도 있다. 또한 부품번호와 부품이름은 상호간에 결정자역할을 하는 특성을 가지고 있다. 이러한 성격을 이용하여 데이터 모델에서는 최소의 속성의 조합이 주식별자를 갖게 하도록 BCNF(Boyce Codd Normal Form)를 적용한다. 즉, 부품번호를 주식별자로 하여 하여 부품을 구성하거나 부품이름을 주식별자로 하여 부품 엔티티 타입을 분리하여 납품과 관계를 갖게 하는 형식으로 정규화를 진행하는 방식이 바로 보이스-코드 정규화 방법이 된다.

<그림 6> BCNF 정규화의 응용

개념적 설명은 무척 까다롭지만 실전 사례를 통해서는 쉽게 이해되는 부분이다. 다시 한 번 정리하면, 주식별자 속성 중에 주식별자의 유일성을 확보하는 최소한의 속성이 아닌 쓸데없이 추가된 속성을 분리하는 것이 보이스-코드 정규화라고 할 수 있다. 또한 주식별자 속성 중에 상호간의 함수종속 관계를 가지는 것을 분리한다. <그림 6>의 부품번호와 부품이름 사례처럼 단독으로 주식별자에 참여할 수 있으면서 상호간의 종속 관계가 있는 코드, 코드명을 생각하면 쉽게 이해될 수 있다. 주식별자 속성이 많아질수록 보이스-코드 정규화의 대상이 되는 경우가 나타나므로 개념을 잘 정리하여 실전에서 데이터 모델을 검증할 수 있도록 해야 한다.

4차 정규화(특정 속성 값에 따라 선택적인 속성의 분리)

보이스-코드 정규화까지 정규화 작업을 진행하면 함수의 종속성에 관한 작업은 모두 정리가 되었다. 이제 더 이상 속성 사이의 종속적인 관계로 인해 발생하는 정규화 작업은 필요하지 않게 되는 것이다. 그러나 하나의 테이블에 두 개 이상의 독립적인 다가속성(multi-valued attribute)이 존재하는 경우에 다가종속(multi-valued dependency)이 발생되어 문제가 생긴다. 다가종속이라는 단어를 해석하면, 하나의 속성 값에 두 개의 이상의 의미를 가지는 값을 가지는 것을 의미한다. 4차 정규화의 대상이 되는 경우는 실제 프로젝트에서는 독립적인 엔티티 타입을 설계할 때 발생하기 보다는 동시에 여러 개의 엔티티 타입과의 관계에서 발생되는 경우가 많이 있다.

4차 정규화의 실전 적용
<그림 7>과 같은 업무 규칙이 있다. ‘한 명의 사원은 여러 개의 프로젝트를 지원할 수 있다’ 그리고 ‘한 명의 사원은 여러 개의 기술을 보유할 수 있다’ 즉 사원과 프로젝트, 사원과 기술 간의 업무적인 관계의 규칙이 있는 경우이다. 이 업무 규칙은 보유하는 기술이 있다는 사실을 관리하고 보유한 기술은 지원한 프로젝트와는 아무런 상관이 없다는 것이 특징이다. 그럼에도 불구하고 <그림 7>의 왼쪽처럼 사원과 프로젝트와 기술 간의 관계를 모두 연결하면 4차 정규화의 규칙을 위배하여 어떤 사원이 새로운 기술을 습득하여 사원내역 엔티티 타입에 등록하려고 하면 마치 금방 습득한 기술을 가지고 어떤 프로젝트를 지원한 것처럼 값을 채워줘야만 하는 현상에 빠지게 된다. 따라서 필요하지 않은 조인 관계를 해소하기 위해 오른쪽 모델과 같이 업무 규칙에 적합하게 관계를 분리하는 방법이 4차 정규화이다.

<그림 7> 4차 정규화의 응용

4차 정규화가 실전 프로젝트에서 거의 나타나지 않는다고 하는 사람들이 많은데 필자가 파악하기로는 2차 정규화나 BCNF보다 더 많이 발생된다. 단, 4차 정규화를 하지 않고 개발을 하다가 새로운 값을 채울 경우에 값을 기본 값(default value)으로 지정해버리는 경우가 많이 있다. 참조무결성 제약조건(FK)를 데이터베이스 테이블에 걸지 않는 경우에 가능한데 구축단계 때 많은 프로젝트에서 이와 같은 편법으로 프로그램을 작성한다. 좋지 않은 경우이다. 이와 같은 경우 데이터모델에 나타난 관계가 실제 데이터에서 불가피하게 단절되어 나타나므로 무결성 체크가 불가능해진다. 설계단계 때 불필요한 관계에 의해 나타나는 4차 정규화의 대상 엔티티 타입을 검증하여 정규화를 적용하도록 해야 한다.

반정규화

논리적인 데이터 모델링 단계에서는 모든 엔티티 타입과 속성들을 정규화 규칙에 적절하게 분석하여 데이터 모델링을 수행한다. 이 단계는 실전 프로젝트에서는 분석단계 때 수행하는 경우가 많고 설계단계 때는 데이터베이스 성능을 고려하여 물리적인 데이터 모델링을 수행하는데 물리적인 데이터 모델링의 여러 개의 타스크 중에 반정규화를 수행하게 된다. 반정규화라고 하면, 일반적으로 다른 엔티티 타입에 있는 속성을 중복한 것만을 생각하는 경우가 많이 있다. 훨씬 많은 반정규화 유형이 있고 각각은 유용하게 활용될 수 있음을 알 수 있다.

반정규화란 정규화된 엔티티 타입, 속성, 관계에 대해 시스템의 성능향상과 개발(development)과 운영(maintenance)의 단순화를 위해 데이터모델을 조정하는 프로세스를 의미한다. 단순하게 정규화 규칙에 반대되는 개념으로만 생각한다면 속성의 중복 정도가 반정규화의 범위에 해당되지만 물리적인 성능을 고려한 반정규화의 개념으로 생각한다면 테이블 통합/분리, 속성 중복, 속성 추가, 관계 중복 등이 반정규화의 범위에 해당된다.

반정규화를 적용하기 전에 반드시 중요하게 고려해야 할 점은 데이터의 무결성을 유지시킬 수 있는 방안을 마련하고 반정규화를 적용해야 한다는 것이다. 시스템을 개발할 때는 성공적인 오픈을 위해 성능을 중요하게 여겨 여러 테이블에 속성들을 반정규화하는 경우가 많은데 반정규화를 많이 할수록 데이터의 무결성은 깨져 이상한 데이터가 많이 남아있거나 돈의 액수가 맞지 않거나 등록된 접수 건수가 맞지 않은 현상이 시스템을 운영하는 중에 점점 많이 발생하게 되어 나중에는 시스템을 사용하지 못하게 되는 경우가 발생된다. 데이터 무결성을 중요하게 생각하고 반정규화를 적용할 필요가 있다.

반정규화에 대한 실전 프로젝트 적용 사례

반정규화를 하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가하거나 분할할 수 있으며 제거할 수도 있다.

1차 정규화에 대한 반정규화

고객에 대한 엔티티 타입에 방문을 두 번까지 가능하다고 할 때 고객번호, 고객명이 중복 속성 값을 갖기 때문에 1차 정규화의 대상이 되어 중간에 있는 고객방문 엔티티 타입으로 1차 정규화가 되었다. 그러나 최대 2회까지 방문이 가능하다는 업무 규칙을 이용하여 성능과 단순성을 고려하여 오른쪽에 있는 1차 정규화에 대한 반정규화 엔티티 타입으로 설계된 예이다.

<그림 8> 1차 반정규화의 응용

최대 발생하는 값을 이용한 이와 같은 반정규화의 유형은 실전 프로젝트에서 빈번하게 사용되지만 최대 발생 값을 변할 수 있는 경우는 정규화된 모습으로 모델링해야 확장성(flexible)이 보장된다는 것을 기억해야 한다.

2차 정규화에 대한 반정규화

주식별자가 두 개 이상일 때 일부 주식별자 속성에 의존적인 속성을 분리하는 2차 정규화에서 조인에 의한 성능저하와 단순성 확보를 위해 반정규화를 적용할 수 있다.

<그림 9> 2차 반 정규화의 모델

<그림 9>의 모델은 일자별 매각 물건 엔티티 타입에서 매각 일자가 결정자가 되고 매각 장소와 매각 시간이 의존자가 된 함수 종속성이 존재하여 2차 정규화를 적용했다가 다시 조인에 의한 성능저하 예방과 단순성을 위해 다시 일자별매각물건이라는 엔티티 타입에 반정규화를 한 경우이다.

3차 정규화에 대한 반정규화

<그림 10>의 모델을 보면 수납이라고 하는 엔티티 타입은 속성간의 결정자(수납확인번호)와 의존자가 존재하는 3차 정규화의 대상이 되는 모습이다. 따라서 수납확인번호를 결정자로 하고 수납확인방법, 수납확인일자, 수납확인자사번을 속성으로 하는 3차 정규화를 적용하였다.

<그림 10> 3차 반정규화의 응용

반정규화를 하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가할 수도 있고 분할할 수도 있으며 제거할 수도 있다. 정규화에 위배되는 것은 아니지만 성능을 위해 적용하는 반정규화의 방법 테이블 통합/분리, 속성 중복, 관계 중복 등 여러 가지가 있을 수 있다.

이력의 최근 변경 속성 값 반정규화

<그림 11>의 모델은 공급자에 대한 전화번호, 메일주소, 위치 등에 대한 변경 정보를 각각 관리하는 현재 데이터와 이력 데이터에 대한 데이터 모델이다. 모든 속성 값이 중복이 없어 완벽히 정규화된 모습이지만 이력 모델이 정규화되어 있음으로 인해 최근 값을 처리하는 데 상당한 시간이 소요되고 SQL 구문도 복잡하게 된다. 따라서 데이터를 조회할 때는 프로세스의 대부분은 가장 최근 값을 참조한다는 성격을 이용하여 오른쪽과 같이 최근 값에 대한 속성 값만을 관리하기 위해 공급자 엔티티 타입에 전화번호, 메일주소, 위치에 대한 속성을 추가하였다.

<그림 11> 최근 변경 값 속성의 반정규화

<그림 11>에서 공급자번호 1001～1005에 해당하는 공급자번호, 공급자명, 전화번호, 메일주소, 위치에 대한 정보를 조회하면 다음과 같이 작성된다.

SELECT A.공급자명, B.전화번호, C.메일주소, D.위치 FROM 공급자 A, (SELECT X.공급자번호, X.전화번호 FROM 전화번호 X, (SELECT 공급자번호, MAX(순번) 순번 FROM 전화번호 WHERE 공급자번호 BETWEEN '1001' AND '1005' GROUP BY 공급자번호) Y WHERE X.공급자번호 = Y.공급자번호 … WHERE A.공급자번호 = B.공급자번호 AND A.공급자번호 = C.공급자번호 AND A.공급자번호 = D.공급자번호 AND A.공급자번호 BETWEEN '1001' AND '1005' SELECT 공급자명, 전화번호, 메일주소, 위치 FROM 공급자 WHERE 공급자번호 BETWEEN '1001' AND '1005' 정규화된 모델에서 SQL반정규화된 모델에서 SQL 반정규화

적절한 반정규화를 통해 성능도 훨씬 향상되었을 뿐만 아니라 SQL 구문도 비교가 안될 만큼 단순해졌음을 알 수 있다.

관계 반정규화

속성의 반정규화에서 데이터를 조회하는 경로를 단축하기 위해 일반속성(주식별자가 아닌)을 중복할 수도 있고 주식별자 속성을 중복할 수도 있다. 주식별자 속성의 중복 중 전체 주식별자를 이루는 전체 속성의 중복은 곧 관계의 중복을 의미한다. 관계의 반정규화는 인위적인 속성의 중복 없이 조회경로 단축을 통해 조인에 의한 데이터 처리 속도를 향상시키는 장점이 있다.

<그림 12>의 왼쪽은 고객, 주문, 주문목록, 배송 엔티티 타입이 정규화가 잘 되어 있고 관계도 업무 규칙에 따라 식별자 관계/비식별자 관계로 적절하게 설정되어 있다. 그런데 배송 엔티티 타입에 발생되는 프로세스가 데이터를 처리할 때 항상 고객에 있는 속성의 모든 정보를 참조해야 하는데 왼쪽 정규화된 모델에서는 항상 주문목록과 주문을 경유하여 고객정보를 처리함으로 조인에 의한 성능저하가 예상된다. 따라서 조회경로를 단축하기 위해 오른쪽과 같이 관계를 추가로 연결하여, 즉, 이미 고객->주문->주문목록->배송으로 관계는 연결되어 있지만 성능을 위해 고객->주문으로 직접 관계를 연결한 관계의 반정규화를 적용한 사례이다.

<그림 12> 관계의 반정규화

<그림 12>의 데이터 모델에 왼쪽에 있는 데이터 모델에 대해 배송일시와 고객번호, 고객명을 가져오는 SQL 문장을 작성하면 다음과 같이 작성될 수 있다.

SELECT D.고객번호, D.고객명, A.일시
  FROM 배송 A, 주문목록 B, 주문 C, 고객 D
  WHERE A.배송번호 = ‘20031001001’
  AND 배송.주문번호 = 주문목록.주문번호
  AND 배송.제품번호 = 주문목록.제품번호
  AND 주문.주문번호 = 배송.주문번호
  AND 고객.고객번호 = 주문.고객번호

간단한 고객에 관련된 정보를 읽어오는데 2개의 테이블을 필요하지 않게 읽은 경우이다. 오른쪽과 같이 관계가 중복된 경우는 배송일시와 고객번호, 고객명을 가져오기 위해 다음과 같이 SQL 문장을 구성할 수 있다.

SELECT B.고객번호, B.고객명, A.일시

FROM 배송 A, 고객 B 

WHERE A.배송번호 = ‘20031001001’ 

AND 배송.고객번호 = 주문.고객번호

2개의 테이블에 대해서만 접근을 하므로 관계가 중복되지 않은 경우보다 훨씬 쉽게 SQL 문장도 구성되며 성능도 더 낫다. 테이블의 관계가 5단계 6단계까지 내려가면서 중간에 비식별자관계로 연결되어 있고 빈번하게 조인이 되는 경우라면 관계의 중복을 고려할 수 있다. 프로젝트 상황에 따라 관계의 반정규화는 성능과 단순성에 있어 매우 유용하다.

호두과자에는 호두가 있다!

중부지방을 경유하는 기차 여행을 하면 자주 호두과자를 먹게 된다. 붕어 없는 붕어빵과는 다르게 호두과자에는 호두 알갱이가 있어 제법 고소한 맛이 난다. 이처럼 관공서, 학교, 기업 등에 구축하는 데이터베이스가 견실하기 위해서는 잘 정리된 정규화 사상이 녹아져 있어 정규화 사상 맛이 나는 데이터 모델이어야 한다. 그리고 거기에 체계화된 방법과 타당성 있는 반정규화를 적용한 데이터 모델을 만들어 내야 한다. 이 일은 그렇게 해도 되는 선택적인 사항이 아니라 한 번 구축하면 변경이 불가능하고 잘못된 데이터베이스는 시간에 따라 엄청난 문제와 제정을 낭비하기 때문에 그렇게 해야 하는 당위성을 가지고 있는 중요한 작업이다.

그러기 위해서는 데이터 모델링을 수행하는 사람은 정규화/반정규화에 대해 거울로 자기 얼굴을 보듯 정확한 이해와 체계적인 사고를 바탕으로 데이터 모델링을 할 수 있는 능력을 가져야 한다. 이 글을 읽는 독자는 이론을 위한 이론, 학교시험에서 점수 획득을 위한 지식의 단계를 뛰어넘어 실전에서 무한한 가치를 창조해 내는 진정한 지식가치의 이론을 겸비하여 최고의 데이터 모델링을 수행하는 전문가가 되기를 희망한다.