Ch11. 물리적 모델링 실습 - 테이블 정의서

테이블 정의서

• 기본값을 비고에 포함했다. 기본값은 별도의 항목으로 설명하는 것이 좋다.
• "NULL"을 제약(제약 조건)에 포함했다. "NULL"은 별도의 항목으로 설명하는 것이 좋다.
• 지면상 "NOT NULL"은 표현하지 않겠다. (여기서 기본은 "NOT NULL"로 보면 된다.)
• "AUTO_INCREMENT" 기본값은 생략했다.
• "created_at", "updated_at"의 기본값은 생략했다.
• "Unique" -> "UQ"

한글명을 영문명으로 변환할 때 앞서 준비한 용어 사전을 사용하자.

member

No	컬럼 한글명	컬럼 영문명	데이터 타입	제약 조건	비고
1	회원 ID	member_id	BIGINT	PK	회원의 고유 식별자(대리키)
2	로그인 ID	login_id	VARCHAR(50)	UQ	회원이 로그인 시 사용하는 ID
3	비밀번호	password	VARCHAR(255)		비밀번호 (반드시 암호화하여 저장)
4	회원명	member_name	VARCHAR(50)		회원의 실명
5	이메일	email	VARCHAR(100)	UQ	회원 인증 및 소통을 위한 이메일
6	주소	addr	VARCHAR(255)	NULL	상품 배송을 위한 주소
7	가입일	created_at	DATETIME		회원 가입 시점
8	수정일	updated_at	DATETIME		데이터 수정 시 자동 갱신

 

 

product

No.	컬럼 한글명	컬럼 영문명	데이터 타입	제약 조건	비고
1	상품 ID	product_id	BIGINT	PK	상품의 고유 식별자(대리 키)
2	상품명	product_name	VARCHAR(100)		상품의 이름
3	상품 가격	product_price	INT		상품의 현재 판매 가격
4	재고 수량	stock_quantity	INT		상품의 현재 재고 수량(기본값: 0)
5	상품 등록일	created_at	DATETIME		상품이 시스템에 등록된 시점
6	수정일	updated_at	DATETIME		데이터 수정 시 자동 갱신

 

인덱스

인덱스명	컬럼	비고
idx_product_name	product_name	상품명 검색 성능 향상을 위한 인덱스

 

orders

No.	컬럼 한글명	컬럼 영문명	데이터 타입	제약 조건	비고
1	주문 ID	order_id	BIGINT	PK	주문의 고유 식별자 (대리 키)
2	회원 ID	member_id	BIGINT	FK	주문한 회원(member.member_id 참조)
3	주문일시	ordered_at	DATETIME		고객이 주문한 비즈니스 시점의 시간 (백엔드 애플리케이션에서 전달)
4	주문 상태	order_status	VARCHAR(20)		예: ORDERED, CANCELED 등 (기본값: ORDERED)
5	총 주문 금액	total_amount	INT		[역정규화] 주문 상품 금액의 총합
6	생성일	created_at	DATETIME		주문 데이터가 시스템에 생성된 시점
7	수정일	updated_at	DATETIME		주문 상태 변경 등 데이터 수정 시 자동 갱신

 

인덱스

인덱스명	컬럼	비고
idx_order_status_ordered_at	order_status, ordered_at	관리자의 주문 상태 및 기간별 조회 성능 향상을 위한 복합 인덱스

 

order_item

No.	컬럼 한글명	컬럼 영문명	데이터 타입	제약 조건	비고
1	주문 상품 ID	order_item_id	BIGINT	PK	주문 상품의 고유 식별자(대리 키)
2	주문 ID	order_id	BIGINT	FK	해당 상품이 속한 주문 (orders.order_id 참조)
3	상품 ID	product_id	BIGINT	FK	주문된 상품 (product.product_id 참조)
4	주문 상품명	product_name	VARCHAR(100)		[역정규화] 주문 당시의 상품명 (스냅샷)
5	주문 가격	order_price	INT		주문 당시의 개별 상품 가격 (스냅샷)
6	주문 수량	order_quantity	INT		주문한 상품의 개수
7	생성일	created_at	DATETIME		데이터가 시스템에 생성된 시점
8	수정일	updated_at	DATETIME		데이터 수정 시 자동 갱신

 

인덱스

인덱스명	컬럼	비고
uq_order_id_product_id	order_id, product_id	하나의 주문에 동일한 상품을 담을 수 없는 유니크 제약 조건

 

 

delivery

