아키텍쳐 설계 원리

DB 설계 원칙 기술드립니다.

1. 데이터베이스 설계 원칙

1.1 정규화(Normalization)

정규화는 데이터의 중복을 최소화하고 일관성을 유지하는 과정입니다.

정규화 단계

1. 제1정규형(1NF): 셀에는 한 개 값만 있어야 합니다.
2. 제2정규형(2NF): 1NF를 만족하면서 기본키(Primary Key)의 일부에만 의존하는 칼럼은 분리
3. 제3정규형(3NF): 2NF를 만족하면서 기본키 아닌 다른 컬럼에 의존하는 컬럼도 분리

예시:

비정규화 테이블:

주문ID | 고객명 | 상품목록 (쉼표로 구분) | 총 가격
------------------------------------------------
1001  | 홍길동 | 아이폰, 에어팟 | 1500000
1002  | 이영희 | 갤럭시, 버즈 | 1300000

제1정규형 적용: (상품 데이터를 별도 테이블로 분리)

주문ID | 고객명  | 총 가격
----------------------
1001  | 홍길동  | 1500000
1002  | 이영희  | 1300000

주문ID | 상품명
----------------
1001  | 아이폰
1001  | 에어팟
1002  | 갤럭시
1002  | 버즈

1.2 반정규화(Denormalization)

반정규화는 성능을 위해 일부 정규화 규칙을 완화하는 과정입니다.

예시:

• 고객 정보와 주문 정보를 조인하는 조회가 많다면, 고객명과 이메일을 주문 테이블에 중복 저장할 수 있음.

-> 기본적으로 정규화 원칙을 따라서 테이블 설계를 하고요 실시간 응답 속도가 매우 중요한 케이스 같은 테이블에 한해서 부분적으로 반정규화를 하길 권장드립니다.

1.3 무결성 제약 조건(Integrity Constraints)

1. 기본키(Primary Key, PK): 각 행을 고유하게 식별하는 컬럼
2. 외래키(Foreign Key, FK): 다른 테이블의 기본키를 참조하는 컬럼
3. 고유성 제약(UNIQUE): 특정 컬럼의 값이 중복되지 않도록 보장
4. NOT NULL: 컬럼 값이 반드시 존재해야 함
5. CHECK: 조건 만족하는 값만 허용

예시:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL
);

1.4 트랜잭션과 동시성 관리

트랜잭션(Transaction)은 데이터베이스에서 하나의 작업 단위로 수행되는 연산 묶음입니다. 트랜잭션은 ACID 특성을 만족해야 합니다.

ACID 특성

• Atomicity (원자성): 트랜잭션 내의 작업은 모두 수행되거나, 전혀 수행되지 않아야 함
• Consistency (일관성): 트랜잭션 실행 전후에 데이터베이스의 일관성 유지
• Isolation (격리성): 동시에 여러 트랜잭션이 수행될 때, 각각의 트랜잭션은 독립적으로 수행돼야 함
• Durability (지속성): 트랜잭션이 성공적으로 완료되면, 그 결과는 영구적으로 저장됨

사용 목적

• 데이터의 무결성 보장
• 여러 단계의 작업을 하나의 단위로 묶어 일관성 유지
• 중간 오류 발생 시 롤백하여 데이터 보호

실제 사용 사례

1. 은행 이체

• 계좌 A에서 돈을 출금하고, 계좌 B에 입금

BEGIN;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 'B';
COMMIT;

• 위 작업 중 하나라도 실패하면 전체 작업을 ROLLBACK 처리

2. 쇼핑몰 주문 처리

• 주문 생성 → 재고 차감 → 결제 완료 → 배송 준비
• 중간에 오류가 나면 주문을 취소하고 재고 복구

3. WMS(창고관리시스템)에서 입고 처리

• 입고 등록 → 재고 수량 증가 → 로트번호 등록
• 어느 단계에서 실패하면 전체 롤백하여 재고 데이터 이상 방지

남발 시 문제점

주의할 점

• 트랜잭션 범위를 짧게 유지: 최소한의 작업만 포함
• 필요한 곳에만 사용: 중요한 변경 작업(UPDATE, INSERT, DELETE)에만
• 복합 로직은 예외/오류 처리를 함께 설계

트랜잭션 관련 명령어

