Koo Logo
로그인회원가입

데이터베이스 반정규화, 테이블 설계의 딜레마

개요

편의를 위한 설계와 유지보수를 위한 설계의 이해관계가 충돌할 경우(트레이드오프)가 있다 이럴 때 어떻게 조인을 관리해야하는지는 큰 문제이다. 다음은 order테이블과 product 테이블을 통해 두개의 테이블이 서로 조인해서 데이터를 추출해야하는 상황의 문제를 가정하고 해결하는 과정이다.

문제 상황: 편의를 위한 반정규화의 함정

초기 테이블 구조

들어가는 데이터의 성격을 고려하지 않고 조인하기 싫어서 테이블을 과하게 중복된 데이터를 양측에 집어넣다보면 다음과 같은 문제가 생긴다.

-- 제품 테이블 (반정규화된 구조)
-- 제품을 조회할때마다 고객사명이나, 배송상태까지 보려고 일부러 하나의 테이블에 담은경우
CREATE TABLE products (
    prod_no INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    -- 주문 정보를 제품 테이블에 포함
    ord_no INT,
    order_date DATE,
    customer_name VARCHAR(100),
    delivery_status VARCHAR(50)
);

-- 주문 테이블
CREATE TABLE orders (
    ord_no INT PRIMARY KEY,
    order_date DATE,
    customer_name VARCHAR(100),
    delivery_status VARCHAR(50),
    total_amount DECIMAL(10,2)
);

발생한 문제

이 구조는 처음에는 편리해 보였지만, 운영 중 심각한 문제가 발생했습니다:

  1. 재고 문제로 주문이 변경되는 경우
    • 주문 번호가 바뀌면 제품 테이블의 모든 관련 정보를 업데이트해야 함
    • 한 번의 주문 변경이 여러 테이블에 영향을 미침
  2. *데이터 일관성 문제
    • 같은 정보가 여러 곳에 존재하여 불일치 발생 가능
    • 어느 테이블의 정보가 최신인지 알 수 없음
  3. 유지보수의 어려움
    • 주문 정보 변경 시 누락되는 테이블 발생
    • 트랜잭션문제가 더 빈번하게 발생할 수 있음.

이를 통해 우리가 생각해야하는건 컬럼에 들어가는 값이 key값에 따라서 움직이는지를 꼭 확인해야한다. 고객명은 제품에 종속된 것처럼 보이지만 실제로는 주문에 종속된 존재이다. 이런 컬럼들이 테이블의 pk값하고 꼭 1대1 매칭되는지 고민해야 문제가 생기지 않는다.

해결책: 정규화를 통한 관계 재설계

1. 테이블 정규화

가장 근본적인 해결책은 테이블을 적절히 정규화하는 것입니다. 제품은 제품 정보만, 주문은 주문 정보만 관리하도록 분리합니다.

-- 제품 테이블: 제품 정보만 관리
CREATE TABLE products (
    prod_no INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    stock_quantity INT,
    category VARCHAR(50)
);

-- 주문 테이블: 주문 정보만 관리
CREATE TABLE orders (
    ord_no INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    delivery_status VARCHAR(50),
    shipping_address TEXT
);

-- 주문-제품 연결 테이블 (다대다 관계)
CREATE TABLE order_items (
    unique_id INT, -- 해당 테이블의 unique한 아이디.
    ord_no INT,
    prod_no INT,
    quantity INT,
    unit_price DECIMAL(10,2),  -- 주문 당시 가격
    discount_rate INT,
    PRIMARY KEY (ord_no, prod_no),
    FOREIGN KEY (ord_no) REFERENCES orders(ord_no),
    FOREIGN KEY (prod_no) REFERENCES products(prod_no)
);

이제 재고 문제로 주문이 변경되더라도, order_items 테이블만 업데이트하면 됩니다:

-- 주문 변경이 간단해짐
UPDATE order_items 
SET ord_no = @new_ord_no 
WHERE ord_no = @old_ord_no AND prod_no = @prod_no;

2. 조회 성능을 위한 View 활용

정규화 후 JOIN이 필요한 조회를 간편하게 하기 위해 View를 생성합니다:

-- 자주 사용되는 조회를 위한 View
CREATE VIEW v_product_orders AS
SELECT 
    p.prod_no,
    p.product_name,
    p.price as current_price,
    oi.ord_no,
    oi.quantity,
    oi.unit_price as order_price,
    o.order_date,
    o.customer_id,
    o.delivery_status
