Logistics-system-project
Tables (12)
p_ai (AI 생성 기록)
- ai_id (AI 생성 기록 ID) — varchar(36) — Primary key — NOT NULL
- order_id (주문 ID) — varchar(36) — Foreign key → p_order.order_id — NOT NULL
- departure_hub_id (출발지 허브 ID) — varchar(36) — Foreign key → p_hub.hub_id
- arrival_hub_id (도착지 허브 ID) — varchar(36) — Foreign key → p_hub.hub_id
- delivery_deadline (납기 기한) — timestamp
- recommended_dispatch_time (최종 발송 시한) — timestamp
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_company (업체)
- company_id (업체 ID) — varchar(36) — Primary key
- user_id (업체담당자 ID) — bigint — Foreign key → p_user.user_id — NOT NULL
- hub_id (관리 허브 ID) — varchar(36) — Foreign key → p_hub.hub_id
- company_name (업체명) — VARCHAR(100)
- address (업체 주소) — varchar(255)
- location_x (경도) — double
- location_y (위도) — double
- company_type (업체타입) — enum
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_delivery (배송)
- delivery_id (배송 ID) — varchar(36) — Primary key — NOT NULL
- order_id (주문 ID) — varchar(36) — Foreign key → p_order.order_id — NOT NULL
- status (배송 상태) — enum(HUB_WAITING, HUB_IN_TRANSIT, HUB_ARRIVED, OUT_FOR_DELIVERY,MOVING_TO_COMPANY)
- departure_hub_id (출발 허브 ID) — UUID — Foreign key → p_hub.hub_id
- arrival_hub_id (목적지 허브 ID) — UUID — Foreign key → p_hub.hub_id
- address (배송지 주소) — varchar(255)
- recipient_name (수령인 이름) — varcher(10)
- recipient_slack_id (수령인 슬랙 ID) — varchar(30)
- company_delivery_staff_id (업체 배송 담당자 ID) — bigint
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_delivery_history (배송 경로 기록)
- delivery_history_id (배송 경로 기록 ID) — varchar(36) — Primary key — NOT NULL
- delivery_id (배송 ID) — varchar(36) — Foreign key → p_delivery.delivery_id — NOT NULL
- delivery_staff_id (배송 담당자 ID) — bigint — Foreign key → p_delivery_staff.delivery_staff_id — NOT NULL
- sequence (배송 경로 상 허브의 순번) — int
- departure_hub_id (출발 허브 ID) — UUID — Foreign key → p_hub.hub_id
- arrival_hub_id (도착 허브 ID) — UUID — Foreign key → p_hub.hub_id
- estimated_distance (예상 거리) — decimal(10,2)
- estimated_duration (예상 소요시간(분)) — int
- actual_distance (실제 거리) — decimal(10,2)
- actual_duration (실제 소요시간(분)) — int
- status (현재 상태) — enum(HUB_WAITING, HUB_IN_TRANSIT, HUB_ARRIVED, OUT_FOR_DELIVERY)
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_delivery_staff (배송 담당자)
- delivery_staff_id (배송 담당자 ID) — bigint — Foreign key → p_user.user_id — NOT NULL
- staff_type (배송 담당자 타입) — enum
- delivery_order (배송 순번) — bigint
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timstamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_delivery_staff_hub ((업체) 배송 담당자 허브)
- delivery_staff_hub_id (배송 담당자 허브 ID) — UUID — Primary key — NOT NULL
- delivery_staff_id (배송 담당자 ID) — bigint — Foreign key → p_delivery_staff.delivery_staff_id — NOT NULL
- hub_id (소속 허브 ID) — UUID — Foreign key → p_hub.hub_id
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timstamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_hub (허브)
- hub_id (허브 ID) — varchar(36) — Primary key
- hub_name (허브 이름) — varchar(100)
- user_id (허브관리자 ID) — bigint — Foreign key → p_user.user_id — NOT NULL
- central_hub_id (중앙 허브 ID) — varchar(36)
- address (주소) — varchar(255)
- location_x (경도) — double
- location_y (위도) — double
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_hub_movement (허브 이동정보)
- hub_movement_id (허브 이동정보 ID) — varchar(36) — Primary key
- departure_hub_id (출발 허브 ID) — varchar(36) — Foreign key → p_hub.hub_id
- arrival_hub_id (도착 허브 ID) — varchar(36) — Foreign key → p_hub.hub_id
- time (소요시간) — int
- distance (이동거리) — int
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_order (주문)
- order_id (주문 ID) — varchar(36) — Primary key — NOT NULL
- product_id (상품 ID) — varchar(36) — Foreign key → p_product.product_id — NOT NULL
- supplier_company_id (공급 업체 ID) — varchar(36) — Foreign key → p_company.company_id
- receiver_company_id (수령 업체 ID) — varchar(36) — Foreign key → p_company.company_id
- status (주문 상태) — enum(PENDING,COMPLETED,CANCELLED)
- quantity (수량) — int
- total_price (총 주문 금액) — int
- order_date (주문 일자) — timestamp
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- createrd_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_product (상품)
- product_id (상품 ID) — varchar(36) — Primary key — NOT NULL
- company_id (업체 ID) — varchar(36) — Foreign key → p_company.company_id
- product_name (상품명) — varchar(100)
- description (상품 설명) — text
- price (상품 단가) — int
- stock (재고 수량) — int
- category (상품 카테고리) — varchar(50)
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_slack_message (슬랙 메시지)
- slack_message_id (슬랙 메시지 ID) — varchar(36) — Primary key — NOT NULL
- sender_user_id (발신 사용자 ID) — bigint — Foreign key → p_user.user_id — NOT NULL
- receiver_user_id (수신 사용자 ID) — bigint — Foreign key → p_user.user_id — NOT NULL
- message_content (메시지 내용) — text
- sent_at (발송 날짜) — timestamp
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varchar(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)
p_user (사용자)
- user_id (사용자 ID) — bigint — Primary key — NOT NULL
- username (사용자명) — varchar(10)
- password (비밀번호) — varchar(60)
- slack_id (슬랙 ID) — varchar(30)
- role (권한) — enum
- is_approved (승인 여부) — boolean
- is_deleted (삭제 여부) — boolean
- created_at (생성일) — timestamp
- created_by (생성자) — varchar(20)
- updated_at (수정일) — timestamp
- updated_by (수정자) — varcahr(20)
- deleted_at (삭제일) — timestamp
- deleted_by (삭제자) — varchar(20)