project31
Tables (21)
BANNERS
- banner_id — INT — Primary key — NOT NULL
- title — VARCHAR(100)
- image_url — VARCHAR(255) — NOT NULL
- is_active — BOOLEAN
CATEGORIES
- category_id — INT — Primary key — NOT NULL
- category_name — VARCHAR(50) — NOT NULL
- display_order — INT
EMPLOYEES
- employee_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- login_id — VARCHAR(50) — NOT NULL
- password — VARCHAR(255) — NOT NULL
- name — VARCHAR(50) — NOT NULL
- role — ENUM('MANAGER', 'STAFF', 'PART_TIME') — NOT NULL
- status — ENUM('EMPLOYED', 'RESIGNED')
ICECREAM_FLAVORS
- flavor_id — INT — Primary key — NOT NULL
- flavor_name — VARCHAR(50) — NOT NULL
- is_active — BOOLEAN
INQUIRIES
- inquiry_id — INT — Primary key — NOT NULL
- inquiry_type — ENUM('STORE_TO_HEAD') — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- title — VARCHAR(200) — NOT NULL
- content — TEXT — NOT NULL
- answer — TEXT
- status — ENUM('WAITING', 'ANSWERED')
- created_at — TIMESTAMP
INVENTORY_ITEMS
- item_id — INT — Primary key — NOT NULL
- item_name — VARCHAR(100) — NOT NULL
- unit — VARCHAR(20) — NOT NULL
KIOSKS
- kiosk_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- kiosk_number — INT — NOT NULL
- device_serial — VARCHAR(100)
- kiosk_status — ENUM('ONLINE', 'OFFLINE', 'MAINTENANCE')
- created_at — TIMESTAMP
ORDERS
- order_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- kiosk_id — INT — Foreign key → KIOSKS.kiosk_id
- user_id — INT — Foreign key → USERS.user_id
- order_number — INT — NOT NULL
- order_type — ENUM('HERE', 'TOGO') — NOT NULL
- dry_ice_mins — INT
- order_status — ENUM('WAITING', 'PREPARING', 'COMPLETED', 'CANCELED')
- total_price — INT — NOT NULL
- created_at — TIMESTAMP
ORDER_ITEMS
- order_item_id — INT — Primary key — NOT NULL
- order_id — INT — Foreign key → ORDERS.order_id — NOT NULL
- product_id — INT — Foreign key → PRODUCTS.product_id — NOT NULL
- quantity — INT
- unit_price — INT — NOT NULL
ORDER_ITEM_FLAVORS
- item_flavor_id — INT — Primary key — NOT NULL
- order_item_id — INT — Foreign key → ORDER_ITEMS.order_item_id — NOT NULL
- flavor_id — INT — Foreign key → ICECREAM_FLAVORS.flavor_id — NOT NULL
- quantity — INT
ORDER_ITEM_OPTIONS
- item_option_id — INT — Primary key — NOT NULL
- order_item_id — INT — Foreign key → ORDER_ITEMS.order_item_id — NOT NULL
- option_id — INT — Foreign key → PRODUCT_OPTIONS.option_id — NOT NULL
PAYMENTS
- payment_id — INT — Primary key — NOT NULL
- order_id — INT — Foreign key → ORDERS.order_id — NOT NULL
- payment_method — ENUM('CARD', 'CASH', 'E_PAY', 'COUPON') — NOT NULL
- base_amount — INT — NOT NULL
- coupon_discount — INT
- point_used — INT
- final_amount — INT — NOT NULL
- payment_status — ENUM('PAID', 'REFUNDED')
- payment_date — TIMESTAMP
PRODUCTS
- product_id — INT — Primary key — NOT NULL
- category_id — INT — Foreign key → CATEGORIES.category_id — NOT NULL
- product_name — VARCHAR(100) — NOT NULL
- description — TEXT
- base_price — INT — NOT NULL
- discount_rate — DECIMAL(5,2)
- is_display — BOOLEAN
- created_at — TIMESTAMP
PRODUCT_OPTIONS
- option_id — INT — Primary key — NOT NULL
- product_id — INT — Foreign key → PRODUCTS.product_id — NOT NULL
- option_type — ENUM('CONTAINER', 'SIZE', 'TOPPING', 'SPOON', 'ETC') — NOT NULL
- option_name — VARCHAR(50) — NOT NULL
- extra_price — INT
- max_flavor_count — INT
RESTOCK_REQUESTS
- request_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- item_id — INT — Foreign key → INVENTORY_ITEMS.item_id — NOT NULL
- request_quantity — INT — NOT NULL
- status — ENUM('WAITING', 'APPROVED', 'SHIPPING', 'COMPLETED')
- requested_at — TIMESTAMP
STORES
- store_id — INT — Primary key — NOT NULL
- store_name — VARCHAR(100) — NOT NULL
- address — VARCHAR(255) — NOT NULL
- phone — VARCHAR(20)
- business_hours — VARCHAR(100)
- created_at — TIMESTAMP
STORE_EXPENSES
- expense_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- expense_date — DATE — NOT NULL
- amount — INT — NOT NULL
- description — VARCHAR(255) — NOT NULL
- created_at — TIMESTAMP
STORE_FLAVORS
- store_flavor_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- flavor_id — INT — Foreign key → ICECREAM_FLAVORS.flavor_id — NOT NULL
- is_sold_out — BOOLEAN
STORE_INVENTORY
- store_inventory_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- item_id — INT — Foreign key → INVENTORY_ITEMS.item_id — NOT NULL
- current_stock — INT
- last_updated — TIMESTAMP
STORE_PRODUCTS
- store_product_id — INT — Primary key — NOT NULL
- store_id — INT — Foreign key → STORES.store_id — NOT NULL
- product_id — INT — Foreign key → PRODUCTS.product_id — NOT NULL
- is_sold_out — BOOLEAN
USERS
- user_id — INT — Primary key — NOT NULL
- phone — VARCHAR(20) — NOT NULL
- point_balance — INT
- created_at — TIMESTAMP