DB 설계

업무를 하는데 있어서 DB, 테이블을 설계할 일이 자주 있다.

물류의 이벤트들을 로그로 기록하고 이를 집계하여 재고의 상태를 추적하는 기능을 만들기 전에 DB를 설계하는 방법을 정리해보기로 한다.

1. 요구사항 뽀개기

설계는 ‘시스템이 무엇을 기록해야 하는가 ‘를 정의하는데서 시작된다.

기능 요구사항에서 뽑아내야 할 정보

•

명사: 엔티티 후보 (회원, 주문, 상품 등…)

•

동사: 관계와 트랜잭션 후보 (회원이 상품을 주문한다)

•

형용사/속성: 엔티티의 컬럼 후보 (회원 상태, 결제 상태, 주문 총액 등..)

비기능 요구사항에서 뽑아내야 할 정보

개인적으로 DB 설계측면에서는 기능 요구사항보다 더 중요하다고 생각함.

•

조회/쓰기의 비율: 사용자 기능은 대부분 조회 위주, 로그 수집은 쓰기의 비율이 높다.

•

데이터의 규모: 데이터가 하루에 쌓이는 양, 이것이 1년, 10년 가면 어떻게 될 것인가? 규모에 따라 설계는 달라지게 된다.

•

정합성 고려: 데이터가 얼마나 정합성을 요구하는지 따져야한다. (결제의 정합도는 100%여야 하고, 조회수 등은 정합성이 완벽할 필요까진 없다.)

•

조회 지연 가능여부: 실시간 조회인가 / 배치 집계인가

위 정보를 바탕으로 쿼리 패턴을 추측한다.

어떤 화면에서 얼마나 많은 사용자가 어떤 조건으로 어떤 빈도로 조회하는가? 이를 따져봐야 인덱스 설계와 역정규화 판단의 근거를 따지게 된다.

2. 개념 모델링 - 엔티티와 관계 (ERD)

엔티티의 식별기준

엔티티는 독립적으로 식별 가능하고 여러 인스턴스가 있으며 속성을 가지고 있는 것이다.

예를 들어, “주소” 라 함은 회원의 속성인가 엔티티로 둘 것인가?

→ 대부분의 케이스에서는 회원주소는 엔티티로 분리한다. 회원마다 주소가 1개라면 속성이지만 회원의 주소는 여러개를 가질 수도 있고 주문, 배송 등에서도 참조해야 하며 변경 등 이력을 추적해야 한다.

관계와 카디널리티

•

1:1 - 가장 드문 관계, 1:1이라면 주로 두 엔티티로 분리하지 않고 한 엔티티에서 속성으로 관리하는 편이 낫다. 회원의 상세정보나 보안 분리(비밀번호 등) 목적이라면 분리한다.

•

1:N - 가장 흔하고 N쪽에 1의 PK를 FK로 설정한다.

•

N:M - 반드시 중앙에 관계테이블을 설계하여 매핑한다. 중간 테이블에 관계 자체의 속성 등을 넣어야 하므로 처음부터 엔티티로 분리하는 편이 나았었다.

선택적/필수적 참여

주문은 반드시 회원이 있어야 존재할 수 있는가? → 비회원 주문이 존재할 경우 주문의 회원 FK는 nullable로 설정이 필요하거나 별도 설계가 필요하다.(비회원 주문 테이블 독립 설계)

서브타입 처리

결제수단(카드, 포인트, 계좌)처럼 공통 속성 + 타입별 속성이 있을때 설계적으로 고민해봐야 한다.

•

한 테이블에서 타입으로 분류: 단순하지만 NULL인 컬럼이 많다.

•

테이블 분리: 공통 테이블 + 각 타입별 별도 테이블로 설계: 정규화가 되지만 조인이 필요하다.

•

각각 독립적인 테이블로 분리: 공통 없이 각각 설계. 하지만 통합 조회에 어려움이 존재

3. 논리 모델링 (정규화와 역정규화)

정규화는 사실을 한 곳에만 저장해서 데이터의 갱신이상(갱신 시 불일치), 삭제시 정보 소실, 삽입 이상(삽입 시 불필요한 값을 요구)을 없애는 것.

그러면 의도적으로 반정규화 하는 케이스는 무엇이 있나

조회 성능: 조인이 너무 많거나, 집계의 비용이 비쌀경우

