관계형 데이터베이스 설계 단계

김규원·2025년 12월 13일

sql

DB

목록 보기

15/22

관계형 데이터베이스 설계 단계 요약

1. 분석 단계

비즈니스 요구사항과 주요 트랜잭션을 파악하여 데이터 흐름과 시스템 범위를 정의
요구사항 수집 및 명세 작성

2. 개념적 모델링

추상화된 엔티티와 관계, 비즈니스 규칙을 도출하여 데이터 구조의 기본을 마련
엔티티 및 속성 도출

3. 논리적 모델링

개념적 모델을 실제 릴레이션 구조로 변환
DBMS 독립적인 구체적 설계를 완성
릴레이션 스키마 도출

4. 물리적 모델링

논리적 모델을 선택한 DBMS에 맞춰
최적화하고 성능과 유지보수를 고려한
DBMS 특화 구현

1단계. 분석 단계

1.1 개요

목표와 목적

비즈니스 목표

고객의 온라인 쇼핑 경험을 극대화
쇼핑몰 운영의 효율성을 높이며
주문 처리, 재고 관리, 결제 및 배송의 통합적 관리 체계 구축

시스템 목표

고객과 관리자 양측의 필요를 만족시키는 사용자 친화적인 DB 시스템 구축
고객: 상품 검색, 주문, 결제, 리뷰 등 원활한 상호작용 구현
관리자: 상품, 주문, 재고, 결제 기록 등 통계 분석의 효율적 관리

범위

주요 대상 업무

1. 상품 관리
1. 고객 관리
1. 주문 및 결제 관리
1. 배송 및 반품/교환 관리
1. 리뷰 및 피드백 관리
1. 관리자 통계 및 운영 보고

시스템 환경

웹 기반 애플리케이션과 통합된 데이터 베이스 시스템 구현

1.2 요구 수집 명세서

이해관계자 식별

내부 이해관계자

쇼핑몰 관리자: 상품 등록 주문 및 재고 관리, 통계 보고
운영 팀: 고객 문의, 배송, AS 관리

외부 이해관계자

고객: 회원 가입, 상품 검색, 주문 및 결제, 리뷰 작성
배송/물류 업체: 배송 현황 관리 및 정보 제공
결제 대항사: 결제 승인 및 거래 정보 연동

사용자 인터뷰 및 설문 분석

고객

다양한 필터 기능을 통한 상품 검색 및 정렬
주문 상태 실시간 알림 및 주문 이력 관리
쉽고 빠른 결제 옵션, 안전한 결제 처리
리뷰 및 평가 시스템, 찜 기능 및 개인화된 추천

관리자 요구

상품 및 재고의 효율적 관리
주문, 결제, 배송 상황 모니터링
통계 및 보고서 자동 생성
사용자 관리(회원 관리, 불만 처리 등)의 용이성

기능적/비기능적 요구사항

기능

상품 등록/수정/삭제, 다중 카테고리 분류
회원 가입, 로그인, 정보 수정 및 탈퇴
주문 생성, 변경, 취소 및 환불 처리
결제 정보 연동 및 승인 기록 관리
배송 정보 등록 및 배송 상태 추적
리뷰 작성, 수정, 삭제 및 평점 관리

비기능

성능: 동시 다중 사용자 처리 보장
보안: 개인정보 보호 및 결제 정보 암호화
확장성: 향후 기능 추가를 위한 스키마 유연성 유지
신뢰성: ACID 트랜잭션 지원을 통한 데이터 무결성 보장
사용성: 사용자 친화적 인터페이스 및 직관적 탐색 기능 제공

1.3. 업무 영역 분할도

업무 영역 분할은 전체 쇼핑몰 운영 업무를 기능별 모듈로 구분하여, 데이터 흐름과 상호작용을 명확히 함.

관리 항목

업무 영역 분할도는 추후 다이어그램 도구를 활용해 시각적으로 표현하며, 각 모듈 간의 데이터 교류 및 트랜잭션 흐름을 상세히 나타낼 예정

1.4. 데이터 요구 분석 명세서

주요 엔티티 정의 및 속성

주요 관계

