앞서 다룬 쇼핑몰 예제로 논리적 설계와 물리적 설계를 이어서 학습해보도록 하겠다.

3. 논리적 설계

논리적 설계는 개념적 설계에서 만든 ERD를 관계형 모델(테이블 구조) 로 변환하는 단계다. 아직 특정 DBMS에 종속되지 않고, 테이블·컬럼·키·관계를 추상적으로 정의하는 것이 목표다.

ERD → 관계형 모델 변환 규칙

1. 엔티티 → 테이블

각 엔티티는 그대로 테이블이 된다. 엔티티의 속성은 컬럼이 되고, 키 속성은 PK로 지정한다.

Customer(고객ID, 이름, 이메일, 주소, 연락처)
Product(상품ID, 상품명, 가격, 재고수량, 설명)
Order(주문ID, 고객ID, 주문일자, 배송주소, 주문상태)

2. 1:N 관계 → FK 추가

"다(N)" 쪽 테이블에 "일(1)" 쪽의 PK를 FK로 추가한다.

Order(주문ID, 고객ID(FK), 주문일자, 배송주소, 주문상태)

고객 한 명이 여러 주문을 가질 수 있으므로, Order 테이블에 고객ID가 FK로 들어간다.

3. M:N 관계 → 중간 테이블(교차 테이블)

M:N 관계는 직접 표현할 수 없으므로 중간 테이블로 분리한다.

위 ERD에서 상품과 주문의 관계가 여기에 해당한다. 상품 하나는 여러 주문에 포함될 수 있고, 주문 하나도 여러 상품을 가질 수 있기 때문이다.

OrderItems(주문ID(FK), 상품ID(FK), 수량, 단가)  ← 중간 테이블

위 ERD에서 주황색으로 표시한 엔티티들이 이 중간 테이블에 해당한다.

4. 식별 관계(실선) vs 비식별 관계(점선)

식별 관계에서는 부모의 PK가 자식의 PK 일부가 된다.

OrderItems PK가 (주문ID, 상품ID) 복합키인 이유가 바로 이것이다. 반면 비식별 관계에서는 부모의 PK가 자식의 FK로만 존재하고 자식은 독립적인 PK를 따로 갖는다.

쇼핑몰 ERD 변환 결과

이전 Step3에서 만든 ERD를 관계형 모델로 변환하면 다음과 같다.

Customer(_고객ID_, 이름, 이메일, 주소, 연락처)
Category(_카테고리ID_, 카테고리명, 상위카테고리ID)
Product(_상품ID_, 카테고리ID(FK), 상품명, 가격, 재고수량, 설명)
Order(_주문ID_, 고객ID(FK), 주문일자, 배송주소, 주문상태)
OrderItems(주문ID(FK), 상품ID(FK), 수량, 단가)   ← 복합 PK
Cart(_카트ID_, 고객ID(FK), 상품ID(FK), 수량)
Review(_리뷰ID_, 고객ID(FK), 상품ID(FK), 평점, 내용, 작성일)
Transaction(_트랜잭션ID_, 주문ID(FK), 결제일자, 결제수단, 금액, 상태)

밑줄 친 항목이 PK, (FK) 표시가 외래키다.

관계형 모델 단계에서는 컬럼의 데이터 타입이나 NULL 허용 여부는 아직 결정하지 않는다. 그건 다음 단계인 물리적 설계에서 다룬다.

4. 물리적 설계

물리적 설계는 논리적 설계의 관계형 모델을 특정 DBMS에서 실제로 동작하는 SQL로 구체화하는 단계다.

컬럼의 데이터 타입, NULL 허용 여부, 인덱스 설계, 파티셔닝 등 실제 저장 방식을 결정한다. 이 단계는 DBA의 영역이지만, 개발자도 기본 원칙은 알아야 한다.

테이블 생성 (CREATE TABLE)

논리적 모델의 Order 테이블을 SQL로 표현하면 다음과 같다.

CREATE TABLE orders (
  order_id    BIGINT       PRIMARY KEY,
  customer_id BIGINT       NOT NULL REFERENCES customer(customer_id),
  order_date  TIMESTAMP    NOT NULL DEFAULT NOW(),
  address     VARCHAR(255) NOT NULL,
  status      VARCHAR(20)  NOT NULL DEFAULT 'PENDING'
);

논리적 설계에서는 단순히 (주문ID, 고객ID, 주문일자, ...)로만 나열했지만, 물리적 설계에서는 각 컬럼의 타입과 제약조건을 명시한다.

인덱스 설계

물리적 설계에서 가장 중요한 의사결정 중 하나가 인덱스다. 데이터를 삽입할 때 PK 값 기준으로 정렬되어 저장되기 때문에, PK 설계 자체가 성능에 직접 영향을 준다. 예를 들어 PK를 숫자(BIGINT)로 설정하면 순차 삽입이 일어나 B-Tree 인덱스가 효율적으로 유지되지만, 문자열(VARCHAR)이나 UUID를 PK로 쓰면 랜덤 삽입이 발생해 페이지 분할이 잦아진다.

인덱스를 추가해야 할 대표적인 경우는 다음과 같다.

• 자주 조회하는 FK 컬럼: orders.customer_id로 특정 고객의 주문 목록을 자주 조회한다면 해당 컬럼에 인덱스를 추가한다.
• WHERE 절에 자주 등장하는 컬럼: orders.status로 주문 상태 필터링을 자주 한다면 인덱스 후보가 된다.
• 정렬·범위 조건 컬럼: order_date 범위 조회가 잦다면 인덱스가 유효하다.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status      ON orders(status);

단, 인덱스는 조회 성능을 높이는 대신 쓰기(INSERT/UPDATE/DELETE) 성능을 낮추고 저장 공간을 추가로 사용한다.

모든 컬럼에 인덱스를 붙이는 것이 아니라, 실제 쿼리 패턴을 분석해 필요한 곳에만 적용하는 것이 원칙이다.

성능 개선할 때는 물리적 설계(인덱스 설계)을 해서 성능이 잘 나오는지 확인해야 한다.

4단계 설계 흐름 정리

요구사항 분석에서 데이터 중심으로 설계를 시작하면, ERD → 관계형 모델 → SQL로 이어지는 흐름이 자연스럽게 연결된다.

각 단계를 명확하게 할수록 이후 API 설계나 쿼리 최적화 작업도 훨씬 수월해진다!