정규화 (Normalization)
- 정규화란?
: 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다
데이터 모델링에서 정규화는 가장 기초적이지만 필수적으로 이뤄져야 하는 작업이다. 성능을 통해 반정규화를 하기도 하지만, 그 이전에 정규화가 왜 필요한지를 반드시 알아야 한다.
몇가지 사례를 통해 정규화가 무엇인지와 그 필요성을 알아보자.
제 1정규형 : 모든 속성은 반드시 하나의 값을 가져야 한다.
제 1정규형은 하나의 속성에는 하나의 값을 가져야 하는 것이다. 위의 모델에서 연락처 속성에 다중값이 들어가는 경우를 생각해보자.
-- 고객연락처 데이터--
- 속성에 다중값이 들어있는 경우
| 고객번호 | 고객명 | 연락처 |
|---|---|---|
| 10000 | 정우진 | 02-123-4567, 010-1234-5678 ,070-1234-1234 |
| 10001 | 한형식 | 010-5678-2345 |
| 10002 | 황영은 | 02-345-3456, 010-4567-7890 |
- 반복되는 속성을 가질경우
| 고객번호 | 고객명 | 연락처1 | 연락처2 | 연락처3 |
|---|---|---|---|---|
| 10000 | 정우진 | 02-123-4567 | 010-1234-5678 | 070-1234-1234 |
| 10001 | 한형식 | 010-5678-2345 | ||
| 10002 | 황영은 | 02-345-3456 | 010-4567-7890 |
위와 같이 데이터가 생성된다면 어떤 문제가 발생할 수 있을까
- 연락처 정보에서 집전화번호와 팩스번호, 핸드폰번호를 구별하기가 어렵다.
- A고객은 집전화가 여러 대고, B고객은 핸드폰,팩스번호가 여러대라면 혼재된 속성에서 원하는 속성 값을 추출하기 어렵다.
- 명확하지 않은 속성은 이메일처럼 다른 유형의 데이터를 포함할 수도 있어 본연의 의미가 퇴색될 수 있다.
이와 같이 데이터를 관리한다면 개발의 복잡성은 증가할 것이고, 연락처의 속성은 그 의미가 점차 퇴색될 것이다. 이는 장기적으로 불안정한 데이터 구조를 양산할 것이다. 개발의 오류 및 데이터 품질 문제까지 야기할 수있다. 따라서 위와 같이 속성에 다중값이 들어있거나, 동일 속성이 반복될 경우 '제1정규화'가 필요하다.
고객연락처라는 엔터티를 추가하여 동일 속성이 반복되는 문제와 다중 값에 대한 문제점을 해결하였다.
-- 고객연락처 데이터--
| 고객번호 | 순번 | 연락처 |
|---|---|---|
| 10000 | 1 | 02-123-4567 |
| 10000 | 2 | 010-1234-5678 |
| 10000 | 3 | 070-1234-1234 |
| 10001 | 1 | 010-5678-2345 |
| 10002 | 1 | 02-345-3456 |
| 10002 | 2 | 010-4567-7890 |
--고객데이터--
| 고객번호 | 고객명 |
|---|---|
| 10000 | 정우진 |
| 10001 | 한형식 |
| 10002 | 황영은 |
위의 데이터를 보면 고객의 연락처가 아무리 많아져도 아무런 문제가 되지 않는다. 만약 집,회사번호,팩스번호를 구분하고싶다면 고객연락처 엔터티에 '연락처구분코드' 속성을 추가하면 된다.
제 2정규형 : 엔터티의 일반속성은 주식별자 전체에 종속적이어야 한다.
| 주문번호 | 상품번호 | 상품명 |
|---|---|---|
| 1100001 | 256 | SQL 전문가 가이드 |
| 1100002 | 257 | 데이터아키텍처 전문가 가이드 |
| 1100003 | 256 | SQL 전문가 가이드 |
| 1100004 | 256 | SQL 전문가 가이드 |
| 1100005 | 258 | 데이터 분석 전문가 가이드 |
표에서 데이터를 확인해보면 'SQL전문가가이드'라는 데이터가 반복되는 것을 볼 수 있다. 표에서 중복되는 데이터는 상품명 외 상품번호도 존재한다. 하지만 상품번호는 고객이 상품을 주문함으로써 발생하는 매핑정보로서 의미를 가지고있다.
주문번호와 함께 주문상세 엔터티의 식별자 의미를 가지고 있기에 중복된 데이터라고 볼 수 없다. 하지만 상품명은 주문번호와는 관계없이 오직 상품번호에 의해서만 결정된다. 이러한 것을 우리는 '종속적이다'라고 한다. 정리하면 상품명은 주문상세의 식별자인 '주문번호+상품번호'가 아닌 오직 상품번호에만 종속적이다. 이를 함수적 종속성으로 표기하면 아래 그림과 같다.
- 함수종속성은 데이터들이 어떤 기준값에 의해 종속되는 현상을 지칭한다.
이때 기준값을 결정자라 하고, 종속되는 값을 종속자라고 한다.
상품명은 상품번호에 종속되어 있기에 종속자이며, 상품번호는 상품명을 결정하기에 결정자이다.
위 사진에서 주문상세 엔터티의 상품명은 식별자 전체가 아닌 일부에만 종속적이다. 이를 부분 종속이라 한다. 제 2정규형을 위배한 것이다.
이러한 데이터의 문제점은 뭐가 있을까
- 상품명이 변경되고 업무적으로 반영해주어야 한다면 주문상세의 중복된 상품명을 모두 변경해야 한다. 이때 많이 팔린 상품일수록 주문상세에서 변경해야 할 상품명의 부하도 크게 증가한다.
- 주문상세의 상품명을 변경한다고 해도 특정 시점에는 아직 변경되지 않은 상품명이 존재하고, 이때 들어온 트랜잭션은 일관되지 않는 데이터를 조회하게 된다.
- 결국 데이터 중복은 성능과 정합성에 문제를 발생시킨다.
제2정규형을 만족할려면 상품 엔터티를 추가해주어야 한다.
--상품데이터--
| 상품번호 | 상품명 |
|---|---|
| 256 | SQL 전문가 가이드 |
| 257 | 데이터아키텍처 전문가 가이드 |
| 258 | 데이터 분석 전문가 가이드 |
--주문상세데이터--
| 주문번호 | 상품번호 |
|---|---|
| 1100001 | 256 |
| 1100002 | 257 |
| 1100003 | 256 |
| 1100004 | 256 |
| 1100005 | 258 |
제3정규형 : 엔터티의 일반속성 간에는 서로 종속적이지 않는다.
위의 사진의 주문엔터티를 보면 고객번호는 주문번호에 종속적이고 고객명은 고객번호에 종속적이다. 이는 '고객명이 주문번호에 종속적'임을 의미하는데 이것을 이행적 종속이라고하고 이행적 종속을 배제하는 것을 제3정규형이라고 한다.
주문번호 -> 고객번호 이고 고객번호 -> 고객명이면 주문번호->고객명이다.
-해당 모델의 문제점을 보면 만일 고객이 이름을 바꿔 고객명이 변경되었다면, 주문엔터티에 고객명을 전부 갱신해야 한다. 이는 주문과는 전혀 연관 없는 트랜잭션이다.
- 데이터 중복으로 인해 발생하는 문제는 성능 부하 및 정합성 오류로 제 2정규형과 동일하다.
예를 들면 고객명 '희은'에서 '은호'로 변경되었다면 주문엔터티의 '희은'이라는 고객명을 찾아 '은호'로 변경해주어야한다는 것이다.
이를 해결하기 위해서 고객엔터티를 분리하여 관리해야한다.
BCNF 정규형 : 제3 정규화를 진행한 테이블에 대해 모든 결정자가 후보키가 되도록 테이블을 분해한다.
아래의 수강테이블을 예로 들어보도록 하자.
| 학번 | 과목명 | 담당교수명 |
|---|---|---|
| 100 | 네트워크 | 박교수 |
| 101 | 운영체제 | 김교수 |
| 102 | 프로그래밍1 | 이교수 |
| 103 | 데이터베이스설계 | 장교수 |
| 104 | JAVA | 차교수 |
| 104 | 운영체제 | 김교수 |
- 삽입이상 : 새로운 교수가 특정 과목을 담당한다는 새로운 정보를 추가할 수 없다. 적어도 한 명 이상의 수강 학생이 필요하다.
- 삭제이상 : 학번 100이 네트워크 과목을 취소하면, 박교수가 네트워크 과목을 담당한다는 정보도 삭제된다.
- 갱신이상 : 박교수의 과목이 변경되면 박교수의 행을 모두 찾아 변경시켜주어야 한다.
이러한 이상현상이 생기는 이유는, 결정자가 후보키로 취급되고 있지 않기 때문이다
후보키는 슈퍼키 중에서 최소성을 갖는 키이므로 이 릴레이션에서는 (학번,과목명)이나 (학번,담당교수명)가 후보키가 된다. 담당 교수만으로는 후보키가 될 수 없다.
하지만, 후보키가 아님에도 과목명을 결정할 수 있기 때문에 담당 교수는 결정자에 속한다.
이 이상현상을 해결하기 위해서 모든 결정자는 항상 후보키가 되도록 릴레이션을 분해해줘야 한다.
| 학번 | 담당교수명 |
|---|---|
| 100 | 박교수 |
| 101 | 김교수 |
| 102 | 이교수 |
| 103 | 장교수 |
| 104 | 차교수 |
| 104 | 김교수 |
| 담당교수명 | 과목명 |
|---|---|
| 박교수 | 네트워크 |
| 김교수 | 운영체제 |
| 이교수 | 프로그래밍1 |
| 장교수 | 데이터베이스설계 |
| 차교수 | JAVA |
| 김교수 | 운영체제 |
제4정규형
- BCNF정규형을 만족하면서 함수종속이 아닌 다치종속을 제거해야한다.
- 다치 종속이란
두개의 독립된 속성이 1:N 관계로 대응하는 관계를 의미한다.
여기 교수정보릴레이션이 있다.
| 교수번호 | 교수명 | 담당교과목 |
|---|---|---|
| 200 | 박교수 | 네트워크, C언어 |
| 201 | 김교수 | 운영체제, 리눅스 |
| 202 | 이교수 | 프로그래밍1,프로그래밍2 |
| 203 | 장교수 | 데이터베이스설계 |
관계 데이터베이스는 기본적으로 속성이 '원자값'을 가질 수 있다.
하지만, 회원번호가 '200', '201', '202'인 튜플의 '담당교과목' 속성에는 값이 하나가 아니다.
이런 경우, 교수번호와 담당교과목 속성은 다치 종속성 관계이고,
교수번호↠담당교과목으로 나타낼 수 있다.
위 표를 아래의 표와 같이 표현해볼수있다.
| 교수번호 | 교수명 | 담당교과목 |
|---|---|---|
| 200 | 박교수 | 네트워크 |
| 200 | 박교수 | C언어 |
| 201 | 김교수 | 운영체제 |
| 201 | 김교수 | 리눅스 |
| 202 | 이교수 | 프로그래밍1 |
| 202 | 이교수 | 프로그래밍2 |
| 203 | 장교수 | 데이터베이스설계 |
하지만, 불필요하게 교수번호와 교수명속성이 중복되는 값이 만들어진다는 단점이 생긴다 따라서 다치종속성 문제를 해결하기 위해서 제4정규화를 진행하여 아래의 두개의 릴레이션으로 분해할 수 있다.
| 교수번호 | 교수명 |
|---|---|
| 200 | 박교수 |
| 201 | 김교수 |
| 202 | 이교수 |
| 203 | 장교수 |
| 교수번호 | 담당교과목 |
|---|---|
| 200 | 네트워크 |
| 200 | C언어 |
| 201 | 운영체제 |
| 201 | 리눅스 |
| 202 | 프로그래밍1 |
| 202 | 프로그래밍2 |
| 203 | 데이터베이스설계 |
제5정규형
- 제4정규형을 만족하면서 조인종속을 제거해야 한다.
- 조인종속이란
하나의 릴레이션을 여러개의 릴레이션으로 분해했다가 다시 조인했을때 데이터손실이 없고 필요없는 데이터가 생기는 것을 말한다.
모든 릴레이션이 제5정규형을 만족할 필요는 없다. 오히려 비효율적인 경우가 많아 일반적으로는 제3정규형이나 BCNF정규형에서 해결한다.
반정규화와 성능
- 반정규화는 정규화를 반대로 하는 것으로 역정규화라고도 한다.
- 정규화는 데이터의 중복을 최소화했다면, 반정규화는 성능을 위해 데이터 중복을 허용하는 것이다.
- 반정규화는 조회성능을 향상시킬 수 있을지 모르겠으나, 그로 인한 입력/수정/삭제 성능은 저하될 수 있다.
사례를 들어 정규화와 반정규화가 성능에 미치는 영향을 살펴보도록 하자.
1. 반정규화를 적용한 모델에서 성능이 향상될 수 있는 경우
위의 모델에서 고객의 편의를 위해 주문서 작성 시 최근 결제 정보를 미리 세팅하여 보여주고 싶어 최근 신용카드 정보를 미리 세팅하는 요건일 경우 아래와 같은 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;위와 같이 고객번호가 1234인 고객의 주문정보를 결제테이블과 조인으로 가져온 후, 신용카드 결제정보를 결제일시로 내림차순 정렬해 최근 1건의 결제수단번호를 가져온다. 이 SQL문은 1234고객의 주문내역이 많을 수록 성능이 나빠지는 문제가 존재한다.
최종결과는 1건을 가져오지만, 주문내역이 많을수록 해당 주문테이블과 결제테이블의 조인 건수가 증가하게 되며, 조인된 결제정보를 모두 읽고 내림차순 정렬하여 최근 1건의 데이터를 가져온다. 즉 주문내역이 많을수록 조인에 대한 부하가 증가하여 성능이 나빠지는 구조인것이다.
이를 개선하기 위해서 결제엔터티에 고객번호 속성을 반정규화함으로써 조인에 대한 성능부하를 개선할 수 있다.
수정된 SQL문은 다음과 같다.
SELECT A.결제수단번호 FROM
(
SELECT A.결제수단번호 FROM 결제 A
WHERE A.고객번호=1234
AND A.결제수단구분코드='신용카드'
ORDER BY A.결제일시 DESC
)A WHERE ROWNUM=1;2. 반정규화를 적용한 모델에서 성능이 저하될 수 있는 경우
위의 사진은 주문과 배송에 대한 모델이다.
대부분은 쇼핑몰들은 고객이 주문한 주문내역에 대해 배송정보를 조회할 수 있는 기능을 갖추고 있다.
주문정보는 주문엔터티에서 가져올 수 있고, 송장번호는 배송엔터티에서 가져올 수 있다. 즉 주문과 배송엔터티를 조인해야 한다. 하지만 성능을 위해서 주문엔터티에 송장번호를 역정규화해보자.
위의 사진과 같이 주문엔터티에 송장번호를 역정규화하면 배송엔터티와 조인하지 않아도 배송정보를 알수있게 된다.
하지만 업무프로세스를 생각해보면 고객이 주문하면 판매자가 배송을 진행한다.
이 말은 고객이 주문하는 그 시점에는 송장번호는 알 수가 없다.
주문자가 주문을 하였다고 해서 바로 배송이되는게 아니라 판매자의 사유로 주문이 취소될 수 있는 경우가 발생할 수도 있기 때문이다.
그렇다면 주문시점에는 송장번호가 NULL데이터가 들어가고 배송준비가 완료되어야 송장번호를 UPDATE할 수 있게 된다. 역정규화 하기 전에는 불필요한 UPDATE로직이 새로 추가되었다.
조회 성능 향상을 위해 불필요한 갱신로직을 추가할 필요가 없다는 것이다.
특히 AWS와 같은 클라우드 환경에서 운영되는 시스템이라면 이런 불필요한 로직으로 인해 과금이 늘어나게 되는 경우를 유의해야 할 것이다.
- 역정규화는 데이터 불일치로 인한 정합성 문제뿐만 아니라 불필요한 트랜잭션으로 인한 성능 문제를 만들어낸다.
참고자료 : SQL전문가가이드, Data-On-Air
