Published 2025. 3. 3. 10:02

[Prodify] DB 설계 - 주문 테이블 (orders)

Prodify - 토이프로젝트

수정 기록

2025.03.06 - `orders` 테이블의 `order_status`, `payment_status` 타입을 VARCHAR(50) → ENUM 으로 변경

처음 테이블 계획

첫 번째 수정

수정점

order는 sql의 예약어(order by)이므로 orders로 이름 변경
사용자명, 상품명 누락되어서 추가함
total_price의 decimal(10, 2)를 decimal(10, 0)으로 변경

1정규형: 릴레이션에 속하는 속성의 속성 값이 모두 원자값(Atomic Value)만으로 구성되어야 함

decimal(10, 0): 소수점 이하 자리가 없는 정수형 값 저장 / decimal(전체 자릿수, 소수부 자릿수)

현재 테이블의 문제점

1.`user_id`, `user_name`, `product_id`, `product_name` 이 같은 테이블에 있으므로 1정규형 충족 불가

사용자 '홍길동'이 여러 번 주문을 할 경우, 현재 설계에서는 각 주문마다 user_id와 함께 user_name인 '홍길동'이 반복해서 저장됨
사용자 '홍길동'이 이름을 '김철수'로 변경한다면, 모든 주문 기록에서 user_name을 수정해야 함
이럴 경우 갱신 이상 (update anomaly) 발생함, 하나의 데이터를 변경하기 위해 여러 레코드를 수정해야 하기 때문
`user_id`, `user_name`은 `users` 테이블에, `product_id`, `product_name`은 `products` 테이블에 저장하기

2. 1:N 구조 반영 불가

현재 구조에선 한 주문에 하나의 상품만 저장 가능함, 왜냐하면 `product_id`와 `product_name` 필드가 각각 하나씩 있기 때문

주문1: order_id=1001, user_id=500, product_id=101, product_name="노트북", quantity=1, total_price=1000000
주문2: order_id=1002, user_id=500, product_id=202, product_name="마우스", quantity=1, total_price=50000
주문3: order_id=1003, user_id=500, product_id=303, product_name="키보드", quantity=1, total_price=150000

하나의 주문이 DB에선 세 개로 쪼개지므로 배송지 정보, 결제 정보 등이 중복되어서 같은 주문 관계인지 알 수 없게 됨
따라서 주문 테이블과 주문 상품 테이블, 즉 테이블을 분리해야 함
주문 상품 테이블에선 `order_id`, `product_id`를 FK로 지정해야 함

두 번째 수정

CREATE TABLE orders
(
    `order_id`        INT              NOT NULL    AUTO_INCREMENT COMMENT '주문 id', 
    `total_price`     DECIMAL(10,0)    NOT NULL    COMMENT '총 결제 금액', 
    `order_status`    VARCHAR(50)      NOT NULL    COMMENT '주문 상태', 
    `payment_status`  VARCHAR(50)      NOT NULL    COMMENT '결제 상태', 
    `created_at`      TIMESTAMP        NOT NULL    COMMENT '주문일', 
    `updated_at`      TIMESTAMP        NOT NULL    COMMENT '수정일', 
     PRIMARY KEY (order_id)
);

CREATE TABLE order_items
(
    `item_id`     INT    NOT NULL    AUTO_INCREMENT COMMENT '주문 상품 id', 
    `order_id`    INT    NOT NULL    COMMENT '주문 id', 
    `product_id`  INT    NOT NULL    COMMENT '판매 상품 id', 
    `quantity`    INT    NOT NULL    COMMENT '주문 수량', 
     PRIMARY KEY (item_id)
);
Foreign Key SQL - order_items(order_id) -> orders(order_id)

※ users, products 테이블은 추후 게시글에 쓸 예정

배운 점

테이블명은 복수로, 컬럼명은 단수로 할 것 (orders - order_id)
`total_price`를 사용해도 될지, 아닐지는 장단점이 있음

저장할 시 장점: 빠른 조회 가능, 주문 시점 가격 유지 가능하므로
저장할 시 단점: SUM(item_price * quantity) 로 계산하면 주문 금액 구할 수 있음
저장 안 하고 실시간 계산 시 장점: 용량 절약 가능
저장 안 할 시 단점: 할인, 쿠폰 적용을 하려면 과거 주문 가격 정보가 필요함 + 상품 가격이 추후에 변동될 수 있으므로 갖고 있어야 함