시점 데이터 보존(스냅샷): 원본이 바뀌더라도 그 당시의 값이 유지되어야 하는 경우.
ex) 주문 가격 (주문 시점의 가격을 저장해야 함)
이 케이스는 엄밀히 말하면 다른 정보를 저장하는 것이지만 역정규화로써 같이 설명 가능

파티셔닝/샤딩: 자식 테이블에서 조인 없이 샤드를 조회하기 위해 부모의 속성을 그대로 복사

반정규화를 하게 되면 DB 단에서 관리할 수 있었던 데이터의 정합성을 애플리케이션 단에서 보장해야 한다.

어떤 방식으로 동기화 할 것인가

•

트랜잭션 범위

•

이벤트 기반 비동기 갱신

•

배치 재계산 (실시간성 고려해서)

역정규화 하는 케이스

유형 1. 시점 스냅샷 — "그때의 값"이 필요할 때

예시: 주문 상품 테이블에 주문 당시 상품명과 가격 복사

order_items: order_id | product_id | product_name | unit_price | qty
Plain Text
복사

정규화 관점에서 product_name과 unit_price는 products 테이블에 있으니 중복입니다. 하지만 상품 가격이 4500원에서 5000원으로 바뀌었다고 해서 어제 주문의 결제 금액이 바뀌면 안 됩니다. 고객이 영수증을 다시 열었는데 금액이 달라져 있으면 그건 버그입니다.

엄밀히 말하면 이건 중복이 아닙니다. products.price는 "현재 판매가"이고 order_items.unit_price는 "주문 당시 결제가"로 서로 다른 사실입니다. 우연히 값이 같을 뿐입니다. 그래서 이 유형은 동기화가 필요 없습니다. 한번 쓰고 절대 안 바뀝니다.

같은 유형: 배송지 주소 (회원이 주소를 지워도 배송 기록은 남아야 함), 쿠폰 할인액 (쿠폰 정책이 바뀌어도 이미 적용된 할인은 그대로), 정산 시점의 수수료율, 계약서의 당시 약관 버전.

판단 기준: "원본이 바뀌었을 때 이 값도 바뀌어야 하는가?" 아니오면 스냅샷입니다. 이건 성능이 아니라 정확성 때문에 하는 것이라 고민할 여지 없이 합니다.

유형 2. 집계 캐시 — 매번 COUNT/SUM하기 비쌀 때

예시: 게시글 테이블에 댓글 수, 좋아요 수 컬럼

posts: post_id | title | comment_count | like_count
Plain Text
복사

목록 화면에 게시글 20개를 보여주는데 각각 댓글 수를 보여줘야 합니다. 정규화된 구조면 SELECT COUNT(*) FROM comments WHERE post_id = ?를 20번 하거나 GROUP BY 조인을 합니다. 게시글 조회가 초당 수천 번이면 이 집계가 DB를 잡아먹습니다.

댓글 수를 posts에 컬럼으로 두면 목록 조회는 단일 테이블 스캔으로 끝납니다. 대신 댓글이 달릴 때마다 UPDATE posts SET comment_count = comment_count + 1을 해야 합니다.

동기화 방식 선택

•

같은 트랜잭션에서 갱신: 댓글 INSERT와 카운트 UPDATE를 한 트랜잭션에. 정합성은 완벽하지만 인기 게시글에 댓글이 몰리면 posts 행에 락 경합이 생김

•

비동기 갱신: 댓글 INSERT 후 이벤트 발행, 컨슈머가 카운트 갱신. 수 초 어긋날 수 있지만 락 경합 없음. 댓글 수 정도는 이 정도 지연이 허용됨

•

주기적 재계산: 배치가 밤마다 실제 COUNT로 덮어씀. 위 두 방식과 병행해서 드리프트 보정용으로 씀

같은 유형: 회원의 총 주문 금액·주문 횟수 (등급 산정용), 상품의 평균 평점·리뷰 수, 채팅방의 안 읽은 메시지 수, 가맹점의 오늘 매출 합계.

판단 기준: 읽기 빈도 / 쓰기 빈도 비율이 크고, 약간의 지연이나 오차를 허용할 수 있는가. 돈처럼 1원도 틀리면 안 되는 집계는 이 방식을 쓰더라도 반드시 원본 이력에서 재계산 가능하게 설계합니다.

유형 3. 조인 회피용 컬럼 복사 — 자주 같이 조회되는 부모 속성

