🌠1-2-1. 정규화

과목1. 데이터 모델링의 이해

제2장 데이터 모델과 SQL

제1절 정규화

데이터 모델링에서 정규화(Normalization)는 가장 기초적이지만 필수적으로 이뤄져야 하는 작업이다. 성능을위해 반정규화를 하기도 하지만, 그 이전에 정규화가 왜 필요한지를 반드시 알아야 한다. 다음 몇 가지 사례를통해 정규화가 무엇인지와 그 필요성을 알아보자.

1. 제1정규형 : 모든 속성은 반드시 하나의 값을 가져야 한다

[그림 I-2-1] 연락처 정보를 포함하는 고객 모델

1정규형은 하나의 속성에는 하나의 값을 가져야 하는 것이다. [그림 I-2-1] 모델에서 연락처 속성에 다중값(multivalued)이 들어가는 경우를 생각해보자.

[표 I-2-1] 고객연락처 데이터

고객번호	고객명	연락처
10000	정우진	02-123-4567,010-1234-5678
10001	한형식	010-5678-2345
10002	황영은	02-345-3456,010-4567-7890

[표 I-2-1]과 같이 데이터가 생성된다면 어떤 문제가 발생할 수 있는지 생각해 보자.

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

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

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

이와 같이 데이터를 관리한다면 개발의 복잡성은 증가할 것이고, 연락처의 속성은 그 의미가 점차 퇴색될 것이다. 이는 장기적으로 불안정한 데이터 구조를 양산할 것이다. 개발의 오류 및 데이터 품질 문제까지 야기할 수 있다. 그렇다면 [표 I-2-1]과 같은 데이터에서는 어떻게 모델을 설계해야 할까?

[그림 I-2-2] 고객연락처 엔터티를 추가한 고객 모델

[그림 I-2-2] 의 모델을 보면 고객연락처라는 엔터티를 추가하여 다중 값에 대한 문제점을 해결하였다. 본 모델의 데이터를 표현하면 [표 I-2-2]와 같다.

[표 I-2-2] 고객과 고객연락처 데이터

고객번호	고객명
10000	정우진
10001	한형식
10002	황영은

[고객]

고객번호	순번	연락처
10000	1	02-123-4567
10000	2	010-1234-5678
10001	1	010-5678-2345
10002	1	02-345-3456
10002	2	010-4567-7890

[고객연락처]

[표 I-2-2] 데이터를 보면 고객의 연락처가 많아져도 아무런 문제가 되지 않는다. 집전화 번호 또는 핸드폰 번호를 구분하고 싶다면, 고객연락처 엔터티에 '연락처구분코드' 속성을 추가하면 된다. '연락처구분코드' 속성을 추가한다면, 이메일 등의 연락처 정보도 수용 가능하다. 이처럼 다중 값을 제거함으로써 속성을 더 명확하게 활용할수 있다. 이는 곧 개발의 복잡성을 감소시킬 수 있다고 할 수 있다.

제1정규형은 다중 값 말고도 다른 유형의 중복 데이터도 의미할 수 있다. 중복 데이터를 속성으로 분리하면 어떨까?

[그림 I-2-3] 반복되는 속성을 가진 모델

[그림 1-2-3]은 주문 엔터티로 주문이 발생했을 때의 정보를 관리한다. 본 주문 모델을 보고 우려되는 점을 생각해보자.

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

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

[그림 1-2-3] 모델에서는 상품을 2개까지만 주문 할 수 있다. 즉 3개 이상의 상품을 주문할 수 없다. 만일 3개이상의 상품을 주문하고 싶다면 본 모델에서는 '상품번호3, 상품명3 … 상품번호, 상품명N'의 속성을 매번 추가해야할 것이다. 속성을 추가한다는 것은 테이블의 칼럼을 추가하는 것으로 대부분의 DBMS에서 테이블 Lock을발생시키고, 환경에 따라서는 사이트 중지가 필요할수도 있는 작업이다.

요즘처럼 365일 24시간 서비스를 지속해야 하는 환경에서 모델을 변경하는 작업은 굉장한 제약이 아닐 수 없다.보통 이런 작업은 정해진 PM(Prevention Maintenance) 시간에 진행하게 된다. 예를 들면 한 달 혹은 일주일에한 번 트랜잭션(Transaction)이 적은 새벽 시간대에 진행한다. 간혹 새벽에 쇼핑몰 등의 사이트에 접속해보면'전산 작업중'이라는 메시지와 함께 접근이 불가했던 경험을 떠올려 보자.

