정규화

HELLO_DINO·2022년 9월 15일

SQL전문가가이드

목록 보기

6/28

제 1절 정규화

제1정규형 : 도메인은 원자값.
제2정규형 : 부분 함수적 종속을 제거.
제3정규형 : 이행적 함수 종속 제거.
BCNF : 결정자이면서 후보키가 아닌 것 제거.
제4정규형 : 다치 종속 제거.
제5정규형 : 조인 종속성 이용.

제1정규형

모든 속성은 반드시 하나의 값을 가져야 한다.

다음과 같이 연락처 속성에 다중값이 들어간 경우를 생각해보자.

연락처 정보에서 집전화와 핸드폰 번호를 구별하기가 어렵다.

A 고객은 집전화가 여러 대고, B고객은 핸드폰이 여러 대라면 혼재된 속성에서 원하는 속성 값을 추출하기 어렵다.

명확하지 않은 속성은 이메일처럼 다른 유형의 데이터를 포함할 수 있어 본연의 의미가 퇴색될 수 있다.

이와 같이 데이터를 관리하는 문제점에는 개발의 복잡성은 증가, 연락처의 속성은 의미 퇴색, 장기적으로 불안정한 데이터 구조를 양산, 개발의 오류 및 데이터 품질 문제까지 야기 가능하다는 문제가 있다.

위의 모델은 고객연락처라는 엔터티를 추가해 다중 값에 대한 문제점을 해결하였다.

다중 값을 제거함으로써 속성을 더 명확하게 활용 가능하다. 이는 개발의 복잡성을 감소 시킬 수 있다고 할 수 있다.

제1정규형은 다중 값 말고도 다른 유형의 중복 데이터도 의미할 수 있다.

본 주문 모델을 보고 우려되는 점을 생각해보자.

상품을 3개 이상 주문할 수 없다.

상품1, 상품2 모두 빠르게 조회하고 싶다면 속성마다 인덱스를 추가해야 한다.

상품명들을 빠르게 조회하기 위해 상품번호들 속성 모두에 인덱스를 추가해야한다. 이는 조회 속도는 빠를 수 있으나, 입력 수정 삭제 속도는 느려진다.

위와 그림처럼 주문상세 엔터티를 추가하면 상품을 몇 개나 주문하던 아무런 제약을 받지 않는다. 또한 추가적인 인덱스도 필요 없다.

제2정규형

엔터티의 일반속성은 주식별자 전체에 종속적이어야 한다.

위의 모델을 다시 보자.
'상품명' 속성이 주식별자가 아닌 오직 상품번호에 대해서만 반복되어 쌓이게 되는 구조다.

위의 표를 확인해 보면 'SQL 전문가 가이드'라는 데이터가 반복된다.
중복되는 데이터는 상품명 외 상품번호도 존재한다. 하지만 상품번호는 고객이 상품을 주문함으로써 발생하는 매핑 정보로서 의미를 가지고 있다. 주문번호와 함께 주문상세 엔터티의 식별자 의미를 가져 중복된 데이터로 볼 수 없지만. 상품명은 주문번호와는 관계없이 오직 상품번호에 의해서만 결정된다. 이를 '종속적이다'라고 한다.

정리하면 상품명은 주문상세의 식별자인 '주문번호 + 상품번호'가 아닌 오직 상품번호에만 종속적이다.
이를 함수적 종속성으로 표기하면 아래와 같다.

함수종속성 : 데이터들이 어떤 기준값에 의해 종속되는 현상
기준값을 결정자라 하고, 종속되는 값을 종속자라고 한다. 상품명은 상품번호에 종속되어 있기에 종속자이며, 상품번호는 상품명을 결정하기에 결정자이다.

위의 그림에서 주문상세 엔터티의 상품명은 식별자 전체가 아닌 일부에만 종속적이다. 이를 부분 종속이라 한다. '엔터티의 일반속성은 주식별자 전체에 종속적이어야 한다'는 제2정규형을 위배한 것이다.

위의 데이터 문제점

상품명이 변경되고 업무적으로 반영해주어야 한다면, 주문상세의 중복된 상품명을 모두 변경해야 한다. 이때 많이 팔린 상품일수록 주무낭세에서 변경해야 할 상품명의 부하도 크게 증가한다.

주문상세의 상품명을 변경한다고 해도 특정 시점에는 아직 변경되지 않은 상품명이 존재하고, 이때 들어온 트랜잭션은 일관되지 않는 데이터를 조회하게 된다.

결국 데이터 중복은 성능과 정합성에 문제를 발생시킨다.