예시: 주문 테이블에 가맹점명 복사, 댓글 테이블에 작성자 닉네임 복사

comments: comment_id | post_id | user_id | user_nickname | content
Plain Text
복사

댓글 목록에 닉네임을 보여주려면 users와 조인해야 합니다. 댓글이 수억 건이고 조회가 많다면 이 조인이 부담됩니다. 닉네임을 댓글에 복사하면 조인이 사라집니다.

문제는 닉네임이 바뀌는 경우입니다. 이 유형은 원본이 바뀌면 복사본도 바뀌어야 하는 진짜 중복이라 동기화가 필수입니다.

•

닉네임 변경 시 UPDATE comments SET user_nickname = ? WHERE user_id = ?. 댓글이 10만 건인 헤비 유저면 이 UPDATE가 무겁고 락이 오래 걸림 → 배치로 나눠서 처리하거나 비동기로

•

또는 "닉네임 변경은 과거 댓글에 반영 안 됨"을 정책으로 정함. 그러면 유형 1 스냅샷이 되어 동기화가 필요 없어짐. 실제로 많은 서비스가 이렇게 함

판단 기준: 이 유형은 셋 중 가장 위험합니다. 복사한 컬럼이 얼마나 자주 바뀌는지가 핵심입니다. 거의 안 바뀌는 값(가맹점명, 카테고리명)은 복사해도 되고, 자주 바뀌는 값(재고, 상태)은 절대 복사하면 안 됩니다. 애매하면 스냅샷 정책으로 바꿔서 동기화 자체를 없애는 게 낫습니다.

유형 4. 분산 환경용 키 전파 — 샤딩·파티셔닝 때문에

예시: 주문 상품 테이블에 user_id 복사

orders:      order_id | user_id | ...
order_items: item_id | order_id | user_id | product_id   ← user_id는 orders에 이미 있음
Plain Text
복사

정규화 관점에서 order_items의 user_id는 orders를 통해 알 수 있으니 중복입니다. 하지만 DB를 user_id 기준으로 샤딩했다면, order_items를 조회할 때 user_id가 없으면 어느 샤드에 있는지 알 수 없어서 전체 샤드를 뒤져야 합니다.

같은 원리로 파티션 키(created_at 등)를 자식 테이블에 복사해서 부모와 같은 파티션에 놓이게 합니다.

이 유형은 user_id가 바뀔 일이 없어서 동기화 문제가 없습니다. 분산 구조를 도입하는 순간 거의 강제되는 역정규화입니다.

유형 5 (보너스). 조회 전용 테이블 — CQRS

예시: 주문 상세 화면용으로 주문+상품+배송+결제 정보를 합친 읽기 전용 테이블

정규화된 원본 테이블은 그대로 두고, 특정 화면이나 검색을 위해 조인이 다 끝난 형태의 테이블을 별도로 만듭니다. 원본이 바뀌면 이벤트로 조회 테이블을 갱신합니다. Elasticsearch로 상품 검색 인덱스를 만드는 것도 본질적으로 이것입니다.

원본 테이블은 정규화를 유지하니 쓰기 정합성을 잃지 않고, 조회 테이블은 완전히 역정규화되어 읽기가 빠릅니다. 대신 두 저장소의 동기화 파이프라인을 운영해야 하고 지연이 존재합니다. 규모가 커지면 대부분 이 방향으로 갑니다.

역정규화를 하면 안되는 케이스

재고 수량을 여러 곳에 복사: 상품 재고를 상품 테이블과 장바구니와 주문에 다 복사하면 재고 차감 시 어디가 진실인지 알 수 없습니다. 재고처럼 자주 바뀌고 정확해야 하는 값은 한 곳에만 둡니다.

회원 등급을 주문에 복사 (스냅샷 목적이 아닌데): "VIP 고객 주문 목록" 조회를 빠르게 하려고 주문에 등급을 복사하면, 등급이 바뀔 때마다 그 회원의 전체 주문을 갱신해야 합니다. 이건 조인이 낫습니다. 단, "주문 당시 등급으로 할인율이 결정됐다"면 스냅샷이므로 복사가 맞습니다. 같은 컬럼이라도 목적에 따라 답이 다릅니다.

초기 단계 서비스 전반: 트래픽이 없는데 역정규화하면 정합성 코드만 늘어납니다. 측정되지 않은 성능 문제는 문제가 아닙니다.