FROM products p
INNER JOIN order_items oi ON p.prod_no = oi.prod_no
INNER JOIN orders o ON oi.ord_no = o.ord_no;

-- 사용 예시
SELECT * FROM v_product_orders WHERE prod_no = 12345;

3. 성능 최적화를 위한 인덱싱

JOIN 성능을 향상시키기 위해 적절한 인덱스를 생성합니다:

-- 조인에 사용되는 컬럼에 인덱스 생성
CREATE INDEX idx_order_items_prod ON order_items(prod_no, ord_no);
CREATE INDEX idx_order_items_ord ON order_items(ord_no, prod_no);

-- 자주 조회되는 조건에 대한 복합 인덱스
CREATE INDEX idx_orders_date_status ON orders(order_date, delivery_status);

성능 비교: 정규화 vs 반정규화

조회 성능

-- 테스트 환경: 300만 건의 주문 데이터

-- 반정규화 (단일 테이블)
SELECT * FROM products WHERE prod_no = 12345;
-- 실행 시간: ~5-8ms

-- 정규화 (JOIN 사용)
SELECT p.*, oi.*, o.*
FROM products p
JOIN order_items oi ON p.prod_no = oi.prod_no
JOIN orders o ON oi.ord_no = o.ord_no
WHERE p.prod_no = 12345;
-- 실행 시간: ~15-20ms (인덱스 사용 시)

업데이트 성능

-- 반정규화: 상품명 변경 시 모든 주문 내역 업데이트
UPDATE products SET product_name = 'New Name' WHERE prod_no = 12345;
-- 영향받는 행: 평균 30개, 실행 시간: ~50-100ms

-- 정규화: 한 곳만 업데이트
UPDATE products SET product_name = 'New Name' WHERE prod_no = 12345;
-- 영향받는 행: 1개, 실행 시간: ~1ms

하이브리드 접근법: 필요한 곳에만 반정규화

모든 경우에 완전한 정규화가 정답은 아닙니다. 상황에 따라 전략적인 반정규화가 필요할 수 있습니다.

1. 읽기 전용 요약 테이블

-- 분석/리포팅용 요약 테이블 (일일 배치로 생성)
CREATE TABLE daily_product_summary (
    summary_date DATE,
    prod_no INT,
    product_name VARCHAR(100),
    total_orders INT,
    total_quantity INT,
    total_revenue DECIMAL(12,2),
    PRIMARY KEY (summary_date, prod_no)
);

2. Materialized View 활용 (PostgreSQL)

-- 자주 조회되는 집계 데이터를 미리 계산
CREATE MATERIALIZED VIEW mv_product_stats AS
SELECT 
    p.prod_no,
    p.product_name,
    COUNT(DISTINCT oi.ord_no) as total_orders,
    SUM(oi.quantity) as total_sold,
    AVG(oi.unit_price) as avg_selling_price
FROM products p
LEFT JOIN order_items oi ON p.prod_no = oi.prod_no
GROUP BY p.prod_no, p.product_name;

-- 주기적으로 갱신
REFRESH MATERIALIZED VIEW mv_product_stats;

3. 캐싱 레이어 도입

