submit/erd

Tables (7)

hotels

  • hotel_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL

points

  • point_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — NOT NULL
  • type — VARCHAR(30) — NOT NULL
  • amount — INT — NOT NULL
  • expires_at — DATETIME
  • reservation_id — BIGINT
  • review_id — BIGINT
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_points_user_exp

reservations

  • reservation_id — BIGINT — Primary key — NOT NULL
  • reservation_code — VARCHAR(20) — NOT NULL
  • user_id — BIGINT — NOT NULL
  • room_id — BIGINT — NOT NULL
  • check_in — DATE — NOT NULL
  • check_out — DATE — NOT NULL
  • guest_count — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_res_room_date
  • room_id (room_id2) — BIGINT — Foreign key → rooms.room_id — NOT NULL

review_images

  • review_image_id — BIGINT — Primary key — NOT NULL
  • review_id — BIGINT — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • sort_order — INT — NOT NULL
  • INDEX — idx_review_images_review
  • room_id — BIGINT — Foreign key → rooms.room_id — NOT NULL

reviews

  • review_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — NOT NULL
  • room_id — BIGINT — NOT NULL
  • content — TEXT — NOT NULL
  • rating — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_reviews_room_status_date
  • room_id (room_id2) — BIGINT — Foreign key → rooms.room_id — NOT NULL

room_images

  • room_image_id — BIGINT — Primary key — NOT NULL
  • room_id — BIGINT — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • sort_order — INT — NOT NULL
  • INDEX — idx_room_images_room_id
  • room_id (room_id2) — BIGINT — Foreign key → rooms.room_id — NOT NULL

rooms

  • room_id — BIGINT — Primary key — NOT NULL
  • hotel_id — BIGINT — Foreign key → hotels.hotel_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • capacity — TINYINT — NOT NULL
  • floor — TINYINT — NOT NULL
  • bathtub_flag — TINYINT(1) — NOT NULL
  • view_type — TINYINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — `fk_Rooms_hotels_idx`