Backend2024-12-05

데이터베이스 설계 모범 사례

효율적이고 확장 가능한 데이터베이스를 설계하기 위한 모범 사례를 소개합니다. 정규화, 인덱싱, 관계 설계 등 실무에서 바로 적용할 수 있는 가이드를 제공합니다.

박민수

Backend Engineer

데이터베이스 설계 모범 사례

소개

DB 스키마를 나중에 바꾸려면 마이그레이션·다운타임 부담이 커집니다. 리워드·이벤트·사용자 데이터를 다루면서 정규화·인덱스·관계 설계를 잘못했다가 고친 경험이 있어, 그때 기준으로 “미리 해두면 좋은” 설계 사례를 정리했습니다.

정규화 (Normalization)

정규화는 데이터 중복을 제거하고 데이터 무결성을 보장하는 핵심 기법입니다.

제1정규형 (1NF)

각 컬럼은 원자값(atomic value)만을 가져야 합니다.

sql

1-- 나쁜 예
2CREATE TABLE orders (
3  id INT PRIMARY KEY,
4  customer_name VARCHAR(100),
5  items VARCHAR(500) -- 여러 아이템이 쉼표로 구분됨
6);
7
8-- 좋은 예
9CREATE TABLE orders (
10  id INT PRIMARY KEY,
11  customer_id INT,
12  order_date DATE
13);
14
15CREATE TABLE order_items (
16  id INT PRIMARY KEY,
17  order_id INT,
18  product_id INT,
19  quantity INT,
20  FOREIGN KEY (order_id) REFERENCES orders(id)
21);

제2정규형 (2NF)

부분 함수 종속을 제거합니다.

sql

1-- 나쁜 예: product_name이 product_id에만 종속
2CREATE TABLE order_items (
3  order_id INT,
4  product_id INT,
5  product_name VARCHAR(100), -- 부분 종속
6  quantity INT,
7  PRIMARY KEY (order_id, product_id)
8);
9
10-- 좋은 예
11CREATE TABLE products (
12  id INT PRIMARY KEY,
13  name VARCHAR(100),
14  price DECIMAL(10, 2)
15);
16
17CREATE TABLE order_items (
18  order_id INT,
19  product_id INT,
20  quantity INT,
21  PRIMARY KEY (order_id, product_id),
22  FOREIGN KEY (product_id) REFERENCES products(id)
23);

제3정규형 (3NF)

이행적 종속을 제거합니다.

sql

1-- 나쁜 예: category_name이 category_id에만 종속
2CREATE TABLE products (
3  id INT PRIMARY KEY,
4  name VARCHAR(100),
5  category_id INT,
6  category_name VARCHAR(100) -- 이행적 종속
7);
8
9-- 좋은 예
10CREATE TABLE categories (
11  id INT PRIMARY KEY,
12  name VARCHAR(100)
13);
14
15CREATE TABLE products (
16  id INT PRIMARY KEY,
17  name VARCHAR(100),
18  category_id INT,
19  FOREIGN KEY (category_id) REFERENCES categories(id)
20);

인덱싱 전략

인덱스는 쿼리 성능을 크게 향상시킬 수 있지만, 잘못 사용하면 쓰기 성능을 저하시킬 수 있습니다.

기본 인덱싱

sql

1-- 단일 컬럼 인덱스
2CREATE INDEX idx_user_email ON users(email);
3
4-- 복합 인덱스 (자주 함께 조회되는 컬럼)
5CREATE INDEX idx_order_user_date ON orders(user_id, order_date);
6
7-- 고유 인덱스
8CREATE UNIQUE INDEX idx_user_username ON users(username);

인덱스 선택 가이드

WHERE 절에서 자주 사용되는 컬럼
JOIN 조건에 사용되는 컬럼
ORDER BY나 GROUP BY에 사용되는 컬럼
카디널리티가 높은 컬럼 (고유 값이 많은 컬럼)

sql

1-- 카디널리티가 높은 컬럼에 인덱스 생성
2CREATE INDEX idx_user_email ON users(email); -- email은 고유값이 많음
3
4-- 카디널리티가 낮은 컬럼은 인덱스 효과가 적음
5-- CREATE INDEX idx_user_gender ON users(gender); -- gender는 2-3개 값만 가짐

복합 인덱스 순서

복합 인덱스의 컬럼 순서는 중요합니다. 가장 선택적인(카디널리티가 높은) 컬럼을 앞에 배치합니다.

sql