또한 상품명1, 상품명2 를 빠르게 조회하기 위해서는 상품번호1, 상품번호2 속성 모두에 인덱스를 추가해야한다. 인덱스를 추가한다는 것은 조회(SELECT) 속도는 빨라질 수 있으나, 입력·수정·삭제 속도는 느려진다는 것을고려해야 한다.

[그림 I-2-4] 주문상세 엔터티를 추가한 모델

그럼 어떻게 설계해야 할까? [그림 I-2-4]와 같이 주문상세 엔터티를 추가하면 된다. 본 모델에서는 상품을몇 개를 주문하던 아무런 제약을 받지 않는다. 또한 추가적인 인덱스도 필요 없다. 이와 같은 설계는 단순히 데이터모델적 설계를 떠나 안정적인 서비스에 기여할 수 있다.

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

[그림 1-24]의 모델을 다시 한번 살펴보자. 주문상세 모델을 보면서 이상한 점을 발견했을 수도 있을 것이다.‘상품명 속성이 주식별자가 아닌 오직 상품번호에 대해서만 반복되어 쌓이게 되는 구조라는 점이다. 다음 [표 I-2-3]을보고 확인해보자.

[표 I-2-3] 주문상세 데이터

주문번호	상품번호	상품명
1100001	256	SQL 전문가 가이드
1100002	257	데이터아키텍처 전문가 가이드
1100003	256	SQL 전문가 가이드
1100004	256	SQL 전문가 가이드
1100005	258	데이터 분석 전문가 가이드

[표 I-2-3]에서 데이터를 확인해보면 'SQL 전문가 가이드'라는 데이터가 반복되는 것을 볼 수 있다. 본 표에서 중복되는 데이터는 상품명 외 상품번호도 존재한다. 하지만 상품번호는 고객이 상품을 주문함으로써 발생하는 매핑 정보로서 의미를 가지고 있다. 주문번호와 함께 주문상세 엔터티의 식별자 의미를 가지고 있기에 중복된 데이터라고 볼 수 없다. 하지만 상품명은 주문번호와는 관계없이 오직 상품번호에 의해서만 결정된다. 이러한 것을 우리는 ‘종속적이다'라고 한다. 정리하면 상품명은 주문상세의 식별자인 주문번호+상품번호'가 아닌 오직 상품번호에만 종속적이다. 이를 함수적 종속성으로 표기하면 [그림 I-2-5]와 같다.

[그림 I-2-5] 함수의 종속성

함수종속성(Functional Dependency)은 데이터들이 어떤 기준값에 의해 종속되는 현상을 지칭한다. 이때 기준값을 결정자(Determinant)라 하고, 종속되는 값을 종속자(Dependent)라고 한다. 상품명은 상품번호에 종속되어 있기에 종속자이며, 상품번호는 상품명을 결정하기에 결정자이다. [그림 I-2-5]에서 주문상세 엔터티의 상품명은 식별자 전체가 아닌 일부에만 종속적이다. 이를 부분 종속(Partial Dependency)이라 한다. 엔터티의 일반속성은 주식별자 전체에 종속적이어야 한다'는 제2정규형을 위배한 것이다. 이러한 데이터는 어떤 문제점을 가질까?

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

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

결국 데이터 중복은 성능과 정합성에 문제를 발생시킨다. 그렇다면 이를 개선하기 위해서는 어떻게 설계해야할까?

[그림 I-2-6] 상품 엔터티를 추가한 모델

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

[표 I-2-4] 상품과 주문상세 데이터

상품번호	상품명
256	SQL 전문가 가이드
257	데이터아키텍처 전문가 가이드
258	데이터 분석 전문가 가이드

[상품]

주문번호	상품번호
1100001	256
1100002	257
1100003	256
1100004	256
1100005	258

[주문상세]

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

3. 제3정규형 : 엔터티의 일반속성 간에는 서로 종속적이지 않는다

[그림 I-2-6] 모델의 주문엔터티를 살펴보자. 고객번호는 주문번호에 종속적이고, 고객명은 고객번호에 종속적이다. 이는 ‘고객명이 주문번호에 종속적'임을 의미한다. 이것을 이행적 종속(Transitive Dependency)이라 하고,이행적 종속을 배제하는 것을 제3정규형이라고 한다. 본 속성들의 함수적 종속성을 표기하면 [그림 I-2-7]과 같다.

