효과적인 데이터베이스 설계 원칙

 

 

출처: https://www.linkedin.com/pulse/mastering-art-database-design-principles-success-imbiruka-clement/

 

 

효과적인 데이터베이스 설계는 애플리케이션의 성능, 확장성, 유지보수성에 직접적인 영향을 미칩니다. 잘 설계된 데이터베이스는 데이터 중복을 최소화하고, 데이터 무결성을 보장하며, 쿼리 성능을 최적화할 수 있습니다.

 

데이터베이스 설계의 핵심 원칙인 정규화와 역정규화, 그리고 스키마 설계 베스트 프랙티스에 대해 알아보고자합니다.

 

 

---

 

정규화(Normalization)

 

정규화의 목적과 중요성

정규화(Normalization)는 데이터 중복을 최소화하고 데이터 무결성을 유지하기 위해 데이터를 분할하는 과정입니다. 정규화를 통해 데이터베이스의 효율성을 높이고, 이상(anomaly)을 방지할 수 있습니다. 정규화는 여러 단계로 이루어지며, 각 단계는 특정 규칙을 따릅니다.

 

정규형 단계

제1정규형(1NF)

제1정규형(1NF)은 모든 테이블이 원자값(atomic value)을 가져야 한다는 규칙입니다. 즉, 각 필드는 더 이상 분할할 수 없는 단일 값을 가져야 합니다.

예제:

-- 1NF 위반 테이블
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_ids VARCHAR(255) -- 다중 값
);

-- 1NF 준수 테이블로 분할
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT
);

CREATE TABLE OrderProducts (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

 

제2정규형(2NF)

 

제2정규형(2NF)은 제1정규형을 만족하면서, 모든 비키 속성이 기본키에 완전 함수적 종속을 가져야 한다는 규칙입니다. 기본키의 일부분에만 종속된 속성이 없어야 합니다.

예제:

-- 2NF 위반 테이블 (복합 키에서 일부 속성이 종속)
CREATE TABLE OrderDetails (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),
    PRIMARY KEY (order_id, product_id)
);

-- 2NF 준수 테이블로 분할
CREATE TABLE OrderDetails (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

 

제3정규형(3NF)

 

제3정규형(3NF)은 제2정규형을 만족하면서, 모든 비키 속성이 이행적 종속을 가지지 않아야 한다는 규칙입니다. 즉, 비키 속성 간의 종속성이 없어야 합니다.

예제:

-- 3NF 위반 테이블 (비키 속성 간 이행적 종속)
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100)
);

-- 3NF 준수 테이블로 분할
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

 

정규화의 예제

 

예제 시나리오: 학생과 과목의 관계를 관리하는 데이터베이스

 

1. 비정규화된 테이블:

CREATE TABLE StudentSubjects (
    student_id INT,
    student_name VARCHAR(100),
    subject_id INT,
    subject_name VARCHAR(100),
    teacher_name VARCHAR(100)
);

 

2. 제1정규형(1NF) 적용:

 

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE Subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(100),
    teacher_name VARCHAR(100)
);

CREATE TABLE StudentSubjects (
    student_id INT,
    subject_id INT,
    PRIMARY KEY (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id)
);

 

 

3. 제2정규형(2NF) 적용:

• 이미 제1정규형을 만족하므로 추가적인 분할은 필요하지 않습니다.

 

---

 

역정규화(Denormalization)

 

역정규화의 필요성

역정규화(Denormalization)는 성능 향상을 위해 정규화된 테이블을 다시 합치는 과정입니다. 주로 읽기 성능을 높이기 위해 사용되며, 데이터 중복을 허용함으로써 조인(join) 연산의 빈도를 줄일 수 있습니다.

 

역정규화의 장단점

장점:

• 쿼리 성능 향상: 조인 연산을 줄여 쿼리 응답 시간을 단축할 수 있습니다.
• 단순한 쿼리: 복잡한 조인 없이 단순한 쿼리로 데이터를 조회할 수 있습니다.

단점:

• 데이터 중복: 동일한 데이터가 여러 테이블에 중복 저장되므로 저장 공간이 증가합니다.
• 데이터 일관성 관리: 중복된 데이터를 일관되게 유지하기 위해 추가적인 관리가 필요합니다.
• 업데이트 비용 증가: 데이터 업데이트 시 여러 테이블을 동시에 수정해야 할 수 있습니다.

 