No.	컬럼 한글명	컬럼 영문명	데이터 타입	제약 조건	비고
1	배송 ID	delivery_id	BIGINT	PK	배송의 고유 식별자(대리 키)
2	주문 ID	order_id	BIGINT	FK, UQ	배송될 주문(orders.order_id 참조)
3	배송 상태	delivery_status	VARCHAR(20)		예: READY, SHIPPING, COMPLETED 등 (기본값: READY)
4	운송장 번호	tracking_no	VARCHAR(50)	NULL	배송 시작 시 택배사에서 발급하는 번호
5	배송지	ship_addr	VARCHAR(255)		상품이 실제 배송될 주소
6	생성일	created_at	DATETIME		배송 데이터가 시스템에 생성된 시점
7	수정일	updated_at	DATETIME		배송 상태 변경 등 데이터 수정 시 자동 갱신

 

pay

No.	컬럼 한글명	컬럼 영문명	데이터 타입	제약 조건	비고
1	결제 ID	pay_id	BIGINT	PK	결제의 고유 식별자 (대리키)
2	주문 ID	order_id	BIGINT	FK, UQ	결제 대상 주문(orders.order_id 참조)
3	결제 수단	pay_method	VARCHAR(50)		예: CREDIT_CARD, BANK_TRANSFER 등
4	결제 금액	pay_amount	INT		실제 은행, 카드 등의 결제가 이루어진 금액, 포인트 할인 등의 금액은 제외된다.
5	결제 상태	pay_status	VARCHAR(20)		예: PAID, FAILED, CANCELED 등
6	결제 일시	paid_at	DATETIME	NULL	결제가 최종 완료된 시점, 비즈니스 관점에서 시간, 은행, 신용카드 사에서 결제된 시간
7	생성일	created_at	DATETIME		결제 데이터가 시스템에 생성된 시점
8	수정일	updated_at	DATETIME		결제 상태 변경 등 데이터 수정 시 자동 갱신

 

 

생성일과 수정일

실무에서는 모든 테이블에 생성일(created_at)과 수정일(updated_at) 컬럼을 넣는 것이 거의 표준처럼 여겨진다. 이 두 컬럼은 데이터가 언제 생성되고 마지막으로 수정되었는지 기록하는, 아주 중요한 "감사(Audit)" 정보를 담고 있다.

• created_at
  • 데이터가 처음 생성된 시점을 기록한다.
  • 이 값은 한 번 정해지면 절대 변하지 않는다.
• updated_at
  • 데이터가 마지막으로 수정된 시점을 기록한다.
  • 행의 어떤 컬럼이라도 변경되면 이 필드의 값은 현재 시간으로 자동 업데이트된다.

이 정보가 있으면 데이터 관련 문제가 발생했을 때 변경 이력을 추적하여 원인을 파악하기가 매우 용이해진다. 예를 들어, "어떤 회원의 주소가 어젯밤에 이상하게 바뀌었어요!"라는 문의가 들어왔을 때, "updated_at"을 보면 언제 변경이 일어났는지 즉시 알 수 있고, 해당 시간대의 로그를 집중적으로 분석하여 원인을 찾을 수 있다. 앞서 학습한 것 처럼 MySQL의 자동 업데이트 기능을 사용하자.

실무 팁 - 등록자(created_by), 수정자(updated_by)