BEGIN; -- 트랜잭션 시작
COMMIT; -- 트랜잭션 성공 시 반영
ROLLBACK; -- 실패 시 되돌리기
SAVEPOINT sp1; -- 중간 저장 지점 설정
ROLLBACK TO sp1; -- 해당 지점까지 롤백

정리 트랜잭션은 DB 작업에서 데이터의 안정성과 일관성을 보장하기 위한 핵심 개념입니다. 꼭 필요한 경우에만 사용하고, 짧고 명확하게 관리해야 합니다.

1.5 인덱스(Index) 최적화

• 인덱스는 테이블의 특정 컬럼에 대해 검색 속도를 빠르게 하기 위한 자료 구조입니다.

• 책의 목차와 같은 개념으로, 데이터를 빠르게 찾을 수 있도록 돕습니다.

• 기본키 인덱스: 기본키(PK) 자동 생성

-- 기본적으로 id에 클러스터형 인덱스가 생성됨
SELECT * FROM users WHERE id = 10;

• 일반 인덱스(Secondary Index)

CREATE INDEX idx_name ON users(name);

-- 인덱스를 사용하는 SELECT
-- 이름으로 검색하는 겨웅 빠르게 탐색 가능
SELECT * FROM users WHERE name = '철수';

• 복합 인덱스(Secondary Index)

CREATE INDEX idx_name_created ON users(name, created_at);

-- 아래 쿼리는 인덱스 사용 가능
SELECT * FROM users WHERE name = '철수' AND created_at >= '2024-01-01';

-- 아래는 인덱스 일부만 사용 → 효율 떨어짐
SELECT * FROM users WHERE created_at >= '2024-01-01';

-- 선두 컬럼(name)부터 조건에 있어야 복합 인덱스가 온전히 사용됨

예시:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

• 다음과 같은 조건에서 인덱스 사용이 유리합니다:

WHERE 조건에 자주 사용되는 컬럼

SELECT * FROM users WHERE email = 'test@example.com';

→ email 컬럼에 인덱스가 있으면 빠르게 탐색 가능

JOIN에 사용되는 컬럼

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

→ orders.customer_id 또는 customers.id 컬럼에 인덱스 필요

ORDER BY에 자주 사용되는 컬럼

SELECT * FROM products ORDER BY created_at DESC;

→ created_at 인덱스를 이용해 정렬 성능 향상

GROUP BY에 사용되는 컬럼

SELECT category, COUNT(*) FROM products GROUP BY category;

→ category에 인덱스가 있으면 더 빠르게 그룹핑 처리

실제 현업 예시

1. 물류 시스템

• 출고 처리 시 order_id로 주문 상세 조회
• orders(order_id) 인덱스 필요

2. 쇼핑몰

• products 테이블에서 category_id, price, brand 조건 검색
• 해당 컬럼들에 복합 인덱스 적용 (예: (category_id, brand, price))

3. 관리 시스템(Admin)

• 유저 검색 시 email, phone_number, user_id 조건 검색
• 각각에 대해 단일 인덱스 또는 email + phone_number 복합 인덱스 사용

인덱스를 남발하면 안 되는 경우

1. INSERT, UPDATE, DELETE가 많은 테이블

• 인덱스는 데이터 변경 시마다 동기화 비용 발생 → 성능 저하
• 예: 실시간 로그 테이블, 트래픽 많은 게시글 뷰 카운트 테이블

2. 자주 바뀌는 값에 인덱스 설정

• status, login_state처럼 자주 변경되는 값은 인덱스 효율이 떨어짐

3. 데이터가 너무 적은 테이블

• 인덱스보다 전체 스캔이 더 빠를 수 있음 → 인덱스 의미 없음

4. 너무 많은 인덱스 생성

• 쓰기 성능 저하 + 쿼리 옵티마이저가 잘못된 인덱스 선택할 수 있음

인덱스 사용 팁

• 조회 빈도와 데이터 변경 빈도를 함께 고려
• 자주 사용되는 쿼리를 기준으로 인덱스 설계
• EXPLAIN 명령어로 인덱스가 잘 사용되는지 확인

1.6 관계(Relationship) 설정