역정규화의 예제

 

예제 시나리오: 주문 데이터 조회 성능 향상을 위한 역정규화

 

정규화된 테이블:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    contact_info VARCHAR(255)
);

 

역정규화된 테이블:

 

CREATE TABLE OrdersDenormalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    contact_info VARCHAR(255),
    order_date DATE
);

• 이점: OrdersDenormalized 테이블을 사용하면 고객 정보를 조인 없이 한 번에 조회할 수 있어 읽기 성능이 향상됩니다.
• 주의점: 고객 정보가 변경될 경우, OrdersDenormalized 테이블의 모든 관련 레코드를 업데이트해야 합니다.

 

---

 

스키마 설계 베스트 프랙티스

 

효과적인 데이터베이스 설계를 위해서는 다음과 같은 베스트 프랙티스를 따르는 것이 중요합니다.

 

일관된 네이밍 컨벤션

• 테이블 및 컬럼 이름: 명확하고 일관된 이름을 사용하여 가독성을 높입니다.
  • 예: Users, Orders, ProductID, created_at
• 카멜 케이스(CamelCase) vs 스네이크 케이스(Snake_Case): 팀 내에서 일관된 스타일을 선택하여 사용합니다.
  • 카멜 케이스: userName
  • 스네이크 케이스: user_name

 

적절한 데이터 타입 선택

• 메모리 사용 최적화: 필요한 최소한의 데이터 타입을 선택하여 메모리 사용을 최적화합니다.
  • 예: INT 대신 SMALLINT 사용 가능
• 데이터 무결성 유지: 적절한 데이터 타입을 선택하여 데이터의 정확성을 보장합니다.
  • 예: DATE, TIMESTAMP 타입 사용

-- 예제: 적절한 데이터 타입 선택
CREATE TABLE Products (
    product_id SMALLINT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0
);

인덱스 전략

• 주요 쿼리에 사용되는 컬럼에 인덱스 생성: 자주 조회되는 컬럼에 인덱스를 추가하여 검색 성능을 향상시킵니다.
• 복합 인덱스 사용: 여러 컬럼을 조합하여 쿼리 성능을 최적화합니다.
• 인덱스의 과도한 사용 피하기: 너무 많은 인덱스는 쓰기 성능을 저하시킬 수 있습니다.

-- 예제: 복합 인덱스 생성
CREATE INDEX idx_orders_customer_date ON Orders(customer_id, order_date);

외래 키(Foreign Key) 사용

• 데이터 무결성 보장: 외래 키를 사용하여 테이블 간의 관계를 명확히 하고, 참조 무결성을 유지합니다.
• Cascade 옵션 활용: 삭제나 업데이트 시 연관된 데이터를 자동으로 처리할 수 있도록 설정합니다.

-- 예제: 외래 키 설정
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE
);

유효성 검사 및 제약 조건

• NOT NULL 제약 조건: 필수적인 데이터를 보장합니다.
• UNIQUE 제약 조건: 특정 컬럼의 값이 고유함을 보장합니다.
• CHECK 제약 조건: 데이터의 유효성을 검증합니다.

-- 예제: 유효성 검사 제약 조건
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

 

 

---

 

 

효과적인 데이터베이스 설계는 애플리케이션의 성능과 유지보수성을 크게 향상시킬 수 있습니다. 정규화와 역정규화는 데이터 중복과 무결성을 관리하는 데 중요한 역할을 하며, 스키마 설계 베스트 프랙티스를 따름으로써 데이터베이스의 효율성과 안정성을 보장할 수 있습니다. 백엔드 개발자는 이러한 설계 원칙을 숙지하고, 실무에 적용함으로써 더 나은 애플리케이션을 개발할 수 있습니다.

 

 

 

 

참고 자료

https://www.geeksforgeeks.org/introduction-of-database-normalization/

Introduction of Database Normalization - GeeksforGeeks

 

https://www.geeksforgeeks.org/postgresql-foreign-key/

PostgreSQL - Foreign Key

 

https://dev.mysql.com/doc/refman/8.4/en/mysql-indexes.html

MySQL :: MySQL 8.4 Reference Manual :: 10.3.1 How MySQL Uses Indexes

 

https://vertabelo.com/blog/best-practices-for-database-design/

Top 11 Best Practices for Database Design