실무에서는 등록일(created_at), 수정일(updated_at은 물론이고, 등록자(created_by), 수정자(updated_by)도 추가해서 관리하는 것이 좋다. 이렇게 하면 문제가 발생했을 때 관리자가 이 데이터를 수정했는지, 아니면 사용자가 이 데이터를 수정했는지 명확하게 인지할 수 있다.

등록자, 수정자는 데이터베이스에서 자동화할 수는 없고, 대신에 애플리케이션에서 관리해야 한다. 스프링 데이터 JPA와 같은 기술은 애플리케이션에서 이런 부분을 자동화해서 관리할 수 있다

 

 

주문일시는 왜 따로 있을까? (created_at vs. ordered_at)

orders 테이블 정의서를 보면 "created_at"(생성일)과 "ordered_at"(주문일시)이라는, 언뜻 보기에 비슷해 보이는 두 개의 시간 관련 컬럼이 있는 것을 보고 의문을 가졌을 것이다. "데이터가 생성된 시간이 주문 시간 아닌가?"라고 생각하기 쉽다. 하지만 실무에서는 이 둘을 명확히 구분하는 것이 매우 중요하다. 결론부터 말하자면, "created_at"은 시스템의 시간이고, "ordered_at"은 \비즈니스의 시간을 나타낸다.

 

시스템의 시간 vs. 비즈니스의 시간

• "created_at"(시스템의 시간)
  • 이 컬럼은 데이터베이스에 실제 "INSERT" 쿼리가 실행되어 행(Row)이 생성된 시점을 기록한다.
  • 이것은 순수하게 기술적인 정보다. 데이터가 언제 물리적으로 저장되었는지를 나타내며, 주로 시스템 감사(Audit), 로그 추적, 데이터 백업 및 복구 시점 확인 등 시스템 관리 목적으로 사용된다.
  • 이 값은 데이터베이스가 "DEFAULT CURRENT_TIMESTAMP"와 같은 기능을 통해 자동으로 채우도록 설정하는 것이 일반적이다.
• ordered_at(비즈니스의 시간)
  • 이 컬럼은 비즈니스 이벤트가 발생한 시점, 즉 고객이 우리 쇼핑몰에서 "주문하기" 버튼을 누른 바로 그 순간을 기록한다.
  • 이것은 고객에게 보여주고, 비즈니스 로직을 처리하는 데 사용되는 매우 중요한 정보다.
  • 예를 들어 "오후 3시 이전 주문 건은 당일 배송"과 같은 정책은 바로 이 "ordered_at"을 기준으로 판단해야 한다.
  • 이 값은 사용자의 요청을 받은 백엔드 애플리케이션이 생성하여 데이터베이스에 전달해야 한다.

두 시간이 달라지는 실무 시나리오

"그래도 두 시간은 거의 같지 않나요?"라고 생각할 수 있다. 대부분의 경우에는 그 차이가 밀리초(ms) 단위로 매우 작을 것이다. 하지만 시스템의 안정성과 데이터의 정확성을 보장해야 하는 실무에서는 두 시간이 의미 있게 달라지는 경우가 분명히 존재한다.

 

시나리오 1: 시스템 장애 또는 지연

• 가장 흔한 경우다. 사용자가 많아 서버에 부하가 걸리거나, 데이터베이스에 일시적인 성능 저하가 발생했다고 가정해 보자.
  • 고객 A가 "14:59:59"에 "주문하기" 버튼을 클릭했다. (비즈니스 이벤트 발생)
  • 백엔드 애플리케이션은 이 요청을 받고, 현재 시간 "14:59:59"를 "ordered_at" 값으로 확정한다.
  • 애플리케이션이 주문 데이터를 orders 테이블에 "INSERT" 하려고 시도한다.
  • 하지만 데이터베이스에 부하가 몰려있어 쿼리가 즉시 실행되지 못하고 2초간 대기했다.
  • "INSERT" 쿼리가 "15:00:01"에 최종적으로 실행되어 데이터가 저장되었다.
• 이 경우, 테이블에는 다음과 같이 기록된다.
  • ordered_at: "2025-08-27 14:59:59"
  • created_at: "2025-08-27 15:00:01"

만약 "created_at"만 있었다면 이 주문은 "오후 3시 이후 주문"으로 잘못 처리되어 당일 배송에서 누락되었을 것이다. "ordered_at"을 별도로 관리했기 때문에 우리는 고객의 주문 시점을 정확히 기록하고 비즈니스 규칙을 올바르게 적용할 수 있다.

 

시나리오 2: 데이터 마이그레이션

• 기존에 운영하던 쇼핑몰의 데이터를 새로운 시스템으로 이전하는 상황을 생각해 보자. 작년(2024년)의 주문 데이터를 옮겨야 한다.
  • "2024년 12월 25일"에 발생했던 주문 데이터를 새로운 orders 테이블에 "INSERT" 한다.
  • 이때, "ordered_at" 값은 당연히 원래 주문이 발생했던 "2024-12-25 10:30:00"으로 지정해서 넣어야 한다.
  • 하지만 "created_at" 값은 이 데이터가 새로운 시스템에 생성된 시점인 "2025-08-27 18:00:00"(마이그레이션 작업 시간)으로 기록될 것이다.
• 이처럼 과거 데이터를 이전하거나 복원할 때, 비즈니스 발생 시점과 데이터 생성 시점은 완전히 달라진다.
• 두 컬럼을 분리하지 않으면 과거 데이터의 중요한 시간 정보를 잃게 된다.

이처럼 "created_at"과 같은 시스템 컬럼은 기술적인 무결성을 위해, "ordered_at"과 같은 비즈니스 컬럼은 비즈니스 로직의 정확성을 위해 반드시 분리하여 관리해야 한다. pay 테이블의 결제 일시(paid_at)도 비즈니스 시간이다. 결제가 최종 완료된 시점을 의미하는데, 여기서는 은행, 신용카드 사에서 결제된 시간을 의미한다. 이 시간을 확인해야 결제에 문제가 발생했을 때 은행, 카드사와 정확한 시간을

맞추어 확인할 수 있다.