1.4.1 고객-주문(1:n)

단일 고객이 다수 주문 생성 가능

1.4.2 주문-상품(n:m)

하나의 주문에 여러 상품이 포함될 수 있으며, 동일한 상품이 여러 주문에 포함될 수 있음
이를 위해 중간 테이블(Order-Details)를 사용

1.4.3 주문-결제(1:1)

각 주문에는 하나의 결제 정보 주문에는 하나의 결제 정보

1.4.4 주문-배송(1:1)

각 주문에는 하나의 배송 정보가 필요

1.4.5. 상품-리뷰(1:n)

개별 상품에 대해 여러 리뷰가 작성될 수 있음

무결성 제약 조건

1. 기본키 및 외래키:

각 엔티티의 고유 식별자 부여 및 관계 무결성 유지

2. 데이터 형식:

이메일, 전화번호, 날짜 등 형식 검증

3. 범위 및 값 제약:

평점(1~5 범위), 재고수(음수가 될 수 없음) 등

1.5 트랜잭션 요구 분석 명세서

1.5.1 주요 트랜잭션 식별 및 프로세스

트랜잭션 유형 - 주문 생성 및 처리

단계 1: 고객이 상품 선택, 장바구니에 담기
단계 2: 주문 생성 시 재고 확인 및 예약
단계 3: 결제 정보 입력 및 결제 승인
단계 4: 배송 정보 생성 및 초기 배송 상태 설정
보증: 각 단계에서 ACID 성질 확보

트랜잭션 유형 - 주문 취소 및 환불 처리

단계 1: 주문 취소 요청 접수
단계 2: 주문 상태“취소”로 수정 및 결제 취소 요청
단계 3: 환불 완료 후 재고 복구
예외 처리: 환불 실패 시 롤백 메커니즘 적용

트랜잭션 유형 - 상품 관리 트랜잭션 (등록/수정/삭제)

단계 1: 입력 데이터 유효성 검사
단계 2: 상품 정보 등록 및 옵션/카테고리 연결
단계 3: 재고 및 관련 테이블 동시 업데이트
보증: 데이터 무결성 침해 없이 처리

트랜잭션 유형 - 고객 계정 및 리뷰 관리

회원가입: 기본 정보 입력 후 중복 확인 및 계정 생성
리뷰 작성/수정/삭제: 고객 신원 확인 후 해당 상품에 리뷰 등록 및 수정
보안: 인증된 사용자만 트랜잭션 수행

1.5.2 트랜잭션 처리 및 동시성 제어

ACID 준수

각 트랜잭션은 원자성, 일관성, 격리성, 지속성을 보장하도록 설계

동시성 관리

다중 사용자 환경에서의 데이터 충돌 방지
예: 락킹 기법, 타임스탬프 기반 동시성 제어

예외 및 롤백 처리

트랜잭션 중 오류 발생 시 전체 트랜잭션을 롤백하여 일관성을 유지

1단계 요약

요구 분석 단계에서는 쇼핑몰 운영 전반에 걸친 기능 및 비기능 요구사항을 명확하게 도출하고, 주요 엔티티와 트랜잭션 흐름을 상세히 기술하였음.
이 명세서는 향후 개념적 모델링 단계에서 엔티티 간 관계를 시각화하는 ER 다이어그램 작성 및, 논리적 모델링 단계에서 구체적 릴레이션 스키마와 무결성 제약 조건 정의의 기초 자료로 활용될 예정임.
추가 작업: 사용자 인터뷰 및 설문 조사, 실제 업무 프로세스 분석 결과를 반영하여 명세서를 보완하고, 필요 시 업계 표준 사례와 연동한 보안 및 성능 요구사항을 추가할 계획임.
이와 같이 요구 분석 명세서는 전체 프로젝트 설계의 기초 자료로, 각 이해관계자의 요구를 체계적으로 정리하고, 나아가 데이터 흐름과 트랜잭션 동작을 명확하게 정의하여 시스템 전체의 일관성과 확장성을 보장하는 데 중점을 두어 작성됨.

2단계. 개념적 모델링

2.1 개념적 모델링의 목표

