Delivery

Tables (15)

category

  • category_id — BIGINT — Primary key
  • category_name — VARCHAR(255) — NOT NULL

delivery_assignment

  • assignment_id — BIGINT — Primary key
  • order_id (Key) — BIGINT — Foreign key → orders.order_id — NOT NULL
  • rider_id (rider_id2) — BIGINT — Foreign key → delivery_rider.rider_id
  • assigned_at — DATETIME
  • accepted_at — DATETIME
  • picked_up_at — DATETIME
  • delivered_at — DATETIME
  • status — ENUM('assigned', 'accepted', 'picked_up', 'delivering', 'completed', 'canceled')

delivery_eta

  • eta_id — BIGINT — Primary key
  • order_id (Key) — BIGINT — Foreign key → orders.order_id — NOT NULL
  • estimated_pickup_time — DATETIME
  • estimated_arrival_time — DATETIME
  • calculated_at — DATETIME
  • source — ENUM('system', 'ai', 'rider_app')

delivery_rider

  • rider_id — BIGINT — Primary key
  • rider_name — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(20)
  • vehicle_type — ENUM('bike', 'car', 'walk')
  • status — ENUM('active', 'rest', 'off', 'banned')
  • location_lat — DOUBLE
  • location_lng — DOUBLE
  • created_at — DATETIME
  • updated_at — DATETIME

delivery_status_history

  • history_id — BIGINT — Primary key
  • order_id (Key) — BIGINT — Foreign key → orders.order_id — NOT NULL
  • status — ENUM('received', 'confirmed', 'cooking', 'cooked', 'assigned', 'picked_up', 'delivering', 'completed') — NOT NULL
  • description — VARCHAR(500)
  • created_at — DATETIME

delivery_tracking

  • tracking_id — BIGINT — Primary key
  • assignment_id (assignment_id2) — BIGINT — Foreign key → delivery_assignment.assignment_id
  • rider_id (rider_id2) — BIGINT — Foreign key → delivery_rider.rider_id
  • latitude — DOUBLE
  • longitude — DOUBLE
  • recorded_at — DATETIME

menu

  • menu_id (Key) — Primary key — NOT NULL
  • store_id (Key2) — Foreign key → store.store_id — NOT NULL
  • category_id (category_id2) — BIGINT — Foreign key → category.category_id
  • name — VARCHAR(255) — NOT NULL
  • description — VARCHAR(1000)
  • price — INT — NOT NULL

order_item

  • order_item_id (Key2) — Primary key — NOT NULL
  • order_id (Key) — BIGINT — Foreign key → orders.order_id — NOT NULL
  • menu_id (Key3) — Foreign key → menu.menu_id — NOT NULL
  • quantity — INT — NOT NULL
  • price — INT — NOT NULL

orders

  • order_id (Key) — BIGINT — Primary key — NOT NULL
  • user_id (Key2) — BIGINT — Foreign key → users.user_id — NOT NULL
  • store_id (Key3) — Foreign key → store.store_id — NOT NULL
  • order_status — ENUM('pending', 'confirmed', 'delivering', 'completed', 'canceled')
  • payment_status — ENUM('paid', 'failed', 'refunded')
  • payment_method — VARCHAR(50)
  • total_price — INT — NOT NULL
  • comment — VARCHAR(500) — ARCHAR(500
  • created_at — DATETIME
  • updated_at — DATETIME

owner

  • owner_id — BIGINT — Primary key
  • owner_name — VARCHAR(255) — NOT NULL
  • owner_email — VARCHAR(255) — NOT NULL
  • owner_phone — VARCHAR(20)
  • password_hash — VARCHAR(255) — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME

stock

  • stock_id — BIGINT — Primary key
  • menu_id (Key) — Foreign key → menu.menu_id — NOT NULL
  • stock_count — INT — NOT NULL

store

  • store_id (Key) — Primary key — NOT NULL
  • owner_id (owner_id2) — BIGINT — Foreign key → owner.owner_id
  • regist_number — VARCHAR(100)
  • store_name — VARCHAR(255) — NOT NULL
  • store_location — VARCHAR(255)
  • latitude — DOUBLE
  • longitude — DOUBLE
  • phone — VARCHAR(20)
  • created_at — DATETIME
  • updated_at — DATETIME

store_cooking_status

  • cooking_id — BIGINT — Primary key
  • order_id (Key) — BIGINT — Foreign key → orders.order_id — NOT NULL
  • store_id (Key2) — Foreign key → store.store_id — NOT NULL
  • status — ENUM('cooking', 'completed') — NOT NULL
  • updated_at — DATETIME

user_address

  • address_id — BIGINT — Primary key
  • user_id (Key) — BIGINT — Foreign key → users.user_id — NOT NULL
  • address_name — VARCHAR(50) — NOT NULL
  • road_address — VARCHAR(255) — NOT NULL
  • detail_address — VARCHAR(255)
  • latitude — DECIMAL(10, 7) — NOT NULL
  • longitude — DECIMAL(10, 7) — NOT NULL
  • is_default — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

users

  • user_id (Key) — BIGINT — Primary key — NOT NULL
  • location — VARCHAR(255) — NOT NULL
  • username — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • status — ENUM('active', 'blocked', 'deleted')
  • role — ENUM('user', 'admin')
  • created_at — DATETIME
  • updated_at — DATETIME