restrant mangement final

Tables (16)

corner_menu

  • fixed_id — BIGINT
  • corner_id — BIGINT — NOT NULL
  • menu_id — BIGINT — NOT NULL
  • menu_kind — ENUM('fixed','daily') — NOT NULL
  • menu_date — DATE
  • image_path — VARCHAR(255)
  • is_active — TINYINT(1) — NOT NULL

coupon_policy

  • coupon_id — BIGINT
  • discount_type — ENUM('PERCENT','AMOUNT') — NOT NULL
  • minimum_price — INT
  • discount_rate — INT — NOT NULL
  • valid_from — DATETIME(3)
  • valid_until — DATETIME(3)

menu_item

  • menu_id — BIGINT
  • restaurant_id — BIGINT — NOT NULL
  • menu_name — VARCHAR(100) — NOT NULL
  • menu_image — VARCHAR(255)
  • description — VARCHAR(255)
  • is_active — TINYINT(1) — NOT NULL

order_coupon

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • coupon_id — BIGINT — NOT NULL
  • discount_krw — INT — NOT NULL

order_item

  • order_item_id — BIGINT
  • order_id — BIGINT — NOT NULL
  • fixed_id — BIGINT — NOT NULL
  • unit_price — INT — NOT NULL
  • qty — INT — NOT NULL
  • line_total — INT

orders

  • order_id — BIGINT
  • user_id — BIGINT — NOT NULL
  • placed_at — DATETIME(3) — NOT NULL
  • order_qty — INT — NOT NULL

payment

  • payment_id — BIGINT
  • order_id — BIGINT — NOT NULL
  • amount_krw — INT — NOT NULL
  • method — ENUM('CARD','KAKAOPAY','NAVERPAY','CASH') — NOT NULL
  • status — ENUM('PENDING','SUCCEEDED','FAILED','REVERSED') — NOT NULL
  • requested_at — DATETIME(3) — NOT NULL
  • approved_at — DATETIME(3)
  • pg_transaction_id — VARCHAR(100)
  • failure_reason — VARCHAR(255)
  • order_title — VARCHAR(255)

payment_event

  • event_id — BIGINT
  • payment_id — BIGINT — NOT NULL
  • event_type — ENUM('REQUESTED','APPROVED','FAILED','REFUNDED') — NOT NULL
  • fail_code — VARCHAR(64)
  • surcharge — INT
  • payload — JSON
  • created_at — DATETIME(3) — NOT NULL
  • INDEX — (payment_id, created_at)

price_plan

  • plan_id — BIGINT
  • fixed_id — BIGINT — NOT NULL
  • semester — VARCHAR(32)
  • price_student — INT — NOT NULL
  • price_staff — INT — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE

restaurant

  • restaurant_id — BIGINT
  • restaurant_name — VARCHAR(100) — NOT NULL
  • restaurant_type — ENUM('학생식당','분식당','교직원식당') — NOT NULL
  • restaurant_location — VARCHAR(100)
  • is_open — TINYINT(1) — NOT NULL

restaurant_closure

  • closure_id — BIGINT
  • restaurant_id — BIGINT — NOT NULL
  • closed_date — DATE — NOT NULL
  • closed_time — TIME
  • reason — VARCHAR(100)

restaurant_corner

  • corner_id — BIGINT
  • restaurant_id — BIGINT — NOT NULL
  • corner_name — VARCHAR(100) — NOT NULL

restaurant_hours

  • op_id — BIGINT
  • restaurant_id — BIGINT — NOT NULL
  • weekday — TINYINT — NOT NULL
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • meal_type — ENUM('breakfast','lunch','dinner') — NOT NULL

role_map

  • user_id — BIGINT — Primary key — NOT NULL
  • role — ENUM('ADMIN','RESTAURANT_ADMIN','CASHIER','USER') — Primary key — NOT NULL

user_account

  • user_id — BIGINT
  • user_name — VARCHAR(100) — NOT NULL
  • user_type — ENUM('STUDENT','STAFF','ADMIN') — NOT NULL
  • user_password — VARCHAR(255) — NOT NULL
  • login_at — DATETIME(3)
  • created_at — DATETIME(3) — NOT NULL
  • birthday — DATE
  • remained_coupon — INT — NOT NULL

user_coupon

  • user_id — BIGINT — Primary key — NOT NULL
  • coupon_id — BIGINT — Primary key — NOT NULL
  • is_used — TINYINT(1) — NOT NULL
  • issued_at — DATETIME(3) — NOT NULL
  • used_at — DATETIME(3)
  • order_id — BIGINT