분석 단계에서 도출한 사업 요구사항과 데이터 요구 분석 결과를 기반으로, 데이터의 본질적인 구조와 엔티티 간 관계, 트랜잭션 흐름을 시각적으로 그리고 논리적으로 명확히 하는 작업

목적

비즈니스 요구사항 반영
- 분석 단계에서 수집한 요구사항(상품, 고객, 주문, 결제, 배송, 리뷰 등)을 데이터 엔티티와 관계로 전환함.
데이터 구조의 시각화
- 엔티티(개체), 이들의 속성 및 상호 관계를 ER 다이어그램을 통해 시각적으로 표현하여, 데이터 흐름과 업무 도메인 내 상호 연결성을 명확히 함.
트랜잭션 모델링
- 비즈니스 프로세스(예: 주문 생성, 결제 처리, 배송 진행 등)의 흐름을 도식화하여, 각 단계에서 필요한 데이터 접근과 업데이트, 그리고 에러 처리와
  롤백 등의 요구사항을 정의함.

2.2 주요 엔티티 및 속성 식별

분석 단계에서 도출된 요구사항을 토대로 다음과 같은 주요 엔티티와 속성을 정의함

각 엔티티는 시스템 내 고유 식별자를 기본키(PK)로 가지며, 외부 엔티티와의 관계를 표현하기 위해 외래키(FK)를 사용함

중요한 포인트
Order_Details (주문 상세)
주문ID (FK), 상품ID (FK), 주문수량, 개별 가격

Orders와 Products 간 다대다 관계를 해소하기 위한 조인 테이블

2.3 엔티티 간 관계 및 ER 다이어그램

ER 다이어그램은 위 엔티티와 관계를 시각적으로 표현함.

엔티티 상자는 각 엔티티를 나타내며, 내부에 주요 속성을 기재함
관계선은 엔티티 간의 상호작용을 보여주며, 관계의 성격(1:1, 1:N, 다:다)을 해당 선 옆에 표시함.
연결선의 꼬리부분 및 다이아몬드 기호를 활용하여, 참여 제약조건(선택적/반드시 포함해야 하는지)을 명시함.
예를 들어, Customers와 Orders 사이에는 고객ID를 통한 연결이있으며, Order_Details 엔티티는 Orders와 Products의 다대다 관계를 해소하는 역할을 함.

1. Customers ──< Places >── Orders

참여도: 고객 1 : 주문 N

Orders ──< Contains >── OrderDetails

참여도: 주문 1 : 주문상세 N

Products ──< Included_in >──
OrderDetails

참여도: 상품 1 : 주문상세 N

Orders ──< Paid_by >── Payments

참여도: 주문 1 : 결제 1 (필수 1:1)
※ 결제 수단과 관계없이 (예: 현금입금 포함) 모든 주문은 최소 1건의 결제 기록이 존재해야 하므로 필수 관계로 설계함.

Orders ──< Shipped_by >── Deliveries

참여도: 주문 1 : 배송 0..1 (선택적 1:1)

Customers ──< Writes >── Reviews

참여도: 고객 1 : 리뷰 N

Products ──< About >── Reviews

참여도: 상품 1 : 리뷰 N

ER-Diagram(피터 첸 표기법)

ER-Diagram(IE 표기법)

2.4 트랜잭션 모델링

개념적 모델링 단계에서는 개별 트랜잭션의 단계와 데이터 흐름을 고려하여, 비즈니스 프로세스가 어떻게 동작하는지 도식화함

2.4.1 주문 생성 및 처리 트랜잭션

2.4.2 기타 트랜잭션

2.4.3 주문 생성 및 처리 흐름 요약

고객이 주문요청
시스템은 다음 순서로 데이터 처리
Order 테이블에 주문생성 (INSERT)
Order Detail 테이블에 상품 상세내역 추가(INSERT)
Payment 테이블에 결제 기록 추가(INSERT)
Product 테이블에 해당 상품 재고 차감(UPDATE)
Delivery 테이블에 배송정보 생성(INSERT-준비상태(default))

2.4.4 CRUD 매트릭스