상품 엔터티를 추가해 주문상세 엔터티의 부분 종속성을 제거 가능하다. 상품명 속성을 상품 엔터티에서 관리하고 상품번호를 매핑키로 활용하여, 상품명을 확인하는 구조로 데이터를 일원화해 관리함으로써 위에서 제시한 문제점을 해결할 수 있다. 이로써 '일반속성은 주식별자 전체에 종속해야 한다'는 제2정규형을 만족하게 된다. 데이터로 이를 확인하면 다음 표와 같다.

기존 주문상세 엔터티에서 상품엔터티를 분리하여 상품정보를 관리하도록 하였다. 데이터를 관리하면 주문상세 엔터티에서는 상품번호만 들고 있고, 상품번호를 매핑키로 상품 엔터티에서 원하는 상품정보 데이터를 가져올 수 있다. 이를 조인이라고 한다. 또한 상품명이 변경되었다면 상품 엔터티에서 데이터를 일원화해 관리하고 있어 중복 데이터에 대한 문제점도 해결할 수 있다.

제3정규형

엔터티의 일반속성 간에는 서로 종속적이지 않는다.
위의 모델을 살펴보면 고객번호는 주문번호에 종속적이고, 고객명은 고객번호에 종속적이다. 이는 '고객명이 주문번호에 종속적'임을 의미한다. 이것을 이행적 종속이라 하고, 이행적 종속을 배제하는 것을 제3정규형이라고 한다. 본 속성들의 함수적 종속성을 표기하면 아래 그림과 같다.

고객번호와 고객명 모두 주문번호에 종속하여 제2정규형은 만족하나, 고객명이 식별자가 아닌 일반속성에 종속적인 제3정규형 위배에 해당한다.

해당 모델의 문제점은

만일 고객이 이름을 바꿔 고객명이 변경되었다면, 주문 엔터티에 고객명을 전부 갱신해야 한다.
이는 주문과는 전혀 연관 없는 트랜잭션이다.

데이터 중복으로 인해 발생하는 문제는 성능 부하 및 정합성 오류로 제 2차정규형과 동일하다.

고객명이 변경 되면 주문 엔터티에서 그 값을 찾아 변경해야 한다.
만약 그 고객이 주문한 내역이 많다면 성능 부하와 특정 시점에 발생하는 정합성 문제를 내재하고 있는 것이다.

고객명 변경으로 인해 발생되는 트랜잭션은 주문과는 전혀 상관없는 트랜잭션이다. 즉 주문 엔터티가 받을 이유가 없다.

고객 속성 변경이 주문 엔터티에 영향을 주지 않으며, 데이터 중복 문제도 개선하였다.
주문 엔터티의 고객번호가 Null 허용인 것이 의아하다면 비회원 주문이 가능한 구조라고 이해하면 된다.

정규화 작업은 선택이 아닌 필수다. 또한 필수적이지만 무조건적이지는 않다. 상황에 따라 반정규화를 진행할 수도 있다.

반정규화와 성능

반정규화는 성능을 위해 데이터 중복을 허용하는 것이다.
반정규화는 조회성능을 향상시킬 수 있으나 입력 수정 삭제 성능은 저하될 수 있다.

가. 반정규화를 적용한 모델에서 성능이 향상될 수 있는 경우
아래 그림은 주문과 결제에 대한 모델이다.

위의 모델에서 고객의 편의를 위해 주문서 작성 시 최근 결제 정보를 미리 세팅해 보여주고 싶다.
최근 신용카드 정보를 미리 세팅하는 요건일 경우 다음과 같이 SQL을 작성한다.

이 구문에서는 1234 고객의 주문 내역이 많을수록 성능이 나빠진다. 최종결과는 1건이지만 주문내역이 많을 수록 해당 주문테이블과 결제테이블의 조인 건수가 증가해, 조인된 결제정보를 모두 읽고 내림차순으로 정렬해 최근 1건만 가져온다.

위의 모델은 결제 엔터티에 고객번호 속성을 반정규화한 것이다. 수정된 SQL은 다음과 같다.

결제 테이블에 '고객번호 + 결제수단구분코드 + 결제일시'로 인덱스를 생성하고 이를 통해 빠르게 결제수단번호를 가져올 수 있다.

나. 반정규화를 적용한 모델에서 성능이 저하될 수 있는 경우

위의 모델을 업무적으로 보면 고객이 주문한 후에 판매자가 배송한다.

내가 주문한 상품이 어디쯤 배송되었는지 조회할 수 있는 화면을 생각해보자.
주문정보는 주문 엔터티에서 가져올 수 있고, 송장번호는 배송 엔터티에서 가져올 수 있다. 즉 주문과 배송 엔터티를 함께 조인해야 한다. 하지만 성능을 위해 주문 엔터티에 송장번호를 반규화하였다.