해당 글은 데이터베이스 설계를 위한 RDBMS 데이터 모델링에 대해 이해를 돕기 위해 작성한 글입니다.
1) 데이터 모델링(Data Modeling)
💡 데이터 모델링(Data Modeling)
- 데이터베이스 시스템을 설계하는 과정에서 데이터를 구조화하여 정의, 설계, 구현 및 관리하는 일련의 단계를 의미합니다. - 이는 조직의 데이터의 요구사항을 분석하고 정의하여, 이를 기반으로 데이터베이스의 구조를 설계를 합니다. - 데이터 모델링의 과정으로 개념적 모델링, 논리적 모델링, 물리적 모델링의 단계를 가집니다. 이러한 단계를 통해 추상적인 개념을 구체적인 개념으로 변화되는 과정입니다.
조직의 데이터 요구사항을 분석하고 정의하는 단계이며, ERD를 사용하여 데이터 구조의 큰 그림으로 파악합니다.
논리적 모델링
개념적 모델링에서 정의된 데이터를 기반으로 데이터베이스 구조를 구체적으로 설계하며 데이터 타입, 키, 제약 조건 등을 정의합니다.
물리적 모델링
논리적 모델링을 기반으로 실제 DBMS에 데이터베이스를 구현. 성능, 저장 효율성, 보안을 고려하여 테이블 설계 및 인덱스를 정의합니다.
1. 개념적 모델링(Conceptual, Contextural Modeling)
💡 개념적 모델링(Conceptual, Contextural Modeling) - 데이터 모델링의 첫 번째 단계로, 조직의 데이터 ‘요구사항을 분석하고 정의’합니다. - 이 단계에서는 비즈니스의 전반적인 데이터 구조를 이해하고 주요 테이블과 그들 간에 관계를 식별하여 ERD(Entity-Relationship Diagram)를 사용하여 시각적으로 표현합니다. 이를 통해 데이터 구조의 큰 그림을 파악할 수 있습니다. - 이 단계에서는 데이터베이스의 구체적인 구현보다는 비즈니스 요구사항을 충족시키는 데 중점을 둡니다.
💡 개념적 모델링 E-R 다이어그램
- 고객의 요구사항에 대해서 개념적 모델링 과정인 E-R 다이어그램 작성을 통해서 시각적으로 표현이 되고 이를 통해 데이터 구조의 큰 그림을 파악할 수 있습니다.
💡 논리적 모델링(Logical Modeling) - 개념적 모델링 단계에서 정의된 데이터를 기반으로 데이터베이스 구조를 보다 구체적으로 설계하는 과정입니다. 해당 과정에서는 데이터 타입, 길이, 키 및 제약 조건과 같은 세부 사항을 정의합니다.
- 논리적 모델링은 특정 데이터베이스 관리 시스템(DBMS)에 의존하지 않으며, 데이터베이스의 논리적 구조를 표현합니다. - 이를 통해 데이터의 무결성, 일관성 및 효율성을 보장할 수 있는 데이터베이스 스키마를 설계합니다. - ERD(Entity-Relationship Diagram)를 사용하여 테이블, 컬럼 및 관계를 시각적으로 표현합니다.
💡 논리적 모델링 : ERD
- 이전에 작성한 E-R 다이어그램을 기반으로 논리적 모델링 단계에서는 좀 더 구체적으로 설계하는 과정입니다. - 이는 테이블을 도출하고 컬럼에 대해 데이터 타입, 길이, 키 및 제약 조건등을 정의하며 각 테이블 간의 관계를 정의합니다.
3. 물리적 모델링(Physical Modeling)
💡 물리적 모델링(Physical Modeling)
- 논리적 모델링을 기반으로 ‘실제 데이터베이스 관리 시스템(DBMS)에 데이터베이스를 구현하는 단계’입니다. 이 단계에서는 데이터베이스의 성능, 저장 효율성 및 보안 등을 고려하여 테이블을 설계하고 인덱스, 파티션 등을 정의합니다.
- 또한, 데이터의 물리적 저장 구조를 설계하고, 이를 통해 데이터 접근 속도를 최적화합니다. - 물리적 모델링 단계에서는 데이터베이스의 용량 계획, 백업 및 복구 전략도 포함됩니다.
💡 물리적 모델링 : DDL
- 논리적 모델링 단계에서 구성한 ERD를 통해서 DDL 스크립트를 구성하여 실제 데이터베이스 내에 해당 테이블을 추가합니다.
CREATE TABLE Reviews (
Product_ID int PRIMARY KEY,
ReviewerID int,
Rating int,
Review text,
Products_ID int FOREIGN KEY REFERENCES Products(ProductID)
);
CREATE TABLE Customers (
ID int PRIMARY KEY,
CustomerID int,
FirstName varchar(50),
LastName varchar(50)
);
CREATE TABLE Products (
ProductID int PRIMARY KEY,
Name int,
Price numeric(8,2)
);
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
Quantity int,
ProductID int FOREIGN KEY REFERENCES Products(ProductID),
CreatedAt datetime2,
Products_ID int FOREIGN KEY REFERENCES Products(ProductID),
Customers_ID int FOREIGN KEY REFERENCES Customers(ID)
);
2) 정규화(Normal Form, NF)
💡 정규화(Normal Form, NF)
- 데이터베이스 설계 과정에서 ‘데이터의 중복을 최소화’하고 데이터 무결성을 유지하며 테이블 간의 관계를 단순하게 하여 데이터 구조화를 이루는 단계를 의미합니다. - 정규화는 여러 단계로 나뉘며, 각 단계는 이전 단계에서 해결되지 않은 데이터 중복 문제와 같은 ‘데이터 이상 현상’을 제거하는 것을 목표로 합니다. - 정규화의 단계는 일반적으로 3NF까지 수행하지만 BCNF 적용하는 경우도 있습니다.
[ 더 알아보기 ] 💡데이터 무결성(Data Integrity)
- 데이터베이스 내의 데이터가 정확하고 일관되며 신뢰할 수 있는 상태를 유지한 상태를 의미합니다. - 이는 데이터의 정확성, 완전성, 일관성을 보장하여 데이터의 신뢰성을 유지하는 중요한 개념입니다.
1. 제1정규화(1NF) : 원자성
💡 제1정규화(1NF) : 원자성 - 모든 테이블의 컬럼의 값이 ‘원자값(Atomic Value)’만을 가지도록 하는 과정을 의미합니다. - 즉, 원자 값은 테이블 내에서 컬럼은 ‘단일한 값만을 가지도록 보장’하며 중복되는 여러 개의 값이 포함되지 않아야 합니다.
- 이를 통해 데이터 중복을 줄이고 데이터베이스를 보다 효율적으로 관리할 수 있습니다.
💡 사용 예시 : 제1정규화 적용 이전 → 제1정규화 적용 이후 - ‘상품 테이블’의 컬럼으로 ‘상품 소유자 정보’가 포함될 때, 데이터가 ‘홍길동, 김길동’과 같이 여러 개의 값으로 입력이 되어있다면, 제1 정규화에 위배가 됩니다. - 이 경우 소유자를 별도의 테이블로 분리하여 각 소유자가 하나의 행내에 표현되도록 해야합니다.
[ 더 알아보기 ] 💡 원자 값(Atomic Value)
- 데이터베이스 테이블의 각 컬럼이 더 이상 분해할 수 없는 단일 값을 가지는 것을 의미합니다. - 이는 각 컬럼이 하나의 값만을 포함하도록 보장하여 데이터의 중복을 방지하고, 데이터베이스의 무결성을 유지하는 데 중요한 역할을 합니다. - 예를 들어, 사용자 테이블 내의 “주소”라는 컬럼 내에 서울시 강남구"와 같은 값을 저장하는 대신, "도시", "구"와 같은 개별 컬럼으로 나누어 각각의 원자 값을 저장하는 것입니다. - 이를 통해 데이터베이스는 보다 효율적으로 관리되고, 쿼리의 성능도 향상될 수 있습니다.
2. 제2정규화(2NF) : 완전 함수적 종속 (부분 종속 제거)
💡 제2정규화(2NF) : 완전 함수적 종속 (부분 종속 제거)
- 제1정규화 조건을 만족하면서, 기본 키(PK)의 일부에만 종속된 ‘부분 종속성’을 제거하는 과정을 의미합니다. - 즉, 테이블의 모든 속성은 기본키에 종속이 되어야 한다는 것을 의미합니다.
- 즉, 기본키(PK)의 일부가 아닌 모든 속성은 기본 키(PK) 전체에 대해 종속되어야 합니다. 만약 기본키의 일부에만 종속되는 속성이 있다면 이를 위배하는 것입니다.
💡 사용 예시 : 제2정규화 적용 이전 → 제2정규화 적용 이후
- 기존 ‘계약 테이블’ 내에 ‘임대인 연락처’가 포함되어 있으면 임대인 정보가 계약의 일부에만 종속되는 경우이기에 제2정규화에 위배가 되기에 이를 테이블로 분리해야 합니다.
3. 제3정규화(3NF) : 이행 종속 제거
💡 제3정규화(3NF) : 이행 종속 제거
- 제2정규화 조건을 만족하면서, ‘이행적 종속'을 제거하는 과정을 의미합니다. A가 B를 결정하고 B가 C를 결정할 때, A가 C를 결정하는 관계를 의미합니다. - 즉, 기본키가 아닌 모든 속성 간에는 서로 종속될 수 없음을 의미합니다.
💡 사용예시 : 제3정규화(3NF) 적용 이전 → 제3정규화(3NF) 적용 이후
- ‘학생 테이블’에 학생 ID, 학과 ID, 학과 명 컬럼이 있을 때, 학과 ID가 학과명을 결정하고 학과명이 학생 ID에 종속된다면 이는 이행적 종속 관계입니다. - 이러한 경우, 제3정규화에 위배되기에 학과 정보를 별도의 테이블로 분리하여 종속성을 제거해야 합니다.
4. BCNF(Boyce and Codd Normal Form)
💡 BCNF(Boyce and Codd Normal Form) - 제3 정규형(3NF)의 강화된 형태로, 데이터베이스 테이블의 모든 결정자가 후보 키(candidate key)여야 한다는 조건을 추가로 요구합니다. 즉, 모든 결정자는 후보키에 속해야 함을 의미합니다. - 이 정규형은 제3 정규형을 만족하면서도 특정한 이상 현상을 방지하기 위해 정의되었습니다.
💡 사용예시 : BCNF(Boyce and Codd Normal Form) 적용 이전 → BCNF(Boyce and Codd Normal Form) 적용 이후
-학생 ID와 과목이 기본 키(primary key)입니다. 그러나 과목이 교수를 결정하는 관계가 있으므로, 과목은 결정자입니다. - 이 경우 과목이 후보 키(candidate key)가 아니기 때문에 BCNF를 위배하게 됩니다.
5. 제4정규화(4NF): 다치 종속성 제거
💡 제4정규화(4NF) : 다치 종속성 제거
- 제3정규화와 BCNF를 만족하면서, 다치 종속성(Multi-valued dependency)을 제거하는 과정을 의미합니다. 이는 하나의 속성이 여러 개의 값을 가질 수 있는 경우를 의미합니다. - 다치 종속성을 제거하기 위해서는 해당 속성을 별도의 테이블로 분리하여 각 속성이 단일 값을 가지도록 해야 합니다. 이를 통해 데이터베이스의 무결성과 일관성을 유지할 수 있습니다.
💡 사용예시 : 제4정규화 적용 이전 → 제4정규화 적용 이후
- '학생 테이블'에 학생 ID, 전화번호, 과목 컬럼이 있습니다. - 한 학생이 여러 개의 전화번호와 여러 개의 과목을 가질 수 있으며, 전화번호와 과목 간에 다치 종속성이 발생합니다.
6. 제5정규화(5NF): 조인 종속성 제거
💡 제5정규화(5NF) : 조인 종속성 제거
- 제4정규화를 만족하면서, 조인 종속성(Join Dependency)을 제거하는 과정을 의미합니다. 이는 하나의 릴레이션을 여러 개의 작은 릴레이션으로 분해할 때, 이들 작은 릴레이션을 다시 조인하여 원래의 릴레이션을 복원할 수 있는 경우를 말합니다. - 이는 매우 고도의 정규화 단계로, 대부분의 실용적인 데이터베이스 설계에서는 제4정규화까지 적용한 후 제5정규화를 적용할 필요가 없는 경우가 많습니다.
💡 사용예시 : 제5정규화 적용 이전 → 제5정규화 적용 이후
- '프로젝트 테이블'에 프로젝트 ID, 직원 ID, 역할 컬럼이 있습니다. 한 프로젝트에 여러 직원이 여러 역할을 가질 수 있으며, 직원과 역할 간에 조인 종속성이 발생합니다. - 이를 해결하기 위해 '프로젝트-직원' 테이블과 '직원-역할' 테이블로 분리하여 각각의 관계를 독립적으로 다루도록 합니다.
3) 반(역) 정규화(Denormalization)
💡 반(역) 정규화(Denormalization)
- 데이터베이스의 성능을 최적화하기 위해 의도적으로 정규화된 데이터 구조를 일부 해제하는 과정입니다. 이는 데이터를 중복 저장하나 테이블을 합치는 등의 방법을 통해 데이터 접근 속도를 향상하는 것을 목표로 합니다.
- 조회 성능을 향상하기 위해 주로 사용되며, 데이터 무결성과 일관성을 유지하기 위해 업데이트, 삭제 등의 작업 시 추가적인 관리가 필요합니다.
1. 테이블 반(역) 정규화
💡 테이블 역 정규화
- 데이터베이스의 성능 최적화를 위해 의도적으로 정규화된 데이터 구조를 일부 해제하는 과정입니다. - 주로 조회 성능을 향상시키기 위해 사용되며, 데이터의 중복 저장, 테이블의 합병 등을 통해 데이터 접근 속도를 높이는 것을 목표로 합니다.
테이블 역 정규화 종류
설명
중복 테이블
- 특정 데이터를 빠르게 조회하기 위해 테이블 간의 병합을 하는 방식을 의미합니다. 테이블간의 관계를 가지며 수행하는 것이 아닌 테이블 간의 병합을 통해서 정규화된 데이터를 역 정규화하는 방식입니다.
통계 테이블
- 집계된 데이터나 요약된 데이터 정보를 포함한 테이블을 구성합니다. 대규모 데이터의 경우 집계 및 요약 정보를 읽어오는데 이를 줄이기 위해서 역 정규화를 수행하는 방식입니다. 단, 해당 방식은 매번 갱신을 해주어야 한다는 단점이 있습니다.
이력 테이블
- 특정 테이블의 모든 컬럼을 이용하는것이 아닌 일부 컬럼만 추가된 이력 테이블로 구성하는 방식입니다.
부분 테이블
- 데이터베이스의 일부 데이터만 저장하는 테이블로 특정 조건에 맞는 데이터만을 저장하여 조회 성능을 향상시키는 방법입니다. 특정기간의 데이터 혹은 특정 조건에 맞는 데이터로만 구성을 합니다.
💡 테이블 역 정규화 예시 1. 중복 테이블
- 특정 데이터를 빠르게 조회하기 위해 테이블 간의 병합을 하는 방식을 의미합니다. - 고객 테이블과 주문 테이블을 병합하여 고객과 주문 정보를 한 번에 조회할 수 있는 테이블을 생성합니다.
2. 통계 테이블
- 집계된 데이터나 요약된 데이터 정보를 포함한 테이블을 구성합니다. - 일일 판매량을 집계한 테이블을 별도로 생성하여 실시간으로 통계 테이블을 조회할 수 있도록 구현합니다.
3. 이력 테이블
- 특정 테이블의 모든 컬럼을 이용하는 것이 아닌 일부 컬럼만 추가된 이력 테이블로 구성하는 방식입니다. - 직원의 승진 이력이나 급여 변동 이력을 별도의 테이블로 관리합니다.
4. 부분 테이블
- 데이터베이스의 일부 데이터만 저장하는 테이블로 특정 조건에 맞는 데이터만을 저장하여 조회 성능을 향상하는 방법입니다. - 최근 6개월간의 주문 정보를 저장하는 테이블을 생성하여 조회 성능을 향상합니다.
2. 컬럼 반(역) 정규화
💡 컬럼 반(역) 정규화
- 성능 최적화를 위해 데이터베이스 테이블의 컬럼 구조를 변경하는 방식입니다. - 주로 데이터 조회 성능을 향상하기 위해 사용이 됩니다.
컬럼 역 정규화
설명
중복 컬럼
여러 컬럼으로 나누어 저장했던 데이터를 하나의 컬럼으로 합치는 방식입니다.
파생 컬럼
기존 컬럼을 기반으로 새로운 정보를 계산하여 저장하는 컬럼을 추가합니다.
결합 컬럼
여러 개의 관련된 데이터를 하나의 컬럼에 결합하여 저장하는 방식입니다.
💡 컬럼 역 정규화 예시 1. 중복 컬럼
- 여러 컬럼으로 나누어 저장했던 데이터를 하나의 컬럼으로 합치는 방식입니다. - 예를 들어, 전화번호를 지역번호, 중간번호, 마지막 번호로 나누어 저장했던 것을 하나의 컬럼으로 합치는 경우입니다.
2. 파생 컬럼 - 기존 컬럼을 기반으로 새로운 정보를 계산하여 저장하는 컬럼을 추가합니다. - 예를 들어, '판매 가격'과 '세금' 컬럼을 기반으로 '총 가격' 컬럼을 추가하는 경우입니다.
3. 결합 컬럼
- 여러 개의 관련된 데이터를 하나의 컬럼에 결합하여 저장하는 방식입니다. - 예를 들어, '주소' 데이터를 '도시', '구', '동'으로 나누어 저장했던 것을 하나의 '주소' 컬럼에 결합하여 저장하는 경우입니다.
3. 관계 반(역) 정규화
💡 관계 반(역) 정규화 - 데이터베이스의 성능을 최적화하기 위해 테이블 간의 관계를 단순화하거나 병합하는 과정을 의미합니다. 이는 조회 성능을 향상하기 위해 주로 사용됩니다.
- 관계 반정규화를 통해 조인 연산을 줄이고, 데이터 접근 속도를 높일 수 있습니다. 그러나 데이터의 중복 저장과 같은 단점이 있으며, 데이터 무결성을 유지하기 위해 추가적인 관리가 필요합니다.
💡 관계 반(역) 정규화 예시 1. 테이블 병합
- 여러 개의 테이블을 하나의 테이블로 병합하여 조회 성능을 향상시킵니다. - 예를 들어, 고객 정보 테이블과 주문 정보 테이블을 병합하여 고객과 주문 정보를 한 번에 조회할 수 있도록 합니다.
2. 중복 관계 추가
- 자주 조회되는 데이터를 다른 테이블에 중복 저장하여 조회 성능을 높입니다. - 예를 들어, 주문 테이블에 고객의 이름과 연락처 정보를 추가로 저장하여 주문 조회 시 고객 정보를 빠르게 참조할 수 있도록 합니다.