주요 개체의 단계별 CRUD 상태 테이블화
주요 트랜잭션이 발생 시 개체에 발생되는 CRUD 상태를 테이블 형태로 표기하여 향후 논리적 모델링, 물리적 모델링시 반영함

2.5 개념적 설계 및 스키마 통합

2.5.1 개념적 스키마 도출

통합 모델링:

위에서 도출한 엔티티, 속성, 관계 및 트랜잭션 모델링 결과를 하나의 통합 개념적 스키마로 정리함.

비즈니스 룰 반영:

각 엔티티 및 관계에 대해 업무 규칙(예: 주문 취소 가능한 기간, 결제 방법 제한 등)을 부가적으로 명시하여, 데이터 모델의 실무 적용성을 높임.

2.5.2 정규화 및 확장성 고려

정규화:

데이터 중복을 제거하고 이상 현상을 방지하기 위해 엔티티들을 정규화함.
특히, 일부 특성은 별도의 개체로 뽑아낼 수 있음.(카테고리, 색상, 사이즈, 주소 등)

확장성:

향후 추가 기능(예: 쿠폰, 회원 등급별 혜택, 이벤트 정보 등)의 도입을 고려하여, 모듈화된 설계를 채택 함.

보안과 접근 제어:

민감 데이터(예: 고객 개인정보, 비밀번호 등)에 대해 암호화 및 접근 제어 정책을 개념적으로 포함시킴.

2단계 요약

명확한 엔티티 및 관계 정의:

▪ 고객, 상품, 주문 등 주요 엔티티 및 이들 간의 관계를 체계적으로 도출하여, 데이터베이스 전반의 구조를 한 눈에 파악할 수 있도록 함.

트랜잭션 프로세스 시각화:

▪ 주문 생성, 결제, 배송, 취소/환불 등의 비즈니스 프로세스를 모델링하여, 시스템 동작의 연속성과 무결성을 강화함.

개념적 스키마의 토대 마련:

▪ 이 모델은 이후 논리적 모델링 단계에서 Relational Schema 및 제약 조건 정의의 기초 자료로 활용되며, 최종 PostgreSQL 물리적 모델링 단계로 이어짐.

이와 같이 개념적 모델링은 분석 단계에서 정의된 요구사항을 바탕으로 데이터의 핵심 구조와 상호 관계를 명확히 하여, 전체 데이터베이스 설계의 토대를 마련하는 중요한 단계임.
추가적으로 논리적 모델링으로 전환하기 전에, 도출된 모델의 타당성과 확장성을 실제 비즈니스 시나리오와 함께 검토하는 작업이 필요함.

3단계. 논리적 모델링

3.1 논리적 모델링의 개요

개념적 모델의 추상적인 아이디어를 실제 데이터베이스의 릴레이션 스키마로 전환하는 작업

목적

엔티티, 속성, 관계를 구체적인 테이블 설계로 전환하여, 데이터의 저장, 조회, 제약 조건 등을 명시함.
데이터 무결성, 관계 일관성, 정규화 원칙을 반영함.

주요 산출물

각 엔티티에 대응하는 테이블 정의
각 테이블의 기본키(PK)와 외래키(FK) 설정
제약 조건(데이터 형식, 범위, null 여부 등) 및 인덱스 설계

3.2 개별 엔티티에 대한 릴레이션 스키마

3.3 무결성 제약 조건 및 정규화 고려

기본키/외래키

각 테이블은 고유 식별자를 가진 기본키를 가지고 있으며, 관련 테이블 간 외래키를 통해 참조 무결성을 유지

유일성 및 체크 제약

이메일과 같은 속성은 유일성 제약을 추가하여 중복을 방지하고, 평점이나 재고 등의 값은 CHECK 제약 조건을 사용해 범위를 한정함.

정규화

테이블 설계는 3차 정규형(3NF) 기준을 따라 데이터 중복을 최소화하고 각 테이블이 하나의 주제에 집중하도록 설계

인덱스 설계

검색이나 조인 연산에서 자주 활용되는 속성(예: 이메일, 주문 상태, 제품 카테고리 등)에 대해 추가 인덱스 설계를 고려 가능

