데이터베이스 정규화
이상현상
데이터베이스 설계 오류로 데이터 무결성/정합성이 일어날 수 있다. 이것을 이상 현상 (Anomaly)라고 한다. 이상 현상이 발생할 경우 리소스 낭비를 비롯하여 운영 상 의도치 않은 현상이 일어날 수 있다.
- 삽입 이상 (Insertion Anomaly)
- 신규 데이터를 삽입하기 위해 불필요한 데이터를 입력해야하는, 또는 필요 데이터를 입력하지 못하는 현상을 의미
- 목적과 맞지않은 조합PK를 만들거나 필요 이상의 컬럼을 기본키로 지정하는 경우
- 갱신 이상 (Update Anomaly)
- 중복 데이터 중 일부만 변경해 데이터가 불일치하게 되는 문제를 의미
- ※ 회사구조 테이블 ( PK: 이름/부서/업무명 )
- 회사 내 사원정보와 부서정보가 한 테이블에 혼합되어 저장되어 있는 형태
| 이름 | 부서 | 업무명 | … |
|---|---|---|---|
| 윤석열 | 재무팀 | 경비관리 | … |
| 윤석열 | 재무팀 | 회계장부관리 | … |
| 홍준표 | 구매팀 | 업체계약 | … |
| 원희룡 | 인사팀 | 채용 | … |
| 이준석 | 재무팀 | IR | … |
위 테이블에서 윤석열이 부서를 옮겨 부서 값을 변경해야 할 때 PK값이 이름, 부서, 업무명 3개 컬럼 값이기 때문에 윤석열의 부서값 중 일부만 변경되어 데이터가 불일치하게 되는 문제를 갱신 이상이라고 한다.
- 삭제 이상 (Delete Anomaly)
- 삭제가 되지 않아야 할 데이터도 같이 지워지는 문제를 의미
- 위 테이블에서, 재무팀을 삭제하는 경우 윤석열이라는 사원의 정보까지 삭제되는 현상을 삭제 이상이라고 한다.
함수적 종속성 (Functional Dependency)
보통 학교나 회사에서는 각 사람별 이름을 학번/사번으로 대체해서 관리한다. 이런 경우 DB에서는 사번을 결정자 X / 사람 이름을 종속자 Y라 하여 Y가 X에 함수적으로 종속되어있다라고 하고 X -> Y라 표현한다.
부분 함수적 종속성
X가 개별 속성이 아닌 집합일 경우 X의 부분집합 만으로도 Y를 결정지을 수 있을 때 부분 함수적 종속이라고 한다.
예시로, {학번, 학과} 라는 집합 X에서 학번 만으로 이름 Y가 결정될 수 있는 경우를 의미한다.
완전 함수적 종속성
X가 집합이면서 종속자 Y가 집합 X 전체에 대해서만 함수적 종속이 될 때 완전 함수적 종속이라고 한다.
위 테이블에서, X 집합을 {부서, 업무명}이라고 할 때 이름을 결정지으려면 부서 만으로는 {윤석열, 이준석} 두 값이 나와 특정 값을 결정할 수 없고 업무명 값까지 결정자 집합으로 지정해야 특정 이름을 구분지을 수 있다.
정규화
위와 같은 이상(Anomaly)현상 또는 과한 함수적 종속성을 피하기 위해서 정규화라는 개념을 도입한다.
데이터베이스를 설계하면서 값 간의 중복을 최소화하는 구조화 프로세스를 의미하며 각 단계 별로 규칙 또는 기준이 추가된다. 실무에서는 1~3NF, BCNF까지 사용한다.
제 1 정규형 - 1NF (First Normal Form)
모든 속성 값은 원자성(Atomic)을 확보해야 한다.
-> 하나의 Row 속성에는 하나의 값만 들어가야 한다.
| 이름 | 부서 | 업무명 | … |
|---|---|---|---|
| 윤석열 | 재무팀 | 경비관리, 회계장부관리 | … |
↓
| 이름 | 부서 | 업무명 | … |
|---|---|---|---|
| 윤석열 | 재무팀 | 경비관리 | … |
| 윤석열 | 재무팀 | 회계장부관리 | … |
제 2 정규형 - 2NF (Second Normal Form)
1NF를 만족시키는 테이블에서 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속성이 지켜진다면 2NF에 속한다. -> 부분 함수 종속성이 발견되는 속성들에 대해서 별도의 테이블을 만들어야 한다.
| 이름 | 보유기술 | 기술레벨 | 회사 | 연봉 | … |
|---|---|---|---|---|---|
| 윤석열 | C++ | A | 네이버 | 4,400 | … |
| 윤석열 | Java | A | 네이버 | 4,400 | … |
| 윤석열 | Python | B | 네이버 | 4,400 | … |
| 홍준표 | Oracle | C | 토스 | 3,800 | … |
| 원희룡 | Javascript | S | 당근마켓 | 3,500 | … |
| 이준석 | Oracle | S | 카카오 | 4,200 | … |
이러한 테이블이 존재하며 PK는 {‘이름’, ‘보유기술’}일 때, 이름 → {회사, 연봉} 이라는 부분 함수 종속성이 나타난다. 이를 정규화 시키기 위해서는 이름 / 회사 / 연봉 속성을 가지는 별도의 테이블로 분리해야 한다.
| 이름 | 보유기술 | 기술레벨 | … |
|---|---|---|---|
| 윤석열 | C++ | A | … |
| 윤석열 | Java | A | … |
| 윤석열 | Python | B | … |
| 홍준표 | Oracle | C | … |
| 원희룡 | Javascript | S | … |
| 이준석 | Oracle | S | … |
| 이름 | 회사 | 연봉 | … |
|---|---|---|---|
| 윤석열 | 네이버 | 4,400 | … |
| 홍준표 | 토스 | 3,800 | … |
| 원희룡 | 당근마켓 | 3,500 | … |
| 이준석 | 카카오 | 4,200 | … |
제 3 정규형 - 3NF (Third Normal Form)
2NF를 만족시키는 테이블에서 이행적 함수 종속이 충족되지 않도록 한다면 3NF에 속한다.
-> 속성 간 삼단 논법(꼬리에 꼬리를 무는꼬꼬무) 관계를 방지해야 한다.
이행적 함수 종속
속성값 X, Y, Z에 대해 X → Y, Y → Z라면 X → Z가 성립하고 Z가 X에 이행적으로 함수 종속되었다.라고 표현한다.
예를 들어 위와 같이 이름 / 회사 / 연봉으로 이루어진 테이블에서 회사가 이름에 종속되고, 연봉이 회사에 종속된다고 하자.
홍준표가 당근마켓으로 이직한다고 하면 회사명을 바꾸게 될 것이다. 그런데 회사명만 바꾸게 된다면 연봉은 회사에 종속되어있기 때문에 갱신이상을 범하게 된다.
이러한 이행적 종속 현상을 방지하기 위해 테이블을 분해한다.
| 이름 | 회사 | … |
|---|---|---|
| 윤석열 | 네이버 | … |
| 홍준표 | 토스 | … |
| 원희룡 | 당근마켓 | … |
| 이준석 | 카카오 | … |
| 회사 | 연봉 | … |
|---|---|---|
| 네이버 | 4,400 | … |
| 토스 | 3,800 | … |
| 당근마켓 | 3,500 | … |
| 카카오 | 4,200 | … |
BCNF (Boyce-Codd Normal Form)
3NF를 진행한 테이블 중에 모든 결정자가 후보키가 되도록 테이블을 분해하는 정규화를 의미한다.
{속성1, 속성2}로 이루어진 기본키가 속성3을 결정하는 결정자로 지정되어 있을 때 속성3이 종속성으로만 남아있지 않고 후보키를 결정짓는 결정자일때 BCNF를 충족시키기 위해 분해해야 한다.
반정규화
시스템의 성능 향상, 개발 및 운영 편의성 등을 위해 일부러 정규화를 의도적으로 위반하고 데이터 모델을 통합/중복/분해하는 과정을 의미한다.
반정규화를 수행함으로써 시스템의 성능이 향상되고 관리 효율성이 증가하지만, 일괄성, 정합성이 저하될 수 있기 때문에 주의 필요
1. 테이블 통합
특정한 2개의 테이블이 서로 Join되어 사용되는 경우가 많아 하나의 테이블로 함쳐 사용하는 것이 성능 향상에 도움이 될 경우 수행
검색 위주의 테이블일 경우 효과적이지만, 입력,수정,삭제 등 DML 수행 시 복잡해 질 수 있다.
Not Null, Default, Check 등의 제약조건을 설계하기 어렵다는 단점 또한 있다.
2. 테이블 분할
테이블 분할에는 수평 분할과 수직 분할이 있다.
수평 분할
수평분할은 컬럼은 동일하지만 Row 별로 사용 빈도 차이가 큰 경우 사용 빈도율을 기준으로 분할하는 방법이다. 보통 매출 이력 등 과거 데이터들을 분리하는 경우에 수행하곤 한다.
수직 분할
한 테이블 내 컬럼이 너무 많을 경우 속성을 기준으로 테이블을 분할하는 것으로 정규화에서의 분할과는 다소 다르다.
- 갱신/조회 위주의 속성 분할
- 한 테이블 내에서 갱신이 자주 일어나는 컬럼과 조회만 수행하는 컬럼을 나누어 용도를 달리 하는 방법
- 크기 위주 분할
- Multipart, 파일, 암호화 같은 대용량을 저장해야하는 컬럼 값을 따로 분리하여 관리
- 보안 적용 분할
- 특정 속성에 대해 보안을 적용할 수 없으므로 보안 적용이 필요한 속성에 대해 분리하는 방법
고려사항
- 수직 분할의 경우 수평과 달리 기본키의 유일성 관리가 어려워진다.
- 데이터 양이 적거나 사용 빈도가 낮은 경우 분할이 필요한지 재고려 필요
- 분할된 테이블로 인해 수행 속도가 느려질 수 있음
- 데이터 검색에 중점을 두어 테이블 분할 여부를 결정
중복 테이블 추가
여러 테이블에서 데이터를 추출하여 사용해야 하거나, 다른 DB서버에서 테이블을 가져와야 하는 경우 중복 테이블 생성으로 효율성을 증대시킬 수 있다.
- 집계 테이블 추가
- 집계 데이터를 위한 테이블을 생성하고 각 원본 테이블에 트리거를 설정하여 사용. 트리거 오버헤드가 주요 고려사항
- 진행 테이블 추가
- 이력 관리가 주 목적이며 적절한 데이터 양과 유지 활용도를 높이기 위해 기본키 적절히 설정해야 한다.
- 특정 부분만을 포함하는 테이블
- 데이터가 많은 테이블의 특정 부분만을 사용하는 경우(특정 컬럼의 특정기간 Row) 해당 부분만으로 새로운 테이블 생성
중복 속성 추가
Join으로 데이터를 처리할 때 데이터 조회 경로를 단축하기 위해 자주 사용하는 속성을 추가하는 것 중복 속성 추가하는 경우
- Join이 자주 발생하는 속성
- 접근 경로가 복잡한 속성
- 액세스의 조건으로 자주 사용되는 속성
- 기본키의 형태가 적절하지 않거나 여러 개의 속성으로 구성된 경우
주요 고려사항
- 테이블/속성 중복 고려
- 데이터 일관성 및 무결성 유의
- SQL 그룹함수를 이용하여 처리할 수 있어야 함
- 저장공간 낭비 고려