1-- 좋은 예: user_id가 더 선택적이므로 앞에 배치
2CREATE INDEX idx_order_user_date ON orders(user_id, order_date);
3
4-- 나쁜 예: order_date가 덜 선택적
5CREATE INDEX idx_order_date_user ON orders(order_date, user_id);

관계 설계

일대다 (One-to-Many)

가장 일반적인 관계입니다.

sql

1CREATE TABLE users (
2  id INT PRIMARY KEY,
3  name VARCHAR(100)
4);
5
6CREATE TABLE orders (
7  id INT PRIMARY KEY,
8  user_id INT,
9  order_date DATE,
10  FOREIGN KEY (user_id) REFERENCES users(id)
11);

다대다 (Many-to-Many)

중간 테이블(join table)을 사용합니다.

sql

1CREATE TABLE students (
2  id INT PRIMARY KEY,
3  name VARCHAR(100)
4);
5
6CREATE TABLE courses (
7  id INT PRIMARY KEY,
8  title VARCHAR(100)
9);
10
11CREATE TABLE student_courses (
12  student_id INT,
13  course_id INT,
14  enrollment_date DATE,
15  PRIMARY KEY (student_id, course_id),
16  FOREIGN KEY (student_id) REFERENCES students(id),
17  FOREIGN KEY (course_id) REFERENCES courses(id)
18);

일대일 (One-to-One)

sql

1CREATE TABLE users (
2  id INT PRIMARY KEY,
3  username VARCHAR(100)
4);
5
6CREATE TABLE user_profiles (
7  user_id INT PRIMARY KEY,
8  bio TEXT,
9  avatar_url VARCHAR(255),
10  FOREIGN KEY (user_id) REFERENCES users(id)
11);

데이터 타입 선택

적절한 데이터 타입 선택은 저장 공간과 성능에 영향을 미칩니다.

숫자 타입

sql

1-- 작은 정수: TINYINT (-128 to 127)
2CREATE TABLE status_flags (
3  id TINYINT PRIMARY KEY
4);
5
6-- 일반 정수: INT
7CREATE TABLE users (
8  id INT PRIMARY KEY
9);
10
11-- 큰 정수: BIGINT
12CREATE TABLE transactions (
13  id BIGINT PRIMARY KEY
14);
15
16-- 소수점: DECIMAL (정확한 계산 필요 시)
17CREATE TABLE products (
18  id INT PRIMARY KEY,
19  price DECIMAL(10, 2) -- 정확한 금액 계산
20);

문자열 타입

sql

1-- 고정 길이: CHAR (길이가 항상 동일한 경우)
2CREATE TABLE country_codes (
3  code CHAR(2) PRIMARY KEY -- 'US', 'KR' 등
4);
5
6-- 가변 길이: VARCHAR (길이가 가변적인 경우)
7CREATE TABLE users (
8  username VARCHAR(50)
9);
10
11-- 긴 텍스트: TEXT
12CREATE TABLE articles (
13  id INT PRIMARY KEY,
14  content TEXT
15);

날짜/시간 타입

sql

1-- 날짜만: DATE
2CREATE TABLE events (
3  id INT PRIMARY KEY,
4  event_date DATE
5);
6
7-- 날짜와 시간: DATETIME
8CREATE TABLE orders (
9  id INT PRIMARY KEY,
10  created_at DATETIME
11);
12
13-- 타임스탬프: TIMESTAMP (자동 업데이트)
14CREATE TABLE posts (
15  id INT PRIMARY KEY,
16  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
17  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
18);

제약 조건 (Constraints)

제약 조건은 데이터 무결성을 보장합니다.

기본 키 (Primary Key)

sql

1CREATE TABLE users (
2  id INT PRIMARY KEY,
3  -- 또는
4  id INT,
5  PRIMARY KEY (id)
6);
7
8-- 복합 기본 키
9CREATE TABLE order_items (
10  order_id INT,
11  product_id INT,
12  quantity INT,
13  PRIMARY KEY (order_id, product_id)
14);

외래 키 (Foreign Key)

sql

1CREATE TABLE orders (
2  id INT PRIMARY KEY,
3  user_id INT,
4  FOREIGN KEY (user_id) REFERENCES users(id)
5    ON DELETE CASCADE  -- 사용자 삭제 시 주문도 삭제
6    ON UPDATE CASCADE  -- 사용자 ID 변경 시 주문도 업데이트
7);

체크 제약 조건 (Check Constraint)

sql

