쇼핑몰 DB 설계 완벽 가이드

개요

이 가이드는 김영한의 실전 데이터베이스 강의의 쇼핑몰 프로젝트 패턴을 기반으로 작성되었습니다.

**목표:**
완전한 쇼핑몰 DB를 3단계 설계 프로세스로 구축

1단계: 요구사항 분석

핵심 기능

**회원 관리:**
- 회원 가입/로그인
- 프로필 관리
- 배송지 관리

**상품 관리:**
- 상품 등록
- 카테고리 분류
- 재고 관리

**주문 관리:**
- 장바구니
- 주문/결제
- 주문 상태 추적
- 배송 관리

2단계: 개념적 설계 (ERD)

핵심 엔티티

**1. 회원 (Member)**
- 회원 ID
- 이메일
- 비밀번호
- 이름
- 전화번호

**2. 배송지 (Address)**
- 배송지 ID
- 회원 ID (FK)
- 주소
- 기본 배송지 여부

**3. 상품 (Product)**
- 상품 ID
- 상품명
- 가격
- 재고

**4. 카테고리 (Category)**
- 카테고리 ID
- 카테고리명
- 상위 카테고리 (자기참조)

**5. 주문 (Order)**
- 주문 ID
- 회원 ID (FK)
- 주문 일시
- 주문 상태

**6. 주문 상세 (OrderItem)**
- 주문 상세 ID
- 주문 ID (FK)
- 상품 ID (FK)
- 수량
- 가격

**7. 배송 (Delivery)**
- 배송 ID
- 주문 ID (FK)
- 배송지 ID (FK)
- 배송 상태

관계 정의

**회원 ↔ 배송지:**
1:N (한 회원이 여러 배송지)

**상품 ↔ 카테고리:**
N:M → 중간 테이블 (ProductCategory)

**회원 ↔ 주문:**
1:N (한 회원이 여러 주문)

**주문 ↔ 주문 상세:**
1:N (한 주문에 여러 상품)

**주문 ↔ 배송:**
1:1 (한 주문에 한 배송)

3단계: 논리적 설계 (정규화)

정규화 적용

**1NF: 원자값**
- 모든 속성이 원자값
- 주소를 우편번호/기본주소/상세주소로 분리

**2NF: 부분 함수 종속 제거**
- 주문 상세에서 상품 정보 분리
- 상품 테이블 참조

**3NF: 이행 함수 종속 제거**
- 배송 정보를 주문과 분리
- 배송 테이블 별도 생성

결과