주문번호 → 고객번호 이고 고객번호 → 고객명 이면 주문번호 → 고객명 이다.

[그림 I-2-7] 이행 종속성

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

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

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

고객명 '정세준'에서 '정우진'으로 변경되었다면 주문 엔터티의 '정세준'이라는 고객명을 찾아 '정우진'으로 변경해주어야 한다. 이때 '정세준 고객이 주문한 내역이 많다면 성능 부하와 특정 시점에 발생하는 정합성 문제를 내재하고있는 것이다. 중요한 것은 고객명 변경으로 인해 발생되는 트랜잭션은 주문과는 전혀 상관없는 트랜잭션이다.즉 주문과 관계없는 트랜잭션을 주문 엔터티가 받을 이유는 없다. 혹 고객명이 변경되는 일은 흔하지 않다라고생각한다면, 고객명이 아니라 고객주소라고 생각해보자. 고객주소는 고객명보다는 더 자주 변경될 수 있다. 고객주소도흔하지 않은 경우라면, 고객별명은 어떠한가? 별명은 언제든지 하루에도 수십번씩 변경될 수 있다. 본 모델은 초심자도이해하기 쉽도록 최대한 명확한 케이스로 설명하겠다.

그렇다면 어떻게 설계해야 할까? 지금까지 패턴을 익혔다면 고객 엔터티를 분리하여 관리해야 한다.

[그림 I-2-8] 고객 엔터티를 추가한 모델

[그림 I-2-8] 모델의 고객 엔터티를 보면 고객 속성 변경이 주문 엔터티에 영향을 주지 않는 구조다. 또한 데이터 중복에 대한 문제도 개선되었다고 볼 수 있다. 혹시 주문 엔터티에 고객번호가 Null 허용인 것을 의아하게 생각한다면, 비회원도 주문이 가능한 구조라고 이해하면 된다.

데이터 모델에 익숙하다면 애초에 상품 엔터티와 고객 엔터티를 분리해야 한다고 생각했을 것이다. 왜 그렇게 생각을 했을까? 본 모델들은 이해를 돕기 위해 누구나 알 수 있는 쉬운 모델로 설명했다. 만일 비즈니즈 도메인이 높은 모델이었어도 엔터티를 분리할 생각을 쉽게 할 수 있을까? 이것을 위해 정규화를 배우는 것이다. 엔터티를 분리해야 하는 기준을 알고 있다면, 더 쉽게 데이터 모델링을 할 수 있다. 개인의 경험치에 기반한 직감적 모델링이 아닌, 근거가 명확한 기준에 의한 모델링은 더 나은 데이터 설계를 가능하게 해준다. 따라서 정규화 작업은 선택이 아닌 반드시 해야 하는 필수다.

또한 정규화는 필수적이지만 무조건적이지는 않다. 상황에 따라서는 반정규화를 진행할 수도 있다. 중요한 것은 기본적으로 정규화를 진행하고 반정규화를 고려해야 한다. 이로써 무분별한 반정규화를 방지하고 무심코 놓칠 수 있는 부분도 챙길 수 있다.

4. 반정규화와 성능

반정규화는 정규화를 반대로 하는 것으로 역정규화라고도 한다. 정규화는 데이터의 중복을 최소화했다면, 반정규화는 성능을 위해 데이터 중복을 허용하는 것이다. 그러므로 성능이 문제될 때 주로 반정규화에 대해 논의하게 된다.

하지만 반정규화가 항상 성능을 향상시킬까? 조회성능을 향상시킬 수 있을지 모르겠으나 그로인한 입력 수정 삭제 성능은 저하될 수 있다. 이 부분을 염두에 두고 반정규화해야 할 것이다. 다음은 사례를 들어 정규화와 반정규화가 성능에 미치는 영향을 살펴본다.

가. 반정규화를 적용한 모델에서 성능이 향상될 수 있는 경우