3.4 관계형 스키마의 특징 및 전환 과정

1. 개념적 모델 → 논리적 모델:

개념적 모델의 엔티티(예: Customers, Products 등)를 각각 테이블로 전환함
개체 간의 관계(1:N, 다:1, 1:1 등)를 외래키 제약으로 반영함.

2. 다대다 관계 처리:

Orders와 Products 사이의 다대다 관계는 - Order_Details 테이블로 처리함.

3. 비즈니스 룰 통합:

주문 상태, 결제 방식 등 비즈니스 로직에 따른 제약 조건을 논리적 스키마에 포함시킴

4. 트랜잭션 무결성 유지:

각 테이블 정의 시 ACID 성질 보장을 위해 각 트랜잭션 단계(주문→생성→결제 승인→배송 처리)가 원자성, 일관성, 격리성, 지속성을 충족하도록 설계함

3단계 요약

논리적 모델링 단계에서는 개념적 모델의 추상적인 개념을 실제 데이터 구조로 변환하여 일관된 데이터베이스 스키마를 구축함.
정의된 테이블과 관계는 PostgreSQL과 같은 데이터베이스 구현 시 기초 자료가 되며, 성능 최적화 및 보안 정책은 물리적 모델링 단계에서 조정됨.
다음 단계에서는 물리적 모델링과 인프라 구축,
SQL 스크립트 배포 전 설계 유효성 검증이 필요함

4단계. 물리적 모델링

4.1 물리적 모델링의 개요

목적

논리적 모델의 개념적 테이블 및 관계를 실제 PostgreSQL 환경에 맞춰 구체적으로 구현함

저장 매개변수

각 테이블에 대해 fillfactor와 같은 저장 옵션을 조정해 디스크 I/O 효율을 높임.

인덱스 및 클러스터

자주 사용되는 컬럼에 대해 인덱스를 생성하고,
필요 시 테이블 클러스터링을 적용하여 조회 성능을 개선.

파티셔닝

높은 데이터 볼륨을 예상하는 테이블(예, 주문 테이블)은 파티셔닝을 고려해 관리 및 쿼리 성능을 최적화.

테이블스페이스

데이터 분산과 성능 튜닝을 위해 특정 테이블이나 인덱스를 별도 테이블스페이스에 저장.

단계

논리적 모델에 기반한 테이블 구조를 PostgreSQL 문법으로 구현
각 테이블에 필요한 인덱스 및 저장 옵션 설정
(필요 시) 파티셔닝 및 클러스터링 적용
물리적 환경에 따른 추가 튜닝 및 백업 전략 수립

Fillfactor

테이블 또는 인덱스 페이지에 데이터를 어느 정도 채울지 비율로 설정 (기본: 100)

효과:

UPDATE가 자주 발생하는 경우, 여유 공간을 두어 페이지 분할 방지
대량의 INSERT/DELETE가 발생하는 테이블
VARCHAR 또는 TEXT 등의 가변 길이 문자열 컬럼이 많은 테이블

목적:

페이지 분할(TOAST/HEAP 분할)을 방지하고 성능 저하를 완화

문법:

CREATE TABLE orders (
 ...
) WITH (fillfactor = 80);
→ 빈공간 20%를 유지하면서 테이블 생성
기존 테이블에 추가하는 방법
ALTER TABLE orders SET (fillfactor = 90)

즉,
INSERT 위주일 땐, 업데이트가 거의 없으므로 권장 fillfactor는 100
UPDATE 위주일 땐, 업데이트가 빈번하므로 권장 fillfactor는 80

Index and Clustering

Index

조회 성능 향상을 위해 컬럼에 인덱스를 설정

CREATE INDEX idx_order_date ON orders(order_date);

Clustering

특정 인덱스를 기준으로 테이블의 실제 데이터 정렬

CLUSTER orders USING idx_order_date;

Partitioning

대용량 테이블을 여러 파티션으로 분리하여 관리 (예: 날짜, 지역 기준)
쿼리 성능 개선, 유지보수 편의성

CREATE TABLE orders (
 order_id int,
 order_date date
) PARTITION BY RANGE(order_date);
-- 생성 예시
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