-- 자주 변경되지 않는 조회 결과를 캐시 테이블에 저장
CREATE TABLE product_cache (
    prod_no INT PRIMARY KEY,
    cache_data JSON,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 캐시 갱신 트리거
CREATE TRIGGER update_product_cache
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    DELETE FROM product_cache WHERE prod_no = NEW.prod_no;
END;

데이터베이스 설계를 위한 핵심 데이터 성격 파악 체크리스트

1. 데이터 변경 특성

1.1 변경 빈도

  • 불변: 한번 생성되면 변경되지 않음 (예: 주문 이력, 로그, 거래 내역)
  • 저빈도: 가끔 변경됨 (예: 사용자 정보, 상품 기본 정보)
  • 고빈도: 자주 변경됨 (예: 재고, 가격, 상태 정보)

1.2 버전 관리

  • 버전 관리 필요: 변경 이력을 추적해야 함
  • 버전 관리 불필요: 최신 상태만 유지

2. 데이터 관계 특성

2.1 관계의 카디널리티

  • 1:1 관계: 하나의 레코드가 다른 테이블의 하나의 레코드와만 연결
    • 필수적 1:1 (양쪽 모두 필수)
    • 선택적 1:1 (한쪽 또는 양쪽 선택적)
  • 1:N 관계: 하나의 레코드가 여러 레코드와 연결
    • 부모가 필수 (자식은 부모 없이 존재 불가)
    • 부모가 선택적 (자식이 독립적으로 존재 가능)
  • N:M 관계: 여러 레코드가 여러 레코드와 연결
    • 단순 연결 (추가 속성 없음)
    • 연결 엔티티 (연결 자체에 속성 있음)

3. 성능 특성

3.1 접근 패턴

  • 단건 조회: 주로 PK로 단일 레코드 조회
  • 범위 조회: 특정 범위의 여러 레코드 조회
  • 집계 연산: SUM, COUNT, AVG 등 빈번
  • 조인 빈도: 다른 테이블과 자주 조인
  • 전체 스캔: 테이블 전체 조회 빈번

3.2 데이터 규모

  • 소규모: 10만 건 이하
  • 중규모: 10만 ~ 1000만 건
  • 대규모: 1000만 건 이상

실제 적용 예시

예시 1: 주문 시스템

[주문 테이블]
✓ 변경 빈도: 불변
✓ 버전 관리: 불필요 (불변이므로)
✓ 1:N 관계: 고객-주문 (부모 필수)
✓ 접근 패턴: 단건 조회, 범위 조회
✓ 데이터 규모: 중규모

[주문 상세 테이블]
✓ 변경 빈도: 불변
✓ 버전 관리: 불필요
✓ N:M 관계: 주문-상품 (연결 엔티티)
✓ 접근 패턴: 조인 빈도 높음
✓ 데이터 규모: 대규모

[상품 테이블]
✓ 변경 빈도: 저빈도
✓ 버전 관리: 필요 (가격 변경 이력)
✓ 1:N 관계: 카테고리-상품 (부모 선택적)
✓ 접근 패턴: 단건 조회, 범위 조회
✓ 데이터 규모: 소규모

예시 2: 재고 관리 시스템

[재고 현황 테이블]
✓ 변경 빈도: 고빈도
✓ 버전 관리: 불필요 (별도 이력 테이블)
✓ 1:1 관계: 상품-재고 (필수적)
✓ 접근 패턴: 단건 조회, 집계 연산
✓ 데이터 규모: 소규모

[재고 변동 이력]
✓ 변경 빈도: 불변
✓ 버전 관리: 불필요 (자체가 이력)
✓ 1:N 관계: 상품-변동이력 (부모 필수)
✓ 접근 패턴: 범위 조회, 집계 연산
✓ 데이터 규모: 대규모

체크리스트 기반 설계 가이드

1. 변경 빈도에 따른 설계

  • 불변: 단일 테이블, 파티셔닝 고려
  • 저빈도: 기본 테이블 + 선택적 이력 테이블
  • 고빈도: 현재 상태 테이블 + 변경 이력 테이블 분리

2. 버전 관리 전략

  • 필요 시: 별도 버전 테이블 또는 이력 컬럼 추가
  • 불필요 시: 단순 UPDATE 처리

3. 관계별 인덱싱 전략

  • 1:1: 양방향 인덱스는 보통 불필요
  • 1:N: 자식 테이블의 FK에 인덱스 필수
  • N:M: 양쪽 FK 모두 인덱스, 복합 PK 고려

4. 접근 패턴별 최적화

  • 단건 조회: PK 인덱스로 충분
  • 범위 조회: 조회 조건 컬럼에 인덱스
  • 집계 연산: 집계 대상 컬럼 인덱스, 요약 테이블 고려
  • 조인 빈도: 조인 컬럼 인덱스, 반정규화 검토
  • 전체 스캔: 파티셔닝, 병렬 처리 고려

5. 규모별 고려사항

  • 소규모: 정규화 우선, 단순한 구조
  • 중규모: 선택적 인덱싱, 적절한 반정규화
  • 대규모: 파티셔닝, 샤딩, 요약 테이블 필수

이 체크리스트를 활용하여 각 엔티티의 특성을 파악하고, 그에 맞는 최적의 테이블 구조와 인덱싱 전략을 수립할 수 있습니다.