RDBMS(Relational Database Management System)에서의 관계 설정은 테이블 간의 연결 고리를 만드는 작업입니다. 관계 설정을 통해 데이터 정합성을 유지하고, 복잡한 데이터를 효율적으로 조회하고 관리할 수 있습니다.

관계의 종류

1. 일대일 (One-to-One)

• 정의: A 테이블의 한 레코드는 B 테이블의 한 레코드와만 연결됩니다.
• 예시: User 테이블과 UserProfile 테이블
  • 하나의 유저는 하나의 프로필만 가질 수 있음
• 사용 시점: 보안 분리, 확장성 고려 등으로 일부 정보만 별도 분리할 때

2. 일대다 (One-to-Many)

• 정의: A 테이블의 한 레코드는 B 테이블의 여러 레코드와 연결될 수 있습니다.
• 예시: Customer와 Orders
  • 하나의 고객은 여러 개의 주문을 할 수 있음
• 사용 시점: 가장 일반적인 형태, 대부분의 실무 데이터 모델이 이 구조를 가짐

3. 다대다 (Many-to-Many)

• 정의: A와 B 테이블 모두 서로 다수와 연결될 수 있음
• 예시: Students와 Courses
  • 하나의 학생은 여러 과목을 수강할 수 있고, 하나의 과목도 여러 학생이 수강할 수 있음
• 중간 테이블: StudentCourse

  CREATE TABLE StudentCourse (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
  );
  

• 사용 시점: 두 테이블 모두 다수로 연결될 필요가 있을 때, 보통 중간 테이블로 구현

실제 현업 예시

1. 전자상거래

• Users <-> Orders (1:N)
• Products <-> Orders (M:N, 중간 테이블로 OrderDetails 활용)

2. 병원 관리 시스템

• Doctors <-> Appointments (1:N)
• Patients <-> Appointments (1:N)
• Doctors <-> Specializations (M:N)

3. WMS(창고관리시스템)

• Warehouses <-> Items (1:N)
• Items <-> InventoryMovements (1:N)

관계 설정 남발 주의사항

정리

• 관계 설정은 데이터 정합성과 구조화를 위해 반드시 필요하지만,
• 남용하면 유지보수와 성능 면에서 부담이 될 수 있음
• 도메인 모델에 기반하여 필수적인 관계만 설정하는 것이 핵심

관계 설정 시 체크리스트

• 비즈니스 로직상 반드시 필요한 연결인가?
• 추후 변경 또는 확장 가능성을 고려했는가?
• 외래키 설정 대신 애플리케이션 단 무결성 검증이 더 나은가?

관계 설정은 '잘 하는 것보다, 덜 잘못하는 것'이 중요합니다.

1.7 뷰(View) 활용

SQL에서 **뷰(View)**는 하나 이상의 테이블을 기반으로 한 가상의 테이블입니다. 뷰 자체는 데이터를 저장하지 않고, 정의된 쿼리를 실행하여 결과를 반환합니다.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

뷰의 특징

• 실제 데이터를 저장하지 않음
• SELECT 쿼리로 정의됨
• 일반 테이블처럼 SELECT, JOIN 등으로 사용 가능
• 일부 DBMS는 뷰에 대해 DML(INSERT/UPDATE/DELETE)도 허용함 (단, 제약 존재)

뷰를 사용하는 이유 (실제 사용 시점)

1. 복잡한 쿼리 단순화

• 자주 사용하는 복잡한 조인/서브쿼리를 뷰로 만들어두고 재사용

-- 복잡한 쿼리를 단순화된 이름으로 사용
SELECT * FROM user_order_summary_view;

2. 보안 목적 (열/행 제한)

• 민감한 데이터가 포함된 테이블에서 특정 컬럼만 노출되도록 뷰 구성

-- 고객 연락처는 제외하고 조회
CREATE VIEW safe_customer_view AS
SELECT id, name FROM customers;

3. 계층적 접근 제어

• 부서/직급에 따라 다른 데이터를 조회할 수 있도록 뷰로 구성

4. DB 마이그레이션/인터페이스 표준화

• 하위 호환성 유지를 위해 기존 인터페이스와 동일한 스키마의 뷰 제공

현업에서 자주 사용하는 예시

뷰 남발하면 안 되는 경우

1. 지나치게 중첩된 뷰

• 뷰 안에서 또 다른 뷰를 참조하면 성능 저하 및 디버깅 어려움 발생