[그림 I-2-9]는 주문과 결제에 대한 모델이다. 본 모델에서 생소한 속성만 설명하면, 주문 엔터티에서 주문상태 코드는 주문 상태에 대한 코드값으로 '주문 취소·반품·교환' 등의 정보를 관리하고, 결제일시 속성은 실제 결제를 진행한 일시정보를 관리한다. 결제 엔터티에서 결제수단구분코드 속성은 '카드결제 계좌이체 핸드폰결제' 등을 관리하는 코드값이다. 결제수단번호 속성은 결제수단구분코드에서 사용한 실제 카드번호·계좌번호 핸드폰번호' 등을 관리하는 속성값이다.

[그림 I-2-9] 정규화 모델 성능 저하

본 모델에서 다음과 같은 요건을 생각해보자. 고객의 편의를 위해 주문서 작성 시 최근 결제 정보를 미리 세팅하여 보여주고 싶다. 실제로 쇼핑몰에 들어가 보면, 최근 사용한 결제 정보가 자동으로 세팅되는 곳이 많다. 이는 고객 경험을 위해 많이 하는 방법이다. 최근 신용카드 정보를 미리 세팅하는 요건일 경우 다음과 같은 SQL을 작성하게 된다.

SELECT A.결제수단번호
  FROM ( SELECT B.결제수단번호
           FROM 주문 A, 결제 B
		  WHERE A.주문번호 = B.주문번호
		    AND A. 고객번호 = 1234
			AND B. 결제수단구분코드 = '신용카드'
		  ORDER BY B.결제일시 DESC
		) A 
 WHERE ROWNUM = 1;

본 SQL문은 고객번호가 1234인 고객의 주문정보를 결제 테이블과 조인으로 가져온 후, 신용카드 결제 정보를 결제일시로 내림차순 정렬해 최근 1건의 결제수단번호를 가져오는 SQL이다. 이와 같은 SQL에서는 어떤 성능 문제가 있을까?

1234 고객의 주문 내역이 많을수록 성능이 나빠지는 문제가 존재한다. 최종결과는 1건을 가져오지만, 주문내역이 많을수록 해당 주문테이블과 결제테이블의 조인 건수가 증가하게 되며, 조인된 결제정보를 모두 읽고 내림차순 정렬하여 최근 1건의 데이터를 가져온다. 즉 주문내역이 많을 수록 조인에 대한 부하가 증가하여 성능이 나빠지는 구조다.

그렇다면 어떻게 개선할 수 있을까? 결제 엔터티에 고객번호 속성을 반정규화함으로써 조인에 대한 성능 부하를 개선할 수 있다.

[그림 I-2-10] 반정규화 모델 성능개선

[그림 I-2-10] 모델은 결제 엔터티에 고객번호 속성을 반정규화하였다. 수정된 SQL은 다음과 같다.

SELECT A.결제수단번호
  FROM ( SELECT A.결제수단번호
           FROM 결제 A
		  WHERE A. 고객번호 = 1234
		    AND A. 결제수단구분코드 = '신용카드’
	      ORDER BY A. 결제일시 DESC
		) A
 WHERE ROWNUM = 1; 

결제 테이블에 ‘고객번호+결제수단구분코드+결제일시’로 인덱스를 생성하고 'Index Range Scan Descending'으로 최종 1건의 데이터만 읽어 결제수단번호를 가져올 수 있다. 최적의 SQL로 성능 부하를 극적으로 개선할 수 있다.

이처럼 정규화가 항상 정답인 것만은 아니다. 요건에 따라서는 반정규화를 진행할 수도 있다. 하지만 반정규화를 남용한다면, 자칫 더 큰 문제를 야기할 수 있다. 기본적으로 정규화를 고려하고 반정규화가 꼭 필요한 대상인지를 검증하고, 다른 방법은 없는지를 검토한 후 반정규화를 적용해야 한다. 즉 반정규화는 꼭 필요할 때에만 적용해야한다.

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

반정규화는 항상 빠른 성능을 보장할까? 실무에서 자주 듣는 소리 중 하나는 ‘조인을 하면 성능이 느려진다' 라는말이다. 한 개의 테이블을 읽어서 데이터를 가져오는 것이 두 개의 테이블을 조인하여 데이터를 가져오는 것보다조금이라도 빠르기는 할 것이므로 이런 관점에서 본다면 일리가 있다.

[그림 I-2-11] 정규화한 주문과 배송 모델

