물류관리시스템

테이블 (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