2. 실시간 데이터 성능 요구 시스템

• 뷰는 실행 시마다 SELECT 쿼리를 실행하므로 속도 이슈가 생길 수 있음

3. 뷰를 너무 많게 관리

• 뷰가 너무 많으면 오히려 관리 비용 증가, 로직 파악 어려움

4. 뷰를 테이블처럼 INSERT/UPDATE 시도

• 일부 경우엔 DML 불가능하거나 오류 유발

요약

• 뷰는 가상 테이블로, 복잡한 쿼리를 단순화하거나 보안을 강화할 때 유용
• 과용 시 오히려 성능과 유지보수에 악영향
• 현업에서는 요약, 권한 제한, 인터페이스 호환 등 다양한 목적으로 활용됨

꼭 필요한 경우에만, 관리 가능한 수준으로 뷰를 설계합시다~

1.8 스토어드 프로시저(Stored Procedure)

• **Stored Procedure(저장 프로시저)**는 데이터베이스 내에 저장된 SQL 문의 집합으로, 미리 컴파일된 일련의 SQL 문을 이름을 통해 호출하여 실행할 수 있는 재사용 가능한 프로시저입니다.
• 일반적으로 복잡한 로직, 반복적인 쿼리 작업을 함수처럼 캡슐화하여 사용합니다.

Stored Procedure 특징

사용이 적절한 경우

1. 복잡한 트랜잭션 처리
   • 예: 주문 처리 → 재고 차감 → 주문 로그 저장 → 결제 처리 등 순차 처리 필요 시
2. 다중 테이블에 걸친 작업 반복 시
   • 예: 매월 정기적으로 데이터 정리, 통계 계산 등 배치 처리
3. 로직의 중앙 집중화
   • 여러 어플리케이션에서 동일 로직 공유 필요할 때
4. 보안 및 접근 제어 목적
   • DB 구조를 감추고 제한된 기능만 공개하고 싶을 때

현업 예시

남발하면 안 되는 경우

1. 단순 조회 쿼리에도 사용
   • SELECT 하나만 필요한데도 매번 프로시저를 쓰면 오히려 복잡성 증가
2. 비즈니스 로직을 과도하게 넣을 때
   • 어플리케이션에서 관리되어야 할 로직이 DB에 들어가면 유지보수가 어려워짐
3. 동적 SQL 남용
   • 보안상 취약점 가능성 (SQL Injection 등)
4. 버전 관리 어려움
   • 프로시저는 Git 등 형상관리 도구와의 통합이 쉽지 않아 추적이 어려움

정리

• Stored Procedure는 성능 개선, 보안 강화, 반복 작업 단순화에 유용하지만, 사용 목적이 명확하지 않거나 너무 많은 로직이 포함되면 오히려 독이 될 수 있음
• 비즈니스 로직은 가능하면 서비스 계층에서 처리하고, 프로시저는 핵심 처리 로직이나 반복 작업에 한정하는 것이 베스트 프랙티스입니다.

기본 문법 예시 (MySQL 기준)

DELIMITER //
CREATE PROCEDURE process_order(IN order_id INT)
BEGIN
    UPDATE orders SET status = 'completed' WHERE id = order_id;
    INSERT INTO order_logs(order_id, action) VALUES (order_id, 'Order Completed');
END //
DELIMITER ;

-- 호출: CALL process_order(123);

2. 주문·배송·결제 시스템 예제

2.1 테이블 설계

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    status ENUM('pending', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_price DECIMAL(10,2) NOT NULL CHECK (total_price >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL UNIQUE,
    status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
    payment_method ENUM('credit_card', 'paypal', 'bank_transfer') NOT NULL,
    transaction_id VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

2.2 주문 상세 내역 조회 뷰

CREATE VIEW order_details AS
SELECT o.id AS order_id, c.name AS customer_name, p.name AS product_name, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN customers c ON o.customer_id = c.id;

2.3 결제 완료 처리 프로시저

DELIMITER //
CREATE PROCEDURE CompletePayment(
    IN p_order_id INT
)
BEGIN
    UPDATE payments SET status = 'paid' WHERE order_id = p_order_id;
    UPDATE orders SET status = 'shipped' WHERE id = p_order_id;
END //
DELIMITER ;