하지만 과연 장점만 있는 것일까? 단점은 없는 걸까? [그림 I-2-11]은 주문과 배송에 대한 모델이다. 업무적으로생각해보면, 고객이 주문하면 이후 판매자가 배송을 한다. 대부분의 쇼핑몰은 고객이 주문한 주문내역에 대해 배송정보를 조회할 수 있는 기능을 갖추고 있다. 즉 현재 내가 주문한 상품이 어디쯤 배송되었는지를 조회할 수 있는화면이다.

이런 화면은 어떻게 구현할 수 있을까? 우선은 고객이 주문한 주문정보가 필요할 것이고, 주문한 상품의 송장번호가필요하다. 송장번호는 판매자가 고객의 상품을 배송하기 위해 택배사로부터 전달받은 번호다. 흔히 택배박스 스티커에서 확인할 수 있다. 그럼 해당 쇼핑몰에서는 송장번호를 택배회사에 전달하여 현재 어디쯤 배달됐는지에 대한정보를 요청하고, 수신된 데이터를 고객에게 보여준다.

위와 같은 요건을 개발한다고 생각해보자. 주문정보는 주문 엔터티에서 가져올 수 있고, 송장번호는 배송 엔터티에서가져올 수 있다. 즉 주문과 배송 엔터티를 함께 조인해야 한다. 하지만 성능을 위해 주문 엔터티에 송장번호를반정규화하였다.

[그림 I-2-12] 반정규화한 주문 모델

[그림 I-2-12]의 주문 엔터티에 송장번호를 반정규화하면, 배송 엔터티와 조인을 하지 않아도 된다. 조인을 제거하였기에 더 빠른 성능을 확보할 수 있다.

하지만 반정규화가 과연 장점만 존재할까? 다시 업무 프로세스를 생각해보자. 고객이 주문하면 판매자가 배송을 진행한다고 앞서 설명하였다. 이 말은 고객이 주문하는 시점에는 송장번호를 알 수가 없다는 뜻이다. 고객이 주문을 하였다고 해서 판매자가 바로 배송하지는 않는다. 재고 소진으로 판매자 취소가 발생할 수도 있다. 무엇보다 송장번호는 택배사에게 부여받는 번호이기 때문이다. 고객의 주문이 완료되면, 판매자는 보통 하루에 2~3번 정도 자신에게 들어온 주문을 확인한다. 상품페이지에서 'xx시까지 주문한 상품은 당일 발송을 진행합니다'라는 문구를 떠올리면 된다. 판매자가 주문정보를 보고 상품을 포장하여 택배 박스에 담아야 비로소 송장번호를 받을 준비가 끝난다. 즉 주문과 동시에 송장번호는 알 수 없다. 그렇다면 [그림 I-2-12] 주문 모델의 송장번호는 주문 시점에는 NULL 데이터가 들어가며, 배송준비가 완료되어야 송장번호를 갱신(UPDATE)할 수 있게 된다.

반정규화를 하기 전에는 없었던 갱신(UPDATE) 로직이 새로 추가되었다. 그럼 이런 고민이 필요하다. 조회(SELECT) 성능 향상을 위해 불필요한 갱신(UPDATE) 로직을 추가해야 할까? 정규화한 그림 I-2-11] 모델에 적절한 인덱스가 구성되었다고 하면, 반정규화한 [그림 I-2-12] 모델이 가지는 이점은 사실 굉장히 미미할 것이다. 이러한 이점을 위해 불필요한 갱신(UPDATE) 로직을 취해야 할까? 일반적인 상황이라면 '배보다 배꼽이 더 큰 경우가 대부분일 것이다. 특히 요즘처럼 AWS와 같은 클라우드 환경에서 운영되는 시스템이라면 이런 불필요한 로직으로 인해 과금이 늘어나게 된다는 것도 유의해야 할 것이다.

그래서 반정규화는 꼭 필요할 때 적용해야 한다. 반정규화는 데이터 불일치로 인한 정합성 문제뿐 아니라, 불필요한 트랜잭션으로 인한 성능 문제를 만들어내기 때문이다. 조회 성능에서 미미한 이점을 취하고, 불필요한 강신으로 인해 또 다른 성능을 손해본다면 이는 합리적인 판단이라고는 할 수 없다. 반정규화는 '그럼에도 진행해야 할 만한 근거가 뒷받침될 때 비로소 진행해야 한다.

출처 : 데이터온에어 – 한국데이터산업진흥원(https://dataonair.or.kr)