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