2차 프로젝트 테이블 설계 - DELIVERYS (배송 정보) 테이블

 

배송 정보를 관리하기 위한 deliveries 테이블을 추가합니다. 이 테이블은 orders 테이블과 1:1 또는 1:N 관계로 연결되어 주문의 배송 과정을 추적합니다.

deliveries 테이블 스키마 (MySQL 기준)

sql

CREATE TABLE `deliveries` (

  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '배송 고유 ID',

  `order_id` VARCHAR(64) NOT NULL COMMENT '상점 주문 ID (orders.id 참조)',

  `tracking_number` VARCHAR(100) NULL COMMENT '운송장 번호',

  `carrier` VARCHAR(50) NULL COMMENT '택배사 (예: CJ대한통운, 우체국)',

  `status` ENUM('PENDING', 'SHIPPED', 'IN_TRANSIT', 'DELIVERED', 'EXCEPTION') NOT NULL DEFAULT 'PENDING' COMMENT '배송 상태',

  `recipient_name` VARCHAR(100) NOT NULL COMMENT '수령인 이름',

  `address` VARCHAR(255) NOT NULL COMMENT '배송지 주소',

  `address_detail` VARCHAR(255) NULL COMMENT '상세 주소',

  `zip_code` VARCHAR(10) NOT NULL COMMENT '우편번호',

  `phone_number` VARCHAR(20) NOT NULL COMMENT '수령인 연락처',

  `shipped_at` DATETIME NULL COMMENT '발송일시',

  `delivered_at` DATETIME NULL COMMENT '배송 완료일시',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',

  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_order_id` (`order_id`),

  INDEX `idx_tracking_number` (`tracking_number`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='배송 정보 테이블';

코드를 사용할 때는 주의가 필요합니다.

주요 필드 설명 및 관계

id: 배송 테이블의 고유 식별자입니다.

order_id: 배송이 연결될 주문 ID입니다. 한 주문에 하나의 배송 정보만 있다고 가정하면, 이 필드에 UNIQUE 제약 조건을 걸어 1:1 관계를 유지할 수 있습니다.

tracking_number: 택배 조회에 사용되는 운송장 번호입니다.

carrier: 어느 택배사를 이용했는지 저장합니다.

status: 배송의 진행 상태를 나타냅니다 (대기 중, 발송 완료, 이동 중, 배송 완료 등).

배송지 정보: recipient_name, address, zip_code, phone_number 등 실제 배송에 필요한 상세 주소 정보가 포함됩니다.

통합된 테이블 구조 요약

이제 시스템은 세 개의 테이블로 구성됩니다.

orders: 주문의 메타데이터 및 주문 상태 관리.

payments: 카카오/토스를 포함한 모든 결제 거래 기록 관리.

deliveries: 주문 상품의 배송 및 위치 추적 관리.

2차 테이블 수정

CREATE TABLE `deliveries` (

  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '배송 고유 ID',

  `order_id` VARCHAR(64) NOT NULL COMMENT '상점 주문 ID (orders.id 참조)',

  `tracking_number` VARCHAR(100) NULL COMMENT '운송장 번호',

  `carrier` VARCHAR(50) NULL COMMENT '택배사 (예: CJ대한통운, 우체국)',

  `status` ENUM('PENDING', 'SHIPPED', 'IN_TRANSIT', 'DELIVERED', 'EXCEPTION') NOT NULL DEFAULT 'PENDING' COMMENT '배송 상태',

  `recipient_name` VARCHAR(100) NOT NULL COMMENT '수령인 이름',

  `address` VARCHAR(255) NOT NULL COMMENT '배송지 주소',

  `address_detail` VARCHAR(255) NULL COMMENT '상세 주소',

  `zip_code` VARCHAR(10) NOT NULL COMMENT '우편번호',

  `phone_number` VARCHAR(20) NOT NULL COMMENT '수령인 연락처',

  `shipped_at` DATETIME NULL COMMENT '발송일시',

  `delivered_at` DATETIME NULL COMMENT '배송 완료일시',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',

  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_order_id` (`order_id`),

  INDEX `idx_tracking_number` (`tracking_number`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='배송 정보 테이블';

deliveries 테이블은 주문 상품의 배송 상태를 추적하고 관리하는 핵심 데이터 저장소입니다. 관리자와 사용자는 동일한 테이블을 보지만, 목적과 접근하는 방식, 그리고 볼 수 있는 정보의 깊이가 다릅니다.

1. 사용자가 deliveries 테이블을 사용하는 방법 (목적: 추적 및 확인)

사용자는 자신이 주문한 상품이 어디쯤 오고 있는지 확인하는 것이 주된 목적입니다. 시스템은 사용자를 대신하여 데이터를 조회합니다.

사용자 행동 사용 예시 DB 사용 방식

내 주문 목록 확인 "내가 주문한 상품이 발송됐나?" orders 테이블에서 user_id로 조회 후, 연관된 deliveries 테이블의 status 확인

배송 조회 상세 "지금 택배가 어디에 있지?" deliveries 테이블의 tracking_number와 carrier 필드를 사용하여 외부 택배사 API로 연결하여 실시간 위치 정보 확인

배송지 정보 확인 "주소를 맞게 입력했나?" deliveries 테이블의 address, recipient_name, phone_number 필드 조회

사용자의 관심사: 현재 상태(status), 운송장 번호(tracking_number), 도착 예정일.

2. 관리자가 deliveries 테이블을 사용하는 방법 (목적: 관리 및 운영)

관리자는 모든 사용자의 배송 현황을 관리하고 제어하는 것이 목적입니다. 데이터 생성, 수정, 전체 조회 권한을 가집니다.

관리자 행동     사용 예시     DB 사용 방식
배송 처리     "이 주문들 오늘 출고해야지."     특정 order_id에 해당하는 레코드에 carrier와 tracking_number를 **입력(INSERT/UPDATE)**하고 status를 SHIPPED로 변경
문제 대응     "배송 중 누락된 건이 있네?"     status가 장기간 IN_TRANSIT인 레코드를 조회하여 문제 파악
고객 문의 대응     "고객이 배송지 변경 요청했네."     특정 id 레코드의 address, phone_number 필드를 수정(UPDATE)
일괄 처리     "오늘 발송된 모든 주문 리스트"     shipped_at 날짜 기준으로 전체 조회 및 엑셀 다운로드
관리자의 관심사: 모든 필드(특히 주소, 연락처, 운송장 번호 입력/수정), 모든 레코드의 상태 변경 이력.

요약

deliveries 테이블은 데이터를 저장하는 창고이고, 관리자와 사용자는 각자의 역할에 맞는 도구를 들고 창고를 드나든다고 생각하시면 쉽습니다.
사용자: 자신의 짐(order_id)이 어디 있는지 확인(status, tracking_number)만 할 수 있습니다.
관리자: 모든 짐을 관리(INSERT, UPDATE, 전체 조회)하고 상태를 변경합니다.

3차 개선 테이블

발생 문제로 인한 변경

배송 시스템 구현 방식은

배송 시스템 구현 방식은 비즈니스 모델과 운영 규모에 따라 달라지지만, 일반적으로 외부 배송 API를 연동하여 자동화하는 방식이 가장 일반적입니다.

세 가지 옵션 중 가장 일반적이고 효율적인 접근 방식은 다음과 같습니다.

가장 일반적인 방법: 외부 API 연동 (카카오맵/우체국 등)

대부분의 온라인 쇼핑몰은 이 방식을 사용합니다.

주소 입력 및 검증 (카카오맵 API):

사용자가 주문 시 카카오맵의 주소 검색 API를 사용하여 정확한 배송 주소를 입력하고 표준화된 우편번호를 받습니다. 이는 오배송을 줄이는 핵심 단계입니다.

배송 추적 (택배사 API 연동):

상품이 발송되면, 쇼핑몰 시스템은 사용 중인 택배사(예: 우체국, CJ대한통운 등)의 배송 조회 API를 통해 운송장 번호와 현재 배송 상태를 자동으로 연동합니다.

관리자는 관리자 페이지에서 자동으로 상태를 확인하고, 사용자는 별도 택배사 홈페이지 방문 없이 쇼핑몰 내에서 배송 현황을 실시간으로 확인할 수 있습니다.

관리자 직접 배송/수기 입력 방식

적합한 경우: 소규모 지역 상점, 자체 배송 차량 운영, 퀵서비스 등 표준화된 택배 시스템을 사용하지 않는 경우에 사용됩니다.

단점: 운영 규모가 커지면 관리 부담이 크고 휴먼 에러 발생 가능성이 높습니다

deliveries 테이블에 반영된 내용
앞서 설계한 deliveries 테이블은 이미 외부 API 연동을 염두에 두고 설계되었습니다.
address, address_detail, zip_code 필드는 카카오맵 주소 API를 통해 받은 표준화된 정보를 저장하기에 적합합니다.
tracking_number와 carrier, 그리고 status 필드는 우체국이나 타 택배사 API와 연동하여 자동으로 업데이트될 정보를 저장합니다

CREATE TABLE `deliveries` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '배송 고유 ID',
`order_id` VARCHAR(64) NOT NULL COMMENT '상점 주문 ID (orders.id 참조)',
`tracking_number` VARCHAR(100) NULL COMMENT '운송장 번호 (택배사 API 연동 정보)',
`carrier` VARCHAR(50) NULL COMMENT '택배사 (예: CJ대한통운, 우체국) (택배사 API 연동 정보)',
`status` ENUM('PENDING', 'SHIPPED', 'IN_TRANSIT', 'DELIVERED', 'EXCEPTION') NOT NULL DEFAULT 'PENDING' COMMENT '배송 상태 (택배사 API 연동 정보)',
`recipient_name` VARCHAR(100) NOT NULL COMMENT '수령인 이름',
`address` VARCHAR(255) NOT NULL COMMENT '배송지 주소 (카카오맵 API로 검증)',
`address_detail` VARCHAR(255) NULL COMMENT '상세 주소',
`zip_code` VARCHAR(10) NOT NULL COMMENT '우편번호 (카카오맵 API로 검증)',
`phone_number` VARCHAR(20) NOT NULL COMMENT '수령인 연락처',
`shipped_at` DATETIME NULL COMMENT '발송일시',
`delivered_at` DATETIME NULL COMMENT '배송 완료일시',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
PRIMARY KEY (`id`),
UNIQUE KEY `ux_order_id` (`order_id`),
INDEX `idx_tracking_number` (`tracking_number`),
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='배송 정보 테이블';

결론적으로, API 연동 방식이 초기 개발 비용은 들지만 장기적인 효율성과 정확성 측면에서 가장 표준적인 방식입니다.

 딜리버리 테이블에 XML 원본 데이터를 저장하는 컬럼을 두는 것은 좋은 접근

배송 테이블에 XML 원본 데이터를 저장하는 컬럼을 두는 것은 좋은 접근 방식입니다. 이는 시스템의 견고함과 유지보수성을 크게 향상시킵니다.

XML 로우데이터(Raw Data) 컬럼이 필요한 이유

택배사 API에서 받은 XML 응답을 그대로 저장하면 다음과 같은 이점이 있습니다.

1. 데이터 무결성 및 원본 보존

원본 증거 자료: 택배사 API가 예기치 않게 변경되거나 파싱 로직에 버그가 생겼을 때, 원본 XML 데이터를 통해 무엇이 문제였는지 정확하게 파악하고 디버깅할 수 있습니다.

히스토리 추적: 파싱된 데이터(예: status, tracking_number)는 변경될 수 있지만, 로우데이터는 API 호출 당시의 정확한 스냅샷을 제공합니다.

2.유연성 및 확장성

• 필드 누락 대비: 초기 설계 시 필요한 필드만 파싱했지만, 나중에 다른 정보(예: 배송 기사 연락처, 상세 배송 지연 사유)가 필요해질 수 있습니다. 원본 데이터가 있으면 DB 스키마를 수정하지 않고도 나중에 해당 정보를 다시 파싱하여 활용할 수 있습니다.

3. 쉬운 디버깅 및 테스트

• 실제 API 호출 없이 저장된 XML 데이터를 사용하여 파싱 로직을 테스트하고 재현할 수 있습니다.

deliveries 테이블 스키마 개선

deliveries 테이블에 XML 로우데이터 저장용 컬럼을 추가합니다.

sql

CREATE TABLE `deliveries` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '배송 고유 ID',
`order_id` VARCHAR(64) NOT NULL COMMENT '상점 주문 ID (orders.id 참조)',
`tracking_number` VARCHAR(100) NULL COMMENT '운송장 번호',
`carrier` VARCHAR(50) NULL COMMENT '택배사',
`status` ENUM('PENDING', 'SHIPPED', 'IN_TRANSIT', 'DELIVERED', 'EXCEPTION') NOT NULL DEFAULT 'PENDING' COMMENT '배송 상태',
-- ... (주소 및 연락처 필드 생략) ...
`shipped_at` DATETIME NULL COMMENT '발송일시',
`delivered_at` DATETIME NULL COMMENT '배송 완료일시',

**`raw_api_response` TEXT NULL COMMENT '택배사 API 원본 XML/JSON 응답 데이터',**

`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',
PRIMARY KEY (`id`),
-- ... (기타 인덱스 및 FK 생략) ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='배송 정보 테이블';

컬럼 타입 선택:
TEXT 타입은 길이가 긴 XML 데이터를 저장하기에 적합합니다.
만약 API 응답이 JSON 형식이라면, MySQL 5.7+ 버전에서는 JSON 타입을 사용하는 것이 더 효율적일 수 있습니다.
포트폴리오 부각 포인트
이 raw_api_response 컬럼을 사용하면 면접 시 **"데이터의 무결성을 위해 외부 API 통신 시 원본 데이터를 백업하여, 추후 유지보수 및 디버깅 용이성을 확보했습니다"**라고 설명하며 실무적인 고민을 했다는 점을 어필할 수 있습니다.

딜리버리 테이블 개선 2 

배송비 부분은 어케하면 좋겠음?

배송비 부분은 시스템의 여러 테이블과 로직에 걸쳐 영향을 미치는 중요한 요소입니다. 배송비 정책을 구현하기 위해 다음 영역들을 고려해야 합니다.

1. 배송비 정책 관리 (delivery_policies 테이블)

배송비 정책(예: 5만원 이상 구매 시 무료 배송, 기본 배송비 3000원)을 중앙에서 관리하는 테이블이 필요합니다.

CREATE TABLE `delivery_policies` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '정책 고유 ID',
`policy_name` VARCHAR(100) NOT NULL COMMENT '정책 이름 (예: 기본 정책, 제주도 정책)',
`base_fee` DECIMAL(10, 2) NOT NULL COMMENT '기본 배송비',
`free_shipping_threshold` DECIMAL(10, 2) NULL COMMENT '무료 배송 기준 금액 (NULL이면 기준 없음)',
`is_active` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '활성화 여부',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='배송비 정책 관리 테이블';

2. 주문 시 배송비 확정 (orders 테이블 수정)

주문 시점에 적용된 배송비와 어떤 정책이 사용되었는지 orders 테이블에 기록해야 합니다. 배송비는 시간이 지나 정책이 바뀌어도 주문 시점의 금액으로 고정되어야 합니다.

orders 테이블에 다음 컬럼을 추가합니다

ALTER TABLE `orders`
ADD COLUMN `shipping_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '주문 시 부과된 배송비',
ADD COLUMN `applied_delivery_policy_id` INT UNSIGNED NULL COMMENT '적용된 배송 정책 ID (delivery_policies.id 참조)';

3. 배송지 정보에 따른 추가 배송비 (deliveries 테이블)

제주도나 도서 산간 지역처럼 기본 배송비 외에 추가 비용이 발생하는 경우, 이 정보를 deliveries 테이블에 저장합니다.

deliveries 테이블에 다음 컬럼을 추가합니다

ALTER TABLE `deliveries`

ADD COLUMN `additional_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '도서산간 추가 배송비';

4. 구현 로직: 배송비 계산 과정

주문 및 결제 과정에서 다음 로직이 수행되어야 합니다.

상품 선택: 사용자가 상품을 장바구니에 담고 주문 페이지로 이동합니다.

주소 입력: 사용자가 배송 주소(zip_code)를 입력합니다.

정책 조회:

시스템은 현재 활성화된 기본 delivery_policies를 조회합니다.

입력된 우편번호를 기준으로 추가 배송비가 발생하는지 확인합니다 (별도의 도서산간 우편번호 목록 관리 필요).

배송비 계산:

상품 총액이 무료 배송 기준을 넘으면 base_fee, additional_fee 모두 0원이 됩니다.

기준 미달 시, 기본 배송비와 추가 배송비를 합산하여 최종 shipping_fee를 계산합니다.

주문 저장: 계산된 shipping_fee를 orders 테이블에 저장하고, total_amount에 포함시켜 결제 금액을 확정합니다.

배송비를 테이블 하나로 만드는건 못하나?

 deliveries 테이블 하나로 배송비 관련 모든 정보를 처리하는 것도 가능은 합니다.

하지만 이는 데이터베이스 설계 원칙 중 하나인 **정규화(Normalization)**를 일부 포기하는 방식이며, 몇 가지 문제점이 발생할 수 있습니다.

deliveries 테이블 하나로 통합 시 문제점

데이터 중복 및 비효율성:

배송 정책(base_fee, free_shipping_threshold 등)은 여러 주문에 걸쳐 동일하게 적용됩니다. 이 정보를 모든 deliveries 레코드에 중복해서 저장하면 데이터베이스 용량이 불필요하게 낭비됩니다.

정책 변경 시 관리 어려움:

만약 무료 배송 기준을 5만원에서 7만원으로 변경해야 한다면, delivery_policies 테이블 하나만 수정하면 됩니다.

하지만 모든 정보를 deliveries 테이블에 저장했다면, 정책을 소급 적용해야 하는지 말아야 하는지 판단이 복잡해집니다. 이미 완료된 배송 건들의 정책 정보도 함께 저장되어 있어 혼란을 줄 수 있습니다.

보고서 작성 및 분석의 어려움:

"최근 3개월간 무료 배송 정책이 적용된 주문의 비율" 같은 보고서를 만들 때, 정책 정보가 여러 레코드에 분산되어 있으면 집계 쿼리가 복잡해집니다.

권장되는 설계와 타협점
가장 좋은 방법은 배송 정책을 위한 별도 테이블을 유지하고, 주문 시점에 확정된 배송비 금액만 orders 또는 deliveries 테이블에 저장하는 것입니다.
orders 테이블에 배송비 금액 저장 (권장)
orders 테이블은 주문 시 확정된 금액(상품 총액, 할인액, 배송비)을 모두 가지고 있어야 최종 결제 금액이 명확해집니다.
orders 테이블: shipping_fee (금액), total_amount (최종 결제 금액 포함)
deliveries 테이블: additional_fee (추가 비용 금액), carrier, tracking_number 등 배송 실물 정보

deliveries 테이블에만 배송비 저장 시 (가능한 타협)

만약 테이블 수를 무조건 줄여야 한다면, 배송비를 deliveries 테이블에만 저장할 수 있습니다.

CREATE TABLE `deliveries` (

  -- ... (기존 필드 생략) ...

  `status` ENUM('PENDING', 'SHIPPED', 'IN_TRANSIT', 'DELIVERED', 'EXCEPTION') NOT NULL DEFAULT 'PENDING',

  

  **`base_shipping_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '기본 배송비',**

  **`additional_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '도서산간 추가 배송비',**

  **`final_shipping_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '최종 청구된 배송비',**

  -- ... (주소, 시간 정보, raw_api_response 등 생략) ...

);

결론

deliveries 테이블 하나로 처리하는 것은 기술적으로 가능하지만, 데이터 중복과 정책 관리의 복잡성을 초래합니다.

유지보수와 확장성을 고려했을 때, 배송 정책 테이블을 별도로 두고, 주문/배송 테이블에는 최종 금액과 참조 ID만 저장하는 표준 설계 방식을 따르는 것이 장기적으로 훨씬 유리합니다.

2차 프로젝트 테이블 설계 - 배송비 테이블 하나로 다 구현하기

 deliveries 테이블 하나로 기본 배송비 정책(5만원 미만 유료, 5만원 이상 무료)과 도서 산간 지역 추가 비용까지 처리하는 스키마입니다.

이 설계는 앞서 설명한 정규화 원칙에 일부 위배되지만, 테이블 수를 최소화하면서 필요한 정보를 저장할 수 있도록 주문 시 확정된 금액을 저장하는 방식입니다.

deliveries 테이블 스키마 (통합 배송비 포함)

CREATE TABLE `deliveries` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '배송 고유 ID',
`order_id` VARCHAR(64) NOT NULL COMMENT '상점 주문 ID (orders.id 참조)',

-- 배송비 관련 필드 --
`product_total_amount` DECIMAL(10, 2) NOT NULL COMMENT '상품 총 금액 (배송비 계산 기준)',
`base_shipping_fee` DECIMAL(10, 2) NOT NULL COMMENT '기본 배송비 (주문 시 정책 기준)',
`additional_fee` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '도서산간 추가 비용',
`final_shipping_fee` DECIMAL(10, 2) NOT NULL COMMENT '최종 청구된 배송비 (base + additional)',

-- 배송 추적 관련 필드 --
`tracking_number` VARCHAR(100) NULL COMMENT '운송장 번호',
`carrier` VARCHAR(50) NULL COMMENT '택배사',
`status` ENUM('PENDING', 'SHIPPED', 'IN_TRANSIT', 'DELIVERED', 'EXCEPTION') NOT NULL DEFAULT 'PENDING' COMMENT '배송 상태',

-- 주소 정보 관련 필드 --
`recipient_name` VARCHAR(100) NOT NULL COMMENT '수령인 이름',
`address` VARCHAR(255) NOT NULL COMMENT '배송지 주소',
`address_detail` VARCHAR(255) NULL COMMENT '상세 주소',
`zip_code` VARCHAR(10) NOT NULL COMMENT '우편번호',
`phone_number` VARCHAR(20) NOT NULL COMMENT '수령인 연락처',

-- 시간 및 기타 정보 --
`shipped_at` DATETIME NULL COMMENT '발송일시',
`delivered_at` DATETIME NULL COMMENT '배송 완료일시',
`raw_api_response` TEXT NULL COMMENT '택배사 API 원본 XML/JSON 응답 데이터',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일시',

PRIMARY KEY (`id`),
UNIQUE KEY `ux_order_id` (`order_id`),
INDEX `idx_tracking_number` (`tracking_number`),
FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='통합 배송 정보 테이블';
​

구현 로직 (Java Spring Boot)

백엔드 애플리케이션에서는 주문이 발생하는 시점에 다음 로직을 수행하여 deliveries 테이블에 정확한 배송비를 계산하여 삽입해야 합니다.

기준 설정 (코드 또는 설정 파일):

무료 배송 기준 금액: 50,000원

기본 배송비: 3,000원

도서 산간 추가비: 4,000원 (예시)

배송비 계산 및 저장:

java

// 주문 시점에 orders 테이블에서 가져온 상품 총액

BigDecimal productTotalAmount = order.getTotalAmount(); 

BigDecimal baseFee = new BigDecimal("3000");

if (productTotalAmount.compareTo(new BigDecimal("50000")) >= 0) {

    // 5만원 이상이면 기본 배송비 0원

    baseFee = BigDecimal.ZERO; 

}

// 우편번호(zipCode)를 기반으로 도서산간 지역인지 확인하는 별도 로직 필요

BigDecimal additionalFee = BigDecimal.ZERO;

if (isIslandArea(order.getZipCode())) {

    additionalFee = new BigDecimal("4000");

}

BigDecimal finalShippingFee = baseFee.add(additionalFee);

// deliveries 테이블에 INSERT 시 위 계산된 값들을 저장

Delivery newDelivery = new Delivery();

newDelivery.setOrderId(order.getId());

newDelivery.setProductTotalAmount(productTotalAmount);

newDelivery.setBaseShippingFee(baseFee);

newDelivery.setAdditionalFee(additionalFee);

newDelivery.setFinalShippingFee(finalShippingFee);

// ... 나머지 주소 정보 설정 ...

deliveryRepository.save(newDelivery);

질문

배송비 테이블 상품 총금액 2개이유는?

상품 총 금액(product_total_amount)을 deliveries 테이블에 별도 컬럼으로 저장하는 이유는 데이터 일관성과 실무적인 편의성을 위한 데이터 중복 저장(Denormalization) 기법입니다.

원칙적으로는 orders 테이블에서 가져오는 것이 맞습니다. 하지만 deliveries 테이블에도 저장함으로써 얻는 이점이 있습니다.

왜 deliveries 테이블에 컬럼이 있어야 하는가? (실무적 이유)

1.조회의 편의성 (JOIN 회피):

1.배송 관리 시스템이나 관리자 페이지에서 배송 현황(deliveries 테이블)을 조회할 때, 해당 주문의 총 금액을 보려면 항상 orders 테이블과 JOIN 해야 합니다.

2.데이터를 deliveries에 함께 저장하면, JOIN 없이 deliveries 테이블만 단독 조회해도 배송비 산정 기준 금액을 바로 확인할 수 있어 쿼리가 단순화되고 성능상 이점이 있습니다.

2.데이터의 불변성 및 스냅샷 보장:

1.주문(orders)이 생성된 후에도 주문 내역이 변경되거나 취소될 가능성이 있습니다.

2.배송 정보(deliveries)는 배송이 시작되는 시점의 확정된 금액을 기준으로 배송비가 책정된 스냅샷(snapshot) 데이터입니다.

3.만약 orders 테이블의 금액이 추후 수정되더라도, deliveries 테이블에 저장된 product_total_amount는 배송비 계산 당시의 정확한 금액을 유지합니다.

결론

상품 총 금액은 orders 테이블에 기본적으로 저장되지만, 배송비 계산의 근거 자료로서 deliveries 테이블에 중복으로 저장하는 것은 실무에서 자주 사용되는 효율적인 설계 방식입니다.

데이터베이스 정규화 원칙에 충실하려면 orders 테이블에서 가져오는 것이 맞지만, 실제 운영 환경에서는 조회 성능과 데이터의 명확성을 위해 중복 저장을 선택할 수 있습니다.

구조 좋습니다. 실무 기준으로도 꽤 잘 설계되어 있고, **배송 단일 구조(주문 1건 = 배송 1건)**을 전제로 설계했다는 게 보여요.
하지만 운영 환경을 고려하면 몇 가지를 꼭 생각해야 합니다:

---

✅ 먼저 좋은 점

요소	평가
배송비 계산 필드 분리 (base, additional, 최종)	👍 명확하고 회계처리 유리
배송 상태 ENUM 단순화	👍 MVP 기준 적절
API 원본 저장 컬럼 존재(raw_api_response)	👍 장애 추적 및 택배사 API 이슈 디버깅에 매우 유용
order_id UNIQUE → 주문 하나당 하나의 배송	👍 단순 쇼핑몰 구조에서는 문제없음

---

---

⚠️ 하지만 아래 경우가 생기면 구조가 깨집니다

시나리오	현재 스키마에서 문제 발생
부분 배송 (재고 부족 → 일부만 먼저 출고)	❌ order_id UNIQUE 때문에 2개의 배송 생성 불가
다중 판매자(Multi Vendor, 마켓플레이스, 스마트스토어 방식)	❌ 배송지가 동일해도 배송 2건
예약 / 당일출고 혼합	❌ 다른 배송타입 구분 필요
묶음배송 / 분할배송 정책 적용	❌ 불가능

---

현재 구조는:

orders (1) — (1) deliveries

하지만 실제 커머스 기준 표준은:

orders (1) — (N) delivery_groups (묶음 단위)
delivery_groups (1) — (N) deliveries (송장 단위)

📦 왜 delivery_group 이 필요한가?

예시:

주문	상품	배송구조
A	사과(농가1), 배(농가2)	배송 2건
B	모니터 + 마우스	묶음배송 1건
C	옷 3개 중 1개 품절 → 나중 배송	분할배송 2건

---

---

🔧 추천 개선안 (확장형)

① delivery_groups (묶음배송 단위)

CREATE TABLE delivery_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  group_status ENUM('PENDING','PARTIAL_SHIPPED','SHIPPED','DELIVERED','CLOSED') DEFAULT 'PENDING',
  shipping_policy TEXT NULL COMMENT '묶음배송 규칙(판매자별, 옵션별 등)',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_order(order_id),
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

---

② deliveries는 송장 단위로 분리

지금 만든 deliveries 테이블을 유지하되:

• order_id FK는 delivery_group_id로 변경하는 게 더 자연스럽습니다.

🔁 수정 버전:

ALTER TABLE deliveries 
CHANGE COLUMN order_id delivery_group_id BIGINT UNSIGNED NOT NULL;

ALTER TABLE deliveries
ADD FOREIGN KEY (delivery_group_id) REFERENCES delivery_groups(id);

---

---

🧭 배송 상태 플로우 표준화 제안

현재 ENUM은 택배사 기준인데, 주문 시스템 기준으로는 아래 구조가 더 적합합니다:

READY_FOR_PICKUP
→ SHIPPED
→ IN_TRANSIT
→ OUT_FOR_DELIVERY
→ DELIVERED
→ RETURNED / LOST / FAILED

추가하면 좋을 ENUM:

상태	이유
CANCELLED	출고 직전 취소
RETURNED	반품 회수
LOST	운송사 분실

---

---

📍 결론

항목	현재 설계 상태	개선 필요 여부
배송비 계산 구조	🔥 완벽	유지
배송상태 ENUM	👍 MVP는 OK, 확장되면 부족	개선 추천
order_id UNIQUE	❌ 부분배송 불가	구조 변경 권장
장기 확장성(마켓, 스마트스토어형)	❌ 부족	delivery_group 추가 설계 필요

---

---

다음 단계 제안

원하면 아래 중 하나 이어서 설계해 줄게:

1. 부분배송과 반품 연결하는 return_items + delivery_items 테이블

2. 라스트마일 택배 API 표준화 설계 (CJ/쿠팡/롯데/우체국 호환)

3. 배송비 정책(무료, 우체국, 제주/도서 추가요금, 부피무게) 계산 엔진 설계

4. SmartStore형 수십 판매자 대응 Multi-Shipping Architecture

---

당신 질문 핵심은 이거임 👇

배송비가 deliveries 테이블에 있는데, 결제는 orders.total_amount 기준이니까… 배송비는 어디에 속해야 맞는가?
배송비가 주문금액에 포함될까? 배송 테이블 금액이 진짜 금액인가?

이거 제대로 이해하려면 "배송비는 계산 속성이지 결제 대상 금액 그 자체가 아니다" 라는 개념을 먼저 잡아야 해요.

---

🚀 정답

배송비는 결제 금액에 포함되며, deliveries 테이블에는 "실제 배송비 정책·계산 결과의 스냅샷"만 저장하는 것이 정답입니다.

즉:

• 결제 → orders.total_amount 에 포함

• 배송 데이터 → deliveries.final_shipping_fee 는 “참고용·기록용”

---

📦 왜 이렇게 나누는가?

배송비는 "결제 대상 금액"이기 때문에 결제 전에 이미 계산이 완료되어 있어야 함.
따라서 구조 흐름은 이렇게 되어야 함:

카트 → 배송 가능지역 체크 → 배송비 계산 → 주문 생성 → 결제 → 배송 생성

즉 배송비 테이블이 결제 금액을 "만드는 기준"이지
배송 테이블이 payment의 실결제금을 들고 있는 건 아님.

---

📌 역할 정리

위치	배송비 역할	결제 금액 포함 여부
orders.total_amount	최종 결제 금액 (상품 + 배송비 - 할인)	✅ 포함
deliveries.final_shipping_fee	배송비 정책의 결과 스냅샷 (계산 기준 기록)	❌ 결제 금액 소유 아님

---

🧠 예시

상품: 30,000
배송비: 3,000
도서산간 추가요금: 2,000
총 결제: 35,000원

테이블	값
orders.total_amount	35,000
deliveries.final_shipping_fee	5,000 (3,000 + 2,000)

---

🔍 왜 deliveries에 배송비를 두냐면?

목적은 이것들 때문👇

이유	설명
📜 회계 추적	결제 당시 배송비 정책 기록 (무료 이벤트였는지 등)
🚚 반품/환불 계산	부분 환불 때 배송비 환불 여부 판단
🧾 법적 감사/로그	PG 정산 오류 대비
🧠 정책 적용	묶음 배송 / 판매자별 배송비 / 지역별 정책 확인

즉, deliveries.final_shipping_fee 는
"배송비를 계산한 증거기록" 이고
"결제 처리의 단일 소스"는 orders입니다.

---

📍 반품/환불에서의 중요한 포인트

환불할 때 배송비를 환불할지 여부는 deliveries.final_shipping_fee 값을 기반으로 로직이 작동하고,
refunds 테이블에서 다음과 같이 기록됨:

케이스	배송비 환불 여부
고객 변심 반품	❌ 배송비 보통 미환불
판매자 문제(불량/오배송)	✅ 배송비 환불
부분환불	⚠ 조건 따라 다름

그래서 배송비는 정산/환불 로직의 input 데이터이지 결제 데이터의 owner가 아님.

---

🧱 최종 구조 요약

orders.total_amount = subtotal - discounts + shipping_fee
         ▲
         │ (결제 대상)
         │
deliveries.final_shipping_fee (결제 기준이 되는 배송비 스냅샷)

---

🏁 결론

✔ 배송비는 주문에 포함되어 결제된다.
✔ 배송 테이블은 배송비 계산 결과 스냅샷을 저장하는 곳이다.
✔ 결제 금액의 소유자는 orders, 배송비 정책 정보는 deliveries.

---

다음 선택지👇

원하면 이어서:

1. 배송비 정책 계산 알고리즘 설계 (무료배송, 제주/도서, 합배송, 판매자별 정책)

2. 부분 취소/반품 시 배송비 환불/차감 규칙 설계

3. PG/정산 기준 배송비 accounting model

번호 골라줘 😎