관계형 데이터베이스의 데이터 모델링 정규화와 비정규화

데이터 모델링은 관계형 데이터베이스의 기초 개념입니다. 제대로 모델링 과정을 거치지 않은 데이터베이스의 설계는 추후에 매우 많은 문제들을 일으키게 됩니다. 대부분의 데이터베이스 전문가들은 성능 향상(Performance Tuning)에 있어 중요한 요소로 데이터베이스 디자인을 말합니다. 처음에 설계를 잘 못하면 아무리 튜닝을 해도 한계에 부딪히게 됩니다.

과거의 전통적인 데이터베이스 개발에서는 전체 중 약 30% 정도의 시간을 데이터베이스 디자인에 할당했다면 관계형 데이터베이스에서는 전체의 개발 시간 중 약간 오바해서 표현하면 약 60~80%의 시간을 데이터베이스 디자인에 할당할 정도로 그 중요성이 큽니다.

 

데이터 모델링

데이터 모델링은 일반적으로 다음과 같은 세 가지 단계로 나눕니다. 다른 의견도 많지만 가장 일반적인 방법에 대해서 설명하도록 하겠습니다. 데이터 모델링을 하는 가장 큰 이유는 데이터의 중복성을 제거하는 일입니다.

실체 (Entity)

개념 : 하나로 묶을 수 있는 것의 실질적 집합을 말하며 이는 바로 테이블로 표현합니다.

예 : 고객 테이블, 주문 테이블, 상품코드 테이블

관계 (Relationship)

개념 : 실체에 대한 설명이나 추가적인 구분자로 기본 키 (Primary Key)와 외래 키 (Foreign Key)로 정의 됩니다. 외래 키는 참조 키와 같은말입니다.

예 : 고객과 주문의 관계

속성 (Attribute)

개념 : 실체나 관계에 대한 서술적 속성입니다. 컬럼이라고 표현하며, 이전의 DB에서는 필드입니다.

 

관계 (Relationship)에 대해...

이 설명의 타이틀에서 보듯이 관계형 데이터베이스에서 가장 중요한 관계에 대해서 잠깐 알아보고 넘어가도록 하겠습니다.

많은 경우 테이블 간의 관계는 "1 대 다"로 이루어 집니다. 예를 들면 고객 테이블의 1번 고객은 주문 테이블에서 많은 주문을 갖는 경우가 대부분입니다. 이런 식으로 테이블 간의 관계를 표현함에 있어서 나타날 수 있는 경우는 다음과 같이 3가지로 나타낼 수 있습니다.

일 대 일 (1 : 1) one to one

이 경우는 한 테이블을 원 상태 그대로 둘 또는 그 이상의 테이블로 나누어 놓은 경우 흔히 발생합니다. 만약, 불필요하게 이런 관계가 발생한다면 차라리 테이블을 다시 합치는 것도 고려해 볼 필요가 있습니다.

일 대 다 (1 : n) one to many

제일 흔한 경우로 부모 자식 관계라고도 합니다.

다 대 다 (n : n) many to many

흔치 않은 경우로 예를 들자면, 학생과 수업과목이란 관계를 생각해 볼수 있습니다. 한 학생이 많은 과목을 수강할 수 있을 뿐 아니라 한 과목의 입장에서도 많은 학생이 관계를 가지게 됩니다. 관계형 데이터베이스 (R-DB)에서는 일반적으로 인정되지 않는 관계로 피해야 합니다. 제 4정규화를 거쳐야 합니다.

 

제 1 정규화 (1NF)

규칙 : 여러값을 가진 컬럼이 존재할 수 없습니다. 즉 다시말해 반복되는 그룹이 존재해서는 안되며 각 행과 열에는 오직 한 값만이 존재해야 합니다.

고객번호        고객명        취미

    1              홍길동        영화

                                     여행

    2              마징가        등산

 

위의 예에서 1번 고객인 홍길동은 두가지 취미를 가지고 있습니다. 이렇게 그룹이 존재하면 제 1 정규화의 대상이 됩니다. 아래와 같이 수정할 수 있습니다.

고객번호        고객명

    1              홍길동

    2              마징가

 

고객번호        일련번호        취미

    1                   1             영화

    1                   2             여행

    2                   1             등산

 

제 2 정규화 (2NF)

규칙 : 모든 키가 아닌 컬럼은 기본 키 전체에 의존적이어야 합니다. 기본 키의 일부분에 의존적이어서는 안됩니다.

사번        프로젝트번호        부서        프로젝트역할        고과율

  1                   a                전산              팀장                 A   

  1                   b                전산              조원                 C   

  1                   c                전산              부팀장              B   

  2                   c                경리              팀장                 A   

  3                   c                기획              팀장                 A   

 

이 테이블에서 기본 키는 (사번+프로젝트번호)입니다. 그런데 부서 컬럼은 사번에 의존적입니다. 다시 말해 부서 컬럼은 (사번+프로젝트번호)전체에 의존적인 것이 아닙니다. 이런 컬럼이 존재한다면 제 2 정규화를 거쳐야 합니다.

