final exam

Tables (8)

hotels

  • hotel_id — BIGINT — Primary key — NOT NULL — 호텔 식별자
  • name — VARCHAR(100) — NOT NULL — 호텔명

point_history

  • point_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • reservation_id — BIGINT — Foreign key → reservations.reservation_id — 예약 적립 출처
  • review_id — BIGINT — Foreign key → reviews.review_id — 리뷰 적립 출처
  • amount — INT — NOT NULL — 적립 포인트
  • reason — ENUM('RESERVATION', 'REVIEW') — NOT NULL — 적립 사유
  • status — ENUM('VALID', 'REVOKED') — NOT NULL
  • earned_at — DATETIME — NOT NULL — 적립일
  • expire_at — DATETIME — NOT NULL — 소멸일 (적립일 + 60일)
  • revoked_at — DATETIME — 회수 일시

reservations

  • reservation_id — BIGINT — Primary key — NOT NULL
  • reservation_no — VARCHAR(36) — NOT NULL — 사용자 제공 예약번호
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • room_id — BIGINT — Foreign key → rooms.room_id — NOT NULL
  • party_size — INT — NOT NULL — 예약 인원
  • checkin — DATE — NOT NULL — 체크인 날짜
  • checkout — DATE — NOT NULL — 체크아웃 날짜
  • state — ENUM('CONFIRMED', 'CANCELED') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • canceled_at — DATETIME — 취소 일시

review_images

  • image_id — BIGINT — Primary key — NOT NULL
  • review_id — BIGINT — Foreign key → reviews.review_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • created_at — DATETIME — NOT NULL

reviews

  • review_id — BIGINT — Primary key — NOT NULL
  • reservation_id — BIGINT — Foreign key → reservations.reservation_id — NOT NULL
  • content — TEXT
  • rating — DECIMAL(2, 1) — NOT NULL — 평점 1.0 ~ 5.0
  • exposure — TINYINT(1) — NOT NULL — 관리자 노출 승인 여부
  • created_at — DATETIME — NOT NULL

room_images

  • image_id — BIGINT — Primary key — NOT NULL
  • room_id — BIGINT — Foreign key → rooms.room_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • created_at — DATETIME — 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 — 객실명 (예: WestWing 1102)
  • capacity — TINYINT — NOT NULL — 숙박 가능 인원
  • floor — TINYINT — NOT NULL — 층
  • price — INT — NOT NULL — 1박 기준 요금
  • bathtub_flag — TINYINT(1) — NOT NULL — 욕조 여부 (1:있음, 0:없음)
  • view_type — TINYINT — NOT NULL — 전망 (0:시티뷰, 1:바다뷰, 2:마운틴뷰)
  • created_at — DATETIME — NOT NULL — 등록 일시

users

  • user_id — BIGINT — Primary key — NOT NULL — 회원 식별자 (JWT userId 와 동일)
  • name — VARCHAR(50) — NOT NULL — 회원명
  • role — ENUM('USER', 'ADMIN') — NOT NULL — 권한 (REVIEW-06 관리자)
  • created_at — DATETIME — NOT NULL