Test1

Tables (9)

Business

  • business_id — INT — Primary key — NOT NULL
  • user_id — INT — NOT NULL — 사업자 계정(User) 참조
  • business_name — VARCHAR(255) — NOT NULL — 사업자 이름
  • business_number — VARCHAR(20) — NOT NULL — 사업자 등록번호
  • license_image_url — TEXT — NOT NULL — S3 등록증 이미지 URL
  • status — ENUM('pending', 'approved', 'rejected', 'suspended') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

Hotel

  • hotel_id — INT — Primary key — NOT NULL
  • business_id — INT — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • star_rating — INT — NOT NULL
  • description — TEXT
  • image_urls — TEXT — 호텔 이미지 URL 리스트(JSON)
  • country — VARCHAR(50) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP

Payment

  • payment_id — INT — Primary key — NOT NULL
  • reservation_id — INT — NOT NULL
  • payment_method — VARCHAR(50) — NOT NULL
  • base_price — INT — NOT NULL
  • final_price — INT — NOT NULL
  • tax — INT — NOT NULL
  • discount — INT — NOT NULL
  • service_fee — INT — NOT NULL
  • status — ENUM('completed','cancelled','refunded') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • refunded_at — TIMESTAMP

Reservation

  • reservation_id — INT — Primary key — NOT NULL
  • user_id — INT — NOT NULL
  • room_id — INT — NOT NULL
  • transaction_id — VARCHAR(255)
  • num_adult — INT — NOT NULL
  • num_child — INT — NOT NULL
  • start_date — TIMESTAMP — NOT NULL
  • end_date — TIMESTAMP — NOT NULL
  • status — ENUM('pending','completed','cancelled') — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

Review

  • review_id — INT — Primary key — NOT NULL
  • reservation_id — INT — NOT NULL — 중복 작성 방지
  • created_at — TIMESTAMP — NOT NULL
  • star_rating — INT — NOT NULL
  • content — TEXT
  • image_urls — TEXT — 리뷰 이미지 URL 리스트(JSON)

Room

  • room_id — INT — Primary key — NOT NULL
  • hotel_id — INT — NOT NULL
  • name — ENUM('스위트룸', '디럭스룸', '스탠다드룸') — NOT NULL
  • room_size — VARCHAR(50) — NOT NULL — ex) 30m²
  • capacity_min — INT — NOT NULL
  • capacity_max — INT — NOT NULL
  • check_in_time — TIME — NOT NULL
  • check_out_time — TIME — NOT NULL
  • image_urls — TEXT — 객실 이미지 URL 리스트(JSON)

Room_Inventory

  • inventory_id — INT — Primary key — NOT NULL
  • room_id — INT — NOT NULL
  • date — DATE — NOT NULL
  • total_quantity — INT — NOT NULL
  • available_quantity — INT — NOT NULL

Room_Price_Policy

  • policy_id — INT — Primary key — NOT NULL
  • room_id — INT — NOT NULL
  • season_type — ENUM('성수기','비성수기','공휴일') — NOT NULL
  • day_type — ENUM('월~목','금','토','일') — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • price — INT — NOT NULL

User

  • user_id — INT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(13) — NOT NULL
  • email — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • date_of_birth — DATE — NOT NULL
  • address — TEXT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • role — ENUM('user','business','admin') — NOT NULL