Tablespace

물리적 저장 경로를 지정하여 데이터베이스 객체를 특정 디스크에 분산 저장
성능 튜닝, 백업 분리, 고속 디스크 활용

-- 새로운 테이블스페이스를 생성
CREATE TABLESPACE fast_disk LOCATION '/ssd1/tbs_fast’;
-- 앞서 정의한 fast_disk 테이블스페이스가 가리키는 /ssd1/tbs_fast 디렉터리에 저장하라고 지정
CREATE TABLE orders (...) TABLESPACE fast_disk;

4.2 PostgreSQL 기반 물리적 테이블 생성

4.3 추가 물리적 최적화 고려 사항

테이블스페이스 설정

대용량 데이터나 I/O 부하를 분산하기 위해, 특정 테이블 또는 인덱스를 별도의 테이블스페이스에 저장할 수 있음.

CREATE TABLESPACE fastspace LOCATION '/mnt/fast_ssd';
ALTER TABLE public.orders SET TABLESPACE fastspace;

클러스터링

자주 사용하는 인덱스 기준으로 테이블을 클러스터링하여, 데이터 물리적 순서를 재정렬하면 쿼리 성능을 향상시킬 수 있음

CLUSTER public.customers USING idx_customers_email

파티셔닝

주문 테이블처럼 빠르게 데이터가 축적될 것으로 예상되는 경우, 주문일자(order_date)를 기준으로 RANGE 파티셔닝을 적용하여 관리 및 쿼리 효율을 극대화할 수 있음

VACUUM 및 ANALYZE

PostgreSQL 고유의 자가 관리 기능인 VACUUM 및 ANALYZE 명령어를 정기적으로 실행하여 데이터베이스 성능을 유지함.

VACUUM
데이터가 UPDATE되거나 DELETE될 때, PostgreSQL은 해당 데이터를 즉시 지우지 않고 "죽은 튜플(Dead Tuple)"로 표시만 합니다. VACUUM은 이 죽은 튜플들이 차지하고 있는 공간을 회수하여 재사용 가능하게 만듭니다.

ANALYZE
테이블과 인덱스의 데이터 분포 통계 정보를 수집하여 시스템 카탈로그에 저장합니다.

PostgreSQL의 쿼리 옵티마이저(Query Optimizer)는 이 통계 정보를 바탕으로 쿼리를 실행하는 가장 효율적인 계획(Execution Plan)을 결정합니다. 통계가 오래되거나 부정확하면, 옵티마이저가 비효율적인 계획을 선택하여 쿼리 성능이 크게 저하될 수 있습니다.

WAL 설정 및 백업 전략

데이터 무결성과 복구를 위해 PostgreSQL의 WAL(Write-Ahead Logging) 관련 매개변수를 조정하고, 정기적인 백업 전략을 수립함.

WAL (Write-Ahead Logging)
모든 데이터 변경 사항(INSERT, UPDATE, DELETE)은 실제 디스크 데이터 파일에 기록되기 전에 먼저 WAL 파일이라는 로그에 기록
wal_level, synchronous_commit 등의 매개변수 조정은 성능과 데이터 안정성(내구성) 사이의 균형을 맞추는 데 중요

4.4 마무리

물리적 모델링 단계에서는 논리적 모델에서 도출한 테이블 구조와 관계를 실제 PostgreSQL 환경에 맞춰 다음과 같이 구체적으로 구현함.

테이블 생성 스크립트:
- 논리적 릴레이션 스키마를 기반으로 하되, fillfactor 등과 같은 저장
- 매개변수를 추가하여 데이터 저장 효율을 높입니다.
인덱스 및 클러스터링:
- 주요 조회 대상 컬럼에 대해 인덱스 생성 및 필요 시 클러스터링을 적용해 쿼리 성능을 개선합니다.
파티셔닝 및 테이블스페이스:
- 대용량 데이터 및 I/O 부하 관리를 위해 파티셔닝과 테이블스페이스 활용 방안을 도입합니다

김규원

행복한 하루 보내세요

이전 포스트

정규화

다음 포스트