1CREATE TABLE products (
2  id INT PRIMARY KEY,
3  price DECIMAL(10, 2) CHECK (price > 0),
4  stock INT CHECK (stock >= 0),
5  status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'archived'))
6);

고유 제약 조건 (Unique Constraint)

sql

1CREATE TABLE users (
2  id INT PRIMARY KEY,
3  email VARCHAR(100) UNIQUE,
4  username VARCHAR(50) UNIQUE
5);

성능 최적화

파티셔닝

대용량 테이블을 여러 파티션으로 분할합니다.

sql

1-- 날짜별 파티셔닝
2CREATE TABLE orders (
3  id INT,
4  user_id INT,
5  order_date DATE,
6  amount DECIMAL(10, 2)
7) PARTITION BY RANGE (YEAR(order_date)) (
8  PARTITION p2022 VALUES LESS THAN (2023),
9  PARTITION p2023 VALUES LESS THAN (2024),
10  PARTITION p2024 VALUES LESS THAN (2025),
11  PARTITION p_future VALUES LESS THAN MAXVALUE
12);

쿼리 최적화

sql

1-- 나쁜 예: SELECT *
2SELECT * FROM users WHERE email = 'user@example.com';
3
4-- 좋은 예: 필요한 컬럼만 선택
5SELECT id, name, email FROM users WHERE email = 'user@example.com';
6
7-- 나쁜 예: 함수를 WHERE 절에서 사용
8SELECT * FROM orders WHERE YEAR(order_date) = 2024;
9
10-- 좋은 예: 인덱스를 활용할 수 있도록
11SELECT * FROM orders 
12WHERE order_date >= '2024-01-01' 
13  AND order_date < '2025-01-01';

보안 고려사항

SQL Injection 방지

sql

1-- 나쁜 예: 문자열 연결
2-- "SELECT * FROM users WHERE email = '" + email + "'"
3
4-- 좋은 예: 파라미터화된 쿼리
5SELECT * FROM users WHERE email = ?;

권한 관리

sql

1-- 읽기 전용 사용자 생성
2CREATE USER 'readonly'@'%' IDENTIFIED BY 'password';
3GRANT SELECT ON database_name.* TO 'readonly'@'%';
4
5-- 애플리케이션 사용자 (제한된 권한)
6CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
7GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'app_user'@'%';
8-- DELETE 권한은 필요시에만 부여

모니터링 및 유지보수

느린 쿼리 로깅

sql

1-- MySQL
2SET GLOBAL slow_query_log = 'ON';
3SET GLOBAL long_query_time = 1; -- 1초 이상 걸리는 쿼리 로깅
4
5-- PostgreSQL
6ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1초

통계 정보 업데이트

sql

1-- MySQL
2ANALYZE TABLE users;
3
4-- PostgreSQL
5ANALYZE users;

결론

효율적인 데이터베이스 설계를 위해 다음 원칙을 따릅니다:

정규화: 데이터 중복 제거 및 무결성 보장
적절한 인덱싱: 쿼리 성능 향상
올바른 데이터 타입: 저장 공간 및 성능 최적화
제약 조건 활용: 데이터 무결성 보장
성능 모니터링: 지속적인 최적화

이러한 모범 사례를 따르면 확장 가능하고 유지보수하기 쉬운 데이터베이스를 구축할 수 있습니다.

데이터베이스 설계 모범 사례

데이터베이스 설계 모범 사례

소개

정규화 (Normalization)

제1정규형 (1NF)

제2정규형 (2NF)

제3정규형 (3NF)

인덱싱 전략

기본 인덱싱

인덱스 선택 가이드

복합 인덱스 순서

관계 설계

일대다 (One-to-Many)

다대다 (Many-to-Many)

일대일 (One-to-One)

데이터 타입 선택

숫자 타입

문자열 타입

날짜/시간 타입

제약 조건 (Constraints)

기본 키 (Primary Key)

외래 키 (Foreign Key)

체크 제약 조건 (Check Constraint)

고유 제약 조건 (Unique Constraint)

성능 최적화

파티셔닝

쿼리 최적화

보안 고려사항

SQL Injection 방지

권한 관리

모니터링 및 유지보수

느린 쿼리 로깅

통계 정보 업데이트

결론

참고 자료

공유하기

관련 포스트

GPT API를 활용한 자연어 처리 최적화

Next.js 14의 새로운 기능 살펴보기

SQL JOIN 완벽 가이드: 실무 예제와 쿼리 최적화 팁