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