**회원 (members)**
``
- id (PK)
- email (UNIQUE)
- password
- name
- phone
`

**배송지 (addresses)**
`
- id (PK)
- member_id (FK)
- postal_code
- address1
- address2
- is_default
`

**상품 (products)**
`
- id (PK)
- name
- price
- stock
- description
`

**카테고리 (categories)**
`
- id (PK)
- name
- parent_id (FK, self-reference)
`

**상품-카테고리 (product_categories)**
`
- product_id (PK, FK)
- category_id (PK, FK)
`

**주문 (orders)**
`
- id (PK)
- member_id (FK)
- order_date
- status (ENUM)
- total_amount
`

**주문 상세 (order_items)**
`
- id (PK)
- order_id (FK)
- product_id (FK)
- quantity
- price
`

**배송 (deliveries)**
`
- id (PK)
- order_id (FK)
- address_id (FK)
- status (ENUM)
- tracking_number
`

4단계: 물리적 설계 (MySQL)

테이블 생성

`sql
-- 회원
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
name VARCHAR(50) NOT NULL,
phone VARCHAR(20),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 배송지
CREATE TABLE addresses (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
member_id BIGINT NOT NULL,
postal_code VARCHAR(10),
address1 VARCHAR(255) NOT NULL,
address2 VARCHAR(255),
is_default BOOLEAN DEFAULT FALSE,
FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
INDEX idx_member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 카테고리
CREATE TABLE categories (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
parent_id BIGINT,
FOREIGN KEY (parent_id) REFERENCES categories(id),
INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 상품
CREATE TABLE products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
description TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_name (name),
FULLTEXT INDEX idx_search (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 상품-카테고리
CREATE TABLE product_categories (
product_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
PRIMARY KEY (product_id, category_id),
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 주문
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
member_id BIGINT NOT NULL,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
status ENUM('PENDING', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED') DEFAULT 'PENDING',
total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
FOREIGN KEY (member_id) REFERENCES members(id),
INDEX idx_member_id (member_id),
INDEX idx_order_date (order_date),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 주문 상세
CREATE TABLE order_items (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id),
INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 배송
CREATE TABLE deliveries (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT NOT NULL UNIQUE,
address_id BIGINT NOT NULL,
status ENUM('PREPARING', 'SHIPPED', 'DELIVERED') DEFAULT 'PREPARING',
tracking_number VARCHAR(50),
delivered_at DATETIME,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (address_id) REFERENCES addresses(id),
INDEX idx_tracking (tracking_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
`

인덱스 전략

**회원 조회 (이메일 로그인):**
`sql
INDEX idx_email (email)
`

**주문 조회 (회원별, 날짜별):**
`sql
INDEX idx_member_id (member_id)
INDEX idx_order_date (order_date)
`

**상품 검색:**
`sql
FULLTEXT INDEX idx_search (name, description)
`

5단계: 샘플 데이터 삽입

`sql
-- 회원
INSERT INTO members (email, password, name, phone) VALUES
('user1@example.com', 'hashed_pw', '홍길동', '010-1234-5678'),
('user2@example.com', 'hashed_pw', '김철수', '010-2345-6789');

-- 카테고리
INSERT INTO categories (name, parent_id) VALUES
('전자제품', NULL),
('노트북', 1),
('스마트폰', 1);

-- 상품
INSERT INTO products (name, price, stock, description) VALUES
('MacBook Pro', 2500000, 10, 'M3 칩셋 탑재'),
('iPhone 15', 1200000, 20, '최신 스마트폰');

-- 상품-카테고리 연결
INSERT INTO product_categories (product_id, category_id) VALUES
(1, 2), -- MacBook → 노트북
(2, 3); -- iPhone → 스마트폰
`

6단계: 활용 쿼리

주문 생성 (트랜잭션)

`sql
START TRANSACTION;

-- 1. 주문 생성
INSERT INTO orders (member_id, total_amount)
VALUES (1, 2500000);

SET @order_id = LAST_INSERT_ID();

-- 2. 주문 상세
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 1, 1, 2500000);

-- 3. 재고 차감
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;

-- 4. 배송 정보
INSERT INTO deliveries (order_id, address_id)
VALUES (@order_id, 1);

COMMIT;
`

회원별 주문 내역

`sql
SELECT
o.id AS order_id,
o.order_date,
o.status,
o.total_amount,
GROUP_CONCAT(p.name) AS products
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.member_id = 1
GROUP BY o.id
ORDER BY o.order_date DESC;
`

핵심 포인트

1. 정규화

**3NF 적용:**
- 배송 정보를 주문과 분리
- 데이터 중복 제거

2. 제약 조건

**CHECK:**
`sql
CHECK (price >= 0)
CHECK (stock >= 0)
``

**FOREIGN KEY:**
CASCADE 옵션으로 참조 무결성

3. 인덱스

**자주 조회되는 컬럼:**
- email, member_id, order_date

4. 트랜잭션

**주문 프로세스:**
모두 성공 or 모두 실패

더 배우기

이 튜토리얼은 기본 구조만 다룹니다.

**심화 학습:**
김영한의 실전 데이터베이스
- 파티셔닝
- 성능 최적화
- 복잡한 쿼리

---

**태그**: #쇼핑몰DB #ERD #DB설계 #튜토리얼 #MySQL