사번        프로젝트번호        프로젝트역할        고과율

  1                   a                      팀장                 A   

  1                   b                      조원                 C   

  1                   c                     부팀장               B   

  2                   c                      팀장                 A   

  3                   c                      팀장                 A   

 

사번        부서

  1          전산

  2          경리

  3          기획

 

제 3 정규화 (3NF)

규칙 : 키가 아닌 컬럼은 다른 키가 아닌 컬럼에 의존적이어서는 안됩니다.

사번        프로젝트번호        프로젝트역할        고과율

  1                   a                      팀장                 A   

  1                   b                      조원                 C   

  1                   c                     부팀장               B   

  2                   c                      팀장                 A   

  3                   c                      팀장                 A   

 

앞서 제 2 정규화를 거친 테이블이지만 아직도 고과율 컬럼은 프로젝트역할에 따라 변하고 있음을 알 수 있습니다. 즉, 고과율은 키가 아닌 프로젝트역할 컬럼에 의존적입니다.

 사번        프로젝트번호        프로젝트역할

  1                   a                      팀장      

  1                   b                      조원      

  1                   c                     부팀장   

  2                   c                      팀장      

  3                   c                      팀장     

 

프로젝트역할        고과율

    팀장                   A   

    조원                   C   

    부팀장                B   

 

제 4 정규화 (4NF)

규칙 : 3NF 테이블은 의존적인 n : n (다 대 다)관계를 가질 수 없습니다.

학번        이름        과목번호        과목명

  1         홍길동           K               국어

  2         마징가           K               국어

  3         슈퍼맨           K               국어

  3         슈퍼맨           M              수학 

  3         슈퍼맨           E               영어

  2         마징가           H               역사

  2         마징가           P               정치

  1         홍길동           P               정치

 

이 테이블은 아래와 같은 두개의 테이블로부터 나온것입니다.

과목번호        과목명

     K               국어                      학번          이름

     M              수학    < 다 : 다 >     1          홍길동      

     E               영어                        2          마징가

     H               역사                        3          슈퍼맨

     P               정치

 

위의 테이블은 학생과 과목간의 관계가 다 : 다 관계입니다. 이를 해결하기 위해 아래와 같이 나눕니다.

                                        학번        일련번호        과목번호

과목번호        과목명             1              1                   K    

    K               국어               1              2                   P    

    M               수학               2             1                   H                 학번        이름 

    E               영어 <1 : n>  2              2                   P     <n : 1>  1         홍길동

    H               역사               2             3                   K                   2         마징가

    P               정치               3             1                   K                   3         슈퍼맨

                                          3             2                   M    

                                          3             3                   E    

 

비정규화 (Denomalization)

정규화를 하는 것은 되도록 중복된 데이터를 제거해서 성능 향상에 도움을 주는것에 목표를 두고 있습니다. 그러나 경우에 따라서는 나누어 놓은 테이블을 성능 향상의 목적으로 다시 합쳐야 할 경우가 생길 수도 있습니다. 나누어 놓은 테이블들을 함께 연결해서 사용하려면 JOIN이라는 방법을 사용하는데, 이릉 위해 어느정도의 부하가 걸리는것이 사실입니다. 따라서 지나치게 자주 JOIN을 사용한다면 차라리 테이블을 다시 합치는 것에 대해 고려해 보아야 합니다. 이렇게 성능향상을 위해 다시 테이블을 합치는 것을 비정규화라고 하며, 보통은 정규화 과정을 거친 후 마지막 단계에서 비정규화를 실시합니다. 물론 처음부터 비정규화를 할수도 있습니다.

꼭 테이블을 합치는 것만이 비정규화는 아닙니다. 아래와 같은 경우에도 비정규화에 해당됩니다.

 

1. 그룹에 대한 합계와 같은 값을 미리 계산하여 테이블에 저장해 둡니다. (일반적으로 트리거가 필요하게 됩니다.)

예) 판매 테이블에 새로운 데이터가 변경될 때 마다 제품 테이블의 총 판매 수량 컬럼이 변경됩니다.

 

2. 한 테이블에서 자주 사용되는 행(레코드)들과 그렇지 않은 행들을 분리하여 두개의 테이블로 둡니다. (이럴 때는 UNION으로 다시 연결 시킬 수 있습니다.)

예) 고객 테이블에 200만 건의 고객이 있습니다. 그중 약 10%에 해당하는 20만명 정도의 고객들만이 자주 거래를 하고 나머지 고객들은 1년이 넘게 거래가 없다고 하면, 이 두 부류를 두개의 테이블로 나누어 관리합니다.

 

3. 다른 테이블에 의존적이지만 자주 JOIN에서 사용되는 컬럼을 중복하여 테이블 안에 하나 더 만듭니다.