물류관리시스템
테이블 (11)
Companies
- id — UUID — 기본 키 — NOT NULL
- hub_id — UUID — 외래 키 → Hubs.id — NOT NULL
- name — VARCHAR(255) — NOT NULL
- type — ENUM('PRODUCER', 'RECEIVER') — NOT NULL
- address — VARCHAR(255) — NOT NULL
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
Deliveries
- id — UUID — 기본 키 — NOT NULL
- order_id — UUID — 외래 키 → Orders.id — NOT NULL
- start_hub_id (id2) — UUID — 외래 키 → Hubs.id — NOT NULL
- destination_hub_id (id3) — UUID — 외래 키 → Hubs.id — NOT NULL
- current_status — ENUM('WAITING_AT_HUB', 'IN_TRANSIT', 'DELIVERED') — NOT NULL
- delivery_address — VARCHAR(255) — NOT NULL
- receiver_name — VARCHAR(255)
- receiver_slack_id — VARCHAR(255)
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
DeliveryPersons
- id — UUID — 기본 키 — NOT NULL
- hub_id — UUID — 외래 키 → Hubs.id — NOT NULL
- user_id — UUID — 외래 키 → Users.id — NOT NULL
- delivery_sequence — INT
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
DeliveryRoutes
- id — UUID — 기본 키 — NOT NULL
- delivery_id — UUID — 외래 키 → Deliveries.id — NOT NULL
- delivery_person_id — UUID — 외래 키 → DeliveryPersons.id — NOT NULL
- start_hub_id — UUID — 외래 키 → Hubs.id — NOT NULL
- end_hub_id — UUID — 외래 키 → Hubs.id — NOT NULL
- sequence — INT — NOT NULL
- estimated_distance — DECIMAL(10, 2)
- estimated_time — DECIMAL(10, 2)
- actual_distance — DECIMAL(10, 2)
- actual_time — DECIMAL(10, 2)
- current_status — ENUM('WAITING', 'IN_TRANSIT', 'ARRIVED')
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
HubRoutes
- id — UUID — 기본 키 — NOT NULL
- delivery_person_id — UUID — 외래 키 → DeliveryPersons.id — NOT NULL
- start_hub_id — UUID — 외래 키 → Hubs.id — NOT NULL
- end_hub_id — UUID — 외래 키 → Hubs.id — NOT NULL
- estimated_distance — DECIMAL(10, 2)
- estimated_time — DECIMAL(10, 2)
- is_active — BOOLEAN
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
Hubs
- id — UUID — 기본 키 — NOT NULL
- name — VARCHAR(255) — NOT NULL
- address — VARCHAR(255) — NOT NULL
- latitude — DECIMAL(10, 8)
- longitude — DECIMAL(11, 8)
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
OrderDetails
- id — UUID — 기본 키 — NOT NULL
- quantity — INT
- product_id — UUID — 외래 키 → Products.id — NOT NULL
Orders
- id — UUID — 기본 키 — NOT NULL
- receiver_id — UUID — 외래 키 → Users.id — NOT NULL
- orderdetails_id — UUID — 외래 키 → OrderDetails.id — NOT NULL
- delivery_id — UUID — 외래 키 → Deliveries.id — NOT NULL
- special_requests — TEXT
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
Products
- id — UUID — 기본 키 — NOT NULL
- company_id — UUID — 외래 키 → Companies.id — NOT NULL
- name — VARCHAR(255) — NOT NULL
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
SlackMessages
- id — UUID — 기본 키 — NOT NULL
- recipient_id (user_id) — UUID — 외래 키 → Users.id — NOT NULL
- message_content — TEXT — NOT NULL
- sent_at — TIMESTAMP — NOT NULL
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL
Users
- id — UUID — 기본 키 — NOT NULL
- slack_id — VARCHAR(50)
- username — VARCHAR(100) — NOT NULL
- password — VARCHAR(255) — NOT NULL
- role — ENUM('MASTER', 'HUB_ADMIN', 'DELIVERY_PERSON', 'COMPANY_ADMIN') — NOT NULL
- is_delete — BOOLEAN
- delete_by — VARCHAR(100)
- delete_at — TIMESTAMP
- update_by — VARCHAR(100)
- update_at — TIMESTAMP
- created_by — VARCHAR(100)
- created_at — TIMESTAMP — NOT NULL