Order System

Tables (14)

branch_info

  • branch_id — VARCHAR(50) — Primary key — NOT NULL
  • branch_name — VARCHAR(100) — NOT NULL
  • branch_address — VARCHAR(255)

branch_manager

  • manager_id — VARCHAR(50) — Primary key — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • manager_name — VARCHAR(50) — NOT NULL
  • branch_id — VARCHAR(50) — Foreign key → branch_info.branch_id — NOT NULL

branch_product_status

  • branch_id — VARCHAR(50) — Foreign key → branch_info.branch_id — NOT NULL
  • product_id — INT — Foreign key → product.product_id — NOT NULL
  • branch_status — VARCHAR(20) — NOT NULL

category

  • category_id — INT — Primary key — NOT NULL
  • category_name — VARCHAR(20) — NOT NULL

hq_admin

  • admin_id — VARCHAR(50) — Primary key — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • admin_name — VARCHAR(50) — NOT NULL
  • employee_no — VARCHAR(50) — NOT NULL

option_group

  • group_id — INT — Primary key — NOT NULL
  • group_name — VARCHAR(100) — NOT NULL
  • is_required — BOOLEAN — NOT NULL
  • is_multiple — BOOLEAN — NOT NULL

option_item

  • option_item_id — INT — Primary key — NOT NULL
  • group_id — INT — Foreign key → option_group.group_id — NOT NULL
  • item_name — VARCHAR(100) — NOT NULL
  • add_price — INT — NOT NULL

order_item

  • order_item_id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → orders.order_id — NOT NULL
  • product_id — INT — Foreign key → product.product_id — NOT NULL
  • quantity — INT — NOT NULL
  • item_price — INT — NOT NULL

order_item_option

  • order_item_id — INT — Foreign key → order_item.order_item_id — NOT NULL
  • option_item_id — INT — Foreign key → option_item.option_item_id — NOT NULL

orders

  • order_id — INT — Primary key — NOT NULL
  • branch_id — VARCHAR(50) — Foreign key → branch_info.branch_id — NOT NULL
  • total_price — INT — NOT NULL
  • payment_method — VARCHAR(20) — NOT NULL
  • order_date — TIMESTAMP — NOT NULL

product

  • product_id — INT — Primary key — NOT NULL
  • category_id — INT — Foreign key → category.category_id — NOT NULL
  • product_name — VARCHAR(100) — NOT NULL
  • base_price — INT — NOT NULL
  • description — TEXT
  • product_type — VARCHAR(20) — NOT NULL
  • target_branch_id — VARCHAR(50) — Foreign key → branch_info.branch_id
  • master_status — VARCHAR(20) — NOT NULL
  • cup_size — VARCHAR(20)
  • calories — DECIMAL(5,1)
  • carbohydrates — DECIMAL(5,1)
  • sugars — DECIMAL(5,1)
  • protein — DECIMAL(5,1)
  • fat — DECIMAL(5,1)
  • saturated_fat — DECIMAL(5,1)
  • trans_fat — DECIMAL(5,1)
  • sodium — DECIMAL(6,1)
  • cholesterol — DECIMAL(5,1)
  • caffeine — DECIMAL(5,1)

product_option_map

  • product_id — INT — Foreign key → product.product_id — NOT NULL
  • group_id — INT — Foreign key → option_group.group_id — NOT NULL

product_tag_map

  • product_id — INT — Foreign key → product.product_id — NOT NULL
  • tag_id — INT — Foreign key → tag.tag_id — NOT NULL

tag

  • tag_id — INT — Primary key — NOT NULL
  • tag_name — VARCHAR(50) — NOT NULL