Database Normalization
1NF, 2NF, 3NF 등의 Normalization과 SCD Type들에 대해 배워보자
•
Data Maturity Model and Reality
BI analytics와 거기에 사용되는 data 사이에 큰 갭이 존재
Database Normalization
•
데이터베이스를 좀더 조직적이고 일관된 방법으로 디자인하려는 방법
•
데이터베이스 정합성을 쉽게 유지하고 레코드들을 수정/적재/삭제를 용이하게 하는 것
•
Normalization에 사용되는 개념
◦
Primary Key (기본 키)
◦
Composite Key (복합 키)
▪
여러 칼럼의 조합이 기본 키
◦
Foreign Key (참조 키)
•
위의 개념은 일반 RDB에서 적용됨
1NF (First Normal Form)
목표는 중복을 제거하고 atomicity를 갖는 것
•
한 셀에는 하나의 값만 있어야함 (atomicity)
•
Primary Key가 있어야함
•
중복된 키나 레코드들이 없어야함
•
Employee 테이블
2NF (Second Normal Form)
목표는 중복을 제거하고 atomicity를 갖는 것
쉽게 말하자면, 스타 스키마의 형태를 갖추는 것
트랜잭션 - 팩트 테이블
•
일단 1NF를 만족해야함
•
다음으로 Primary Key를 중심으로 의존결과를 알 수 있어야함
•
부분적인 의존도가 없어야함
◦
즉 모든 부가 속성들은 Primary key를 가지고 찾을 수 있어야함
◦
즉, 모든 PK가 아닌 칼럼은 기본 키 칼럼에 완전히 의존(종속)합니다.
▪
사실 State_code와 home_state도 밖으로 분리되는 것이 더 스타 스키마에 가깝다.
3NF (Third Normal Form)
•
일단 2NF를 만족해야함
•
전이적 부분 종속성을 없어야함
◦
전이적 부분 종속성 : 테이블 내에서 기본키로 인한 종속성이 아닌 칼럼 사이에 부분적으로 종속성이 발생
◦
2NF의 예에서 state_code과 home_state가 같이 Employees 테이블에 존재
•
예
Fact 테이블과 Dimension 테이블
•
Fact 테이블: 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블
◦
일반적으로 매출 수익, 판매량, 이익과 같은 측정 항목 포함. 비즈니스 결정에 사용
◦
Fact 테이블은 일반적으로 외래 키를 통해 여러 Dimension 테이블과 연결됨
◦
보통 Fact 테이블의 크기가 훨씬 더 큼
•
Dimension 테이블: Fact 테이블에 대한 상세 정보를 제공하는 테이블
◦
고객, 제품과 같은 테이블로 Fact 테이블에 대한 상세 정보 제공
◦
Fact 테이블의 데이터에 맥락을 제공하여 다양한 방식으로 분석 가능하게 해줌
◦
Dimension 테이블은 primary key를 가지며, fact 테이블에서 참조 (foreign key)
◦
보통 Dimension 테이블의 크기는 훨씬 더 작음
SCD(Slowly Changing Dimensions)
•
Slowly Changing Dimensions
◦
DW나 DL에서는 모든 테이블들의 히스토리를 유지하는 것이 중요함
▪
보통 두 개의 timestamp 필드를 갖는 것이 좋음
•
created_at (생성시간으로 한번 만들어지면 고정됨)
•
updated_at (꼭 필요 마지막 수정 시간을 나타냄)
▪
어떤 칼럼은 시간을 두고 바뀌기도 하고 어떻게 기록을 할지 중요해진다.
◦
이 경우 컬럼의 성격에 따라 어떻게 유지할지 방법이 달라짐
▪
SCD Type 0
▪
SCD Type 1
▪
SCD Type 2
▪
SCD Type 3
▪
SCD Type 4
◦
OLTP로부터 OLAP으로 복사를 해오는데 분석을 하는 입장에서는 히스토리가 중요하다. 일부 속성들은 시간을 두고 변하게 되는데 DW Table쪽에 어떻게 반영해야하나?
▪
현재 데이터만 유지 vs 처음부터 지금까지 히스토리도 유지
SCD Type
•
SCD Type 0
◦
한번 쓰고 나면 바꿀 이유가 없는 경우들
◦
한번 정해지면 갱신되지 않고 고정되는 필드들
▪
예) 고객 테이블이라면 회원 등록일, 제품 첫 구매일
•
SCD Type 1
◦
데이터가 새로 생기면 덮어쓰면 되는 컬럼들
◦
처음 레코드 생성 시에는 존재하지 않았지만 나중에 생기면서 채우는 경우
▪
derived field
▪
예) 고객 테이블이라면 연간소득 필드
•
SCD Type 2 (2, 3, 4 → 히스토리와 관계 있음)
◦
특정 entity에 대한 데이터가 새로운 레코드로 추가되어야 하는 경우
◦
예) 고객 테이블에서 고객의 등급 변화
▪
tier라는 컬럼의 값이 “regular”에서 “vip”로 변화하는 경우
▪
즉 customer_id 가 100인 row가 하나 더 추가 된다 (tier = vip)
▪
등급이 변경 시간 칼럼도 같이 추가되어야함
•
SCD Type 3
◦
SCD Type 2의 대안으로 특정 entity 데이터가 새로운 컬럼으로 추가되는 경우
◦
예) 고객 테이블에서 tier라는 컬럼의 값이 “regular”에서 “vip”로 변화하는 경우
▪
previous_tier라는 컬럼 생성
▪
변경시간도 별도 컬럼으로 존재해야함
•
️ SCD Type 4◦
일반적으로 많이 쓰임
◦
특정 entity에 대한 데이터를 새로운 Dimension 테이블에 저장하는 경우
◦
SCD Type 2의 변종
◦
예) 별도의 테이블로 저장하고 이 경우 아예 일반화할 수도 있음
▪
tier의 변경 흐름을 새로운 테이블을 만들어 저장