정규화(Normalization)의 기본 목표는 테이블 간에 중복된 데이터를 최소화시키는 것이다. 즉, 중복요소를 찾아 제거해 나가는 과정이라고 할 수 있다.
중복된 데이터를 허용하지 않음으로써 무결성(Integrity)를 유지할 수 있으며, DB의 저장 용량 역시 줄일 수 있다.
또한 삽입/갱신/삭제 시 발생할 수 있는 각종 이상현상(Anamolies)들을 방지할 수 있다.
데이터베이스 정규화의 목적은 주로 두 가지이다.
1. 불필요하고 중복된 데이터(data redundancy)를 제거한다.
2. 데이터 저장을 "논리적으로" 한다.
여기서 2번 데이터 저장을 논리적으로 한다는 것은 데이터 테이블의 구성이 논리적이고 직관적이어야한다는 것이다.
우선 정규화를 안 했을 때의 문제점에 대해서 알아보자.
위와 같이 Adam이라는 학생이 두 번 들어가 있는 것처럼 정규화가 되지 않은 구조의 테이블의 경우, 데이터 핸들링시 다양한 이상현상이 발생하게 된다.
이상 현상(Anomaly)
데이터를 다루면서 생기는 이상현상이란 무엇을 의미할까?
데이터베이스를 생성해서 사용하다가, 생각하지 못한 이상한 현상들이 몇몇 발생하는데, 다음과 같은 세 부분의 이상현상이 있다.
여러 종류의 종속 관계를 하나의 관계로 표현할 때 발생
속성 사이에 종속 혹은 튜플의 중복 때문에 발생
삽입 이상 - Insert
" 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제 "
만약, 위와 같은 데이터베이스가 있을 때 '나휴학'이라는 사람이 휴학을 했다고 가정해보자.
학생 테이블이므로 "나휴학" 의 Idx, 이름, 학년 데이터는 넣어줄 필요가 있다.
그럼 수강과목에는 어떤 값을 넣어야할까? NULL을 넣어도 될까?
이렇게, 대체할 수 없는 데이터에 대해 임의의 무효한 값(null)을 넣어줄 때가 생길 수 있다.
이럴 때를 바로 삽입이상이라고 한다.
갱신 이상 - Update
"관계에서 튜플의 속성을 갱신할 때 일부 정보만 갱신하여 데이터가 불일치하게 되는 모순의 문제 "
만약, 김다라가 2학년이 되어서 데이터를 수정해야한다고 가정해보자
그런 Idx가 1번인 데이터와 6번인 데이터를 모두 찾아서 변경해야할 것이다.
이 과정에서 한 행의 데이터라도 변경되지 않으면 데이터의 모순이 발생하게 된다.
삭제 이상 - Delete
"관계에서 한 튜플을 삭제할 때 상관없거나 꼭 필요한 값들도 함께 삭제되어 발생하는 데이터 손실 문제"
만약, 김다라가 데이터베이스 과목을 취소하고 싶어서 해당 레코드를 삭제하면 김다라에 대한 정보가 아예 사라질 것이다.
사라져야할 데이터인 '데이터베이스'뿐만 아니라 꼭 필요한 '김다라'에 대한 정보의 데이터가 손실된다.
위와 같이 정규화가 제대로 되지 않은 테이블의 경우 갱신/삽입/삭제 시 다양한 이상현상 문제점이 발생할 수 있다. 이를 테이블의 구성을 논리적으로 변경하여 해결하고자 하는 것이 바로 정규화다.
정규화의 장점
데이터베이스 변경 시 이상 현상(Anomaly)을 제거할 수 있다.
정규화된 데이터베이스 구조에서는 새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 된다.
데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되어 응용프로그램의 생명을 연장시킨다.
정규화의 단점
릴레이션(테이블)의 분해로 인해 릴레이션 간의JOIN연산이 많아진다.
질의에 대한 응답 시간이 느려질수도있다. 그러나 데이터의 중복 속성을 제거하고 결정자에 의해 동일한 의미의 일반 속성이 하나의 테이블로 집약되므로 한 테이블의 데이터 용량이 최소화되는 효과가 있다.
따라서 데이터를 처리할 때 속도가 빨라질 수도 있고 느려질 수도 있다.
만약 조인이 많이 발생하여 성능 저하가 나타나면 반정규화(De-normalization)를 적용할 수도 있다.
정규화의 법칙(Normalization Rule)은 1차정규화, 2차정규화, 3차정규화, BCNF, 4차정규화, 5차정규화로 나눌 수 있다.
하지만 실무적으로 4차, 5차 정규화까지 하는 경우는 많지 않다고 한다. 따라서 이 포스팅에서도 BCNF까지만 알아보겠다.
데이터베이스 정규화 절차
함수 종속성을 이용해 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해해서 이상현상이 발생하지 않도록 Step By Step Approach 로 수행
기본 정규형으로는 제 1‧2‧3‧BCNF 정규형이 있으며, 제 4‧5 정규형 은 고급 정규형으로 분류
[ 제1 정규화 ]
제1 정규화란 테이블의 컬럼이 원자값(Atomic Value, 하나의 값)을 갖도록 테이블을 분해하는 것이다.
제1 정규형은 다음과 같은 규칙들을 만족해야 한다.
1. 각 컬럼이 하나의 속성만을 가져야 한다. 2. 하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 한다. 3. 각 컬럼이 유일한(unique) 이름을 가져야 한다. 4. 칼럼의 순서가 상관없어야 한다.
간단하게 예시를 들면 이해가 빠르다. 아래 테이블을 살펴보자.
[그림 1] 1정규화가 필요한 테이블
각 컬럼이 하나의 값(속성)만을 가져야 한다. -> 불만족! 하나의 칼럼(과목)에 두 개의 값을 가짐
하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 한다. -> 만족
각 컬럼이 유일한(unique) 이름을 가져야 한다. -> 만족
칼럼의 순서가 상관없어야 한다. -> 만족
1번 규칙이 불만족하므로 이를 고치기 위해서는 아래와 같이 분해할 수 있다.
[그림 2] 1NF
위와 같이 각 컬럼이 원자 값을 갖도록 테이블을 분해하면 1 정규형을 만족하도록 분해한 것이다.
[ 제2 정규화 ]
제2정규화는 PK가 여러 키로 구성된 복합키(Composite Primary Key)로 구성된 경우 2차 정규화의 대상이 되며, 복합키 전체에 의존하지 않고 복합키의 일부분에만 종속되는 속성들이 존재할 경우 (즉, 부분적 함수 종속 관계) 이를 분리하는 것이다.
즉, 제2 정규화란 제1 정규화를 진행한 테이블에 대해부분 함수적 종속을 제거(완전 함수 종속을 만족)하도록 테이블을 분해하는 것이다.
※참고
함수적 종속 이란?
X의 값을 통해 Y의 값을 바로 식별할 수 있을 때, 혹은 X값에 따라 Y값이 달라질 때 함수적 종속이라고 한다.
여기서 완전 함수 종속이라는 것은 기본키의 부분집합이 결정자가 되어선 안된다는 것을 의미한다. 다른 말로는 모든 column이 하나의 키 값에 의한 종속자인 것을 의미한다.
제2 정규형은 다음과 같은 규칙을 만족해야 한다.
1. 1정규형을 만족해야 한다. 2. 모든 컬럼이 부분적 종속(Partial Dependency)이 없어야 한다. == 모든 칼럼이 완전 함수 종속을 만족해야 한다.
부분적 종속이란 기본키 중에 특정 컬럼에만 종속되는 것이다.
완전 함수 종속이란 기본키의 부분집합이 결정자가 되어선 안된다는 것이다. ( 비슷한 말이다 )
그림 3] 2정규화가 필요한 테이블
위와 같은 테이블과 FD 다이어그램을 보자.
성적의 특정 값을 알기 위해서는 (학생 번호+과목)이 있어야 한다. (ex : 102번의 자바 성적 70 )
하지만 특정 과목의 지도교수는 과목명만 알면 지도교수가 누군지 알 수 있다. (ex : 자바의 지도교수 박자바)
위 테이블에서 기본키는 (학생 번호, 과목)으로 복합키이다.
그런데 이때 지도교수 컬럼은 기본키인 (학생 번호, 과목)에 종속되지 않고 기본키의 부분집합인 (과목) 에만 종속되는 부분적 종속이다.
따라서 제2 정규화를 만족하지 않으므로 아래와 같이 분해해야 한다.
[그림 4] 2NF
위와 같이 분해하면 제2 정규형을 만족한다.
[ 제3 정규화 ]
테이블의 키가 아닌 컬럼들은 기본키에 의존해야 하는데 겉으로는 그런 것처럼 보이지만 실제로는 기본키가 아닌 다른 일반 컬럼에 의존하는 컬럼들이 있을 수 있다. 이를 (이행적 함수 종속 관계)라고 한다.
제3정규화는 PK에 의존하지 않고 일반컬럼에 의존하는 컬럼들을 분리한다. 즉, 제2 정규화를 진행한 테이블에 대해이행적 종속을 없애도록테이블을 분해하는 것이다.
이행 종속성이란 A->B, B->C 일 때 A->C 가 성립하면 이행 종속이라고 한다.
1. 2 정규형을 만족해야 한다. 2. 기본키를 제외한 속성들 간의 이행 종속성 (Transitive Dependency)이 없어야 한다.
예를 들어 아래와 같은 계절 학기 테이블을 살펴보자.
테이블을 보면 학생 번호를 알면 강좌 이름을 알 수 있다. 그리고 강좌 이름을 알면 수강료를 알 수 있다. 따라서 학생 번호를 알면 수강료를 알 수 있다. 즉, 이행 종속성이 존재하므로 제 3정규형을 만족하지 않는다.
그러므로 이를 (학생 번호, 강좌 이름) 테이블과 (강좌 이름, 수강료) 테이블로 분해해야 한다.
이행적 종속을 제거하는 이유는 비교적 간단하다.
예를 들어 501번 학생이 수강하는 강좌가 스포츠경영학으로 변경되었다고 하자. 이행적 종속이 존재한다면 501번의 학생은 스포츠경영학이라는 수업을 20000원이라는 수강료로 듣게 된다. 물론 강좌 이름에 맞게 수강료를 다시 변경할 수 있지만, 이러한 번거로움을 해결하기 위해 제3 정규화를 하는 것이다.
즉, 학생 번호를 통해 강좌 이름을 참조하고, 강좌 이름으로 수강료를 참조하도록 테이블을 분해해야 하며 그 결과는 다음의 그림과 같다.
[ BCNF(Boyce and Codd Normal Form) 정규화 ]
BCNF란 3차정규형을 만족하면서모든 결정자가 후보키 집합에 속한 정규형이다.
후보 키(Candidate key): Super key 중에서 더이상 쪼개질 수 없는 Super key를 Candidate Key라고 한다. 즉 각 row를이 유일성과최소성을 만족하는 최소한의 속성들의 집합이다. 기본키가 될 수 있는 후보이기 때문에 후보키라고 불린다.
1. 3정규형을 만족해야 한다. 2. 모든 결정자가 후보키 집합에 속해야 한다.
모든 결정자가 후보키 집합에 속해야 한다는 뜻은, 후보키 집합에 없는 컬럼이 결정자가 되어서는 안 된다는 뜻이다.
BCNF가 필요한 테이블
특강수강 테이블에서 기본키는 (학생번호, 과목)이다. 그리고 기본키 (학생번호, 과목)는 교수를 결정하고 있다. 하지만 같은 과목을 다른 교수가 가르칠 수도 있어서 과목-> 지도교수 종속은 성립하지 않는다.
하지만 지도교수가 어떤 과목을 가르치는지는 알 수 있으므로 지도교수-> 과목 종속이 성립한다.
그런데 문제는 교수가 과목을 결정하는 결정자이지만, 후보키가 아니라는 점이다.
이처럼 후보키 집합이 아닌 컬럼(즉, 지도교수 컬럼) 이 결정자가 되어버린 상황을 BCNF를 만족하지 않는다고 한다.
(참고로 위 테이블은 제3 정규형까지는 만족하는 테이블이다 )
BCNF를 만족하기 위해서는 아래와 같이 분해하면 된다.
[그림 8] BCNF
참고로 위에서 학생 번호와 지도교수는 다치 종속성이 발생하게 되는데, 이는 제4 정규형에서 다뤄진다.
[제4 정규형 이상~]
정규형
보통 정규화는 BCNF 까지만 하는 경우가 많다. 그 이상 정규화를 하면 정규화의 단점이 나타날 수도 있다.
결국, 정규화 과정이란 중복된 속성을 최소화 하고 종속관계에 있는 속성을 제거하는 과정이다. 이러한 정규화 과정을 다시 조인하면 데이터의 손실없이 이전상태로 복구가 가능해야 한다.
역정규화
위에서 정규화를 학습하면서 정규화의 필요성과 과정을 살펴보았다. 하지만 이렇게 정밀한 과정을 거쳐 테이블을 분해했는데 왜 이걸 굳이 또 역정규화하는 방법론이 존재하는 것일까?
우선 정규화의 개념에 대해 짧게 다시 살펴보자.
정규화
관계형 데이터베이스의 설계에서중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화(Normalization)라고 한다. (출처 Wikipedia)
Data Model 과정에서 Entity 사이의 관계(Level, Depth 등)를 분석하여 다수의 Relation으로 분리하는 과정을 말한다.
정규화는 이렇게 함으로써 데이터의 일관성(Consistency)과 모델의 응집도(Cohesion)를 높이는 것을 지향한다.
정규화는 일관성(Consistency)을 향상시킨다.
정규화는 하나의 논리적 Database에서 여러 테이블에 동일한 데이터(column)가 관리되지 않도록 설계하는 과정이다.
만약, 여러 테이블에서 필요한 데이터라면 해당 Entity의 Level은 최상위 Level에 속하게 될 것이고, 데이터의 값(value)이 아닌 데이터의 구분자(일반적으로 index)로 관계를 맺어주면 된다.
즉, 데이터가 한 곳에서 관리되는 것은 데이터의 변경(INSERT / UPDATE / DELETE)이 있을 때, 특정 테이블의 특정 row만 수정하면 된다.
하지만 여러 곳에 데이터가 산재해 있을 경우, 해당 데이터의 값을 일관되게 관리하는 즉, 데이터의 Sync 이슈를 최소화 시킬 수 있기 위해서는 정규화 과정이 중요하다.
정규화는 데이터 조회의 비용을 향상시킨다.
정규화를 통해 데이터를 Entity 단위로 분리하고, 각 데이터 간의 Relation을 만들었다면, 필요한 데이터가 하나의 테이블에 있지 않은 경우가 많다.
즉, 필요한 정보가 해당 테이블에 없기 때문에 다른 테이블과 JOIN을 통해 정보를 가져와야 하며, 이는 연산 시간과 비용을 증가시킬 수 있다.
역정규화
정규화된 데이터베이스에서 성능을 개선이나 개발적 측면에서 편의성을 위해 사용되는 전략을 역정규화(Denormalization)라고 한다. (출처 Wikipedia)
정규화의 단점으로 언급되었던 것이 '비용(Cost)'이다.
역정규화는 데이터베이스의 비용을 최소화하기 위해 중복을 허용하며 Entity를 다시 통합하거나 분할하여 정규화 과정을 통해 도출된 DB 구조를 재조정하는 과정이다.
즉, Join이 너무 많아지는 DB 설계와 쿼리는 요청을 처리하는 시간을 증가시키는 문제가 있기 때문에 모든 주요 Entity를 분리하는 것이 좋은 것이 아니라 DB의 전반적인 성능을 향상시킬 수 있는 구조화 과정을 거치는 것이 필요하다.
다시 한번 더 말하지만 역정규화를 하는 가장 큰 이유는 성능 문제가 있는(읽기작업이 많이 필요한) DB의 전반적인 성능을 향상시키기 위함이다.
그렇다고 정규화를 한다고 해서 반드시 성능을 떨어뜨리는 것이 아니기 때문에 일반화 하지는 말자.
다음과 같은 정규화를 마친 논리적 테이블들이 있다고 하자.
[ 원본 테이블 ]
역정규화 - JOIN을 줄이기
1. 만일 topic_tag_relation의 topic_title필드를 가지고 tagName을 알아야된다고 해보자.
2. 그러면 topic_tag_relation과 tag테이블을 조인해서 조건에 부합하는 name컬럼 값을 가져오면 된다.
3. 하지만 만일 저러한 쿼리가 서버내에서 많이 일어날 경우, join은 많은 부하가 일어나길 마련이다.
4. 이때 그냥 테이블을 하나로 합쳐버리는 것이다.
5. 제 1정규화를 통해 나눴던 걸 성능과 편의를 위해서 역으로 되돌아가버린 것이다.
역정규화 - 계산 작업을 줄이기
이번에는 데이터를 처리하는 비용을 줄이기 위한 역정규화 이다.
1. 만일 count 값이 필요하다고 가정하자. group by를 통해 컬럼을 묶고 조회하면 된다.
2. 하지만 만일 이러한 쿼리가 빈번하게 일어난다면 역시 부하가 발생한다.
3. 따라서 그룹핑해서 내장함수를 쓰는게 아닌 아예 topic_count데이터를 컬럼 추가하는 것이다. topic_count 필드를 추가하고 데이터를 준다.
4. 하지만 유효한 데이터를 유지하기 위해서는 글이 추가될때마다 지속적으로 데이터를 업데이트해야 하는 관리를 해야 한다.
5. 트리거를 쓰던지 서버 사이드에서 처리하던지 하면 된다.
6. 관건은 group by 부하를 줄이는 것이기 때문이다.
역정규화 - 테이블 분리
하나의 표를 성능을 위해서 여러개로 쪼개는 경우이다.
만일 topic테이블에 description의 용량이 엄청많을때 이때 topic테이블을 자주 조회했을 경우 약간 부하가 올수도 있다.
그래서 표의 성능을 위해 용량이 큰 테이블을 따로 분리하는 것이다.
테이블을 나누면 여러대의 서버에서 각기 다르게 접근해서 처리할수 있으니 성능이 좋다.
행을 기준으로 분리하는것은 만일 author_id 회원이 몇만명일때, 하나의 테이블에 접근해서 처리하는것보다, 테이블을 분담해서 author_id가 1000이하일 경우 1500이상일 경우 각기 다른 테이블로 접근해서 부하를 줄이는 것이다.
