hotel_system

테이블 (47)

객실 (rooms)

  • room_id — BIGINT — 기본 키 — NOT NULL
  • hotel_id — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • room_type_id — BIGINT — 외래 키 → 객실타입.room_type_id — NOT NULL
  • room_name — VARCHAR(100) — NOT NULL
  • capacity — INT — NOT NULL
  • bed_type — VARCHAR(50)
  • base_price — INT — NOT NULL
  • status — VARCHAR(20)
  • created_at — DATETIME
  • updated_at — DATETIME

객실가격 (room_prices)

  • room_price_id — BIGINT — 기본 키 — NOT NULL
  • room_id — BIGINT — 외래 키 → 객실.room_id — NOT NULL
  • price_date — DATE — NOT NULL
  • price — INT — NOT NULL

객실이미지 (room_images)

  • room_image_id — BIGINT — 기본 키 — NOT NULL
  • room_id — BIGINT — 외래 키 → 객실.room_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • sort_order — INT

객실타입 (room_types)

  • room_type_id — BIGINT — 기본 키 — NOT NULL
  • type_name — VARCHAR(50) — NOT NULL
  • description — TEXT

객실편의시설 (room_facilities)

  • room_facility_id — BIGINT — 기본 키 — NOT NULL
  • room_id — BIGINT — 외래 키 → 객실.room_id — NOT NULL
  • facility_name — VARCHAR(100) — NOT NULL

객실히스토리 (reservation_status_history)

  • history_id — BIGINT — 기본 키 — NOT NULL
  • reservation_id — BIGINT — 외래 키 → 예약.reservation_id — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • memo — VARCHAR(255)
  • changed_at — DATETIME

결제 (payments)

  • payment_id — BIGINT — 기본 키 — NOT NULL
  • reservation_id — BIGINT — 외래 키 → 예약.reservation_id — NOT NULL
  • payment_method — VARCHAR(30) — NOT NULL
  • payment_amount — INT — NOT NULL
  • payment_status — VARCHAR(30) — NOT NULL
  • transaction_id — VARCHAR(100)
  • paid_at — DATETIME
  • created_at — DATETIME

결제로그 (payment_logs)

  • payment_log_id — BIGINT — 기본 키 — NOT NULL
  • payment_id — BIGINT — 외래 키 → 결제.payment_id — NOT NULL
  • request_data — TEXT
  • response_data — TEXT
  • created_at — DATETIME

공지사항 (notices)

  • notice_id — BIGINT — 기본 키 — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • is_visible — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

관리자접속로그 (admin_logs)

  • admin_log_id — BIGINT — 기본 키 — NOT NULL
  • admin_user_id — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • action_type — VARCHAR(50) — NOT NULL
  • target_table — VARCHAR(50)
  • target_id — BIGINT
  • description — TEXT
  • created_at — DATETIME

권한마스터테이블 (roles)

  • role_id — BIGINT — 기본 키 — NOT NULL
  • role_name — VARCHAR(30) — NOT NULL

남은객실 (room_inventory)

  • inventory_id — BIGINT — 기본 키 — NOT NULL
  • room_id — BIGINT — 외래 키 → 객실.room_id — NOT NULL
  • inventory_date — DATE — NOT NULL
  • total_count — INT — NOT NULL
  • available_count — INT — NOT NULL

리뷰 (reviews)

  • review_id — BIGINT — 기본 키 — NOT NULL
  • reservation_id — BIGINT — 외래 키 → 예약.reservation_id
  • rating — INT — NOT NULL
  • content — TEXT
  • created_at — DATETIME

리뷰이미지 (review_images)

  • review_image_id — BIGINT — 기본 키 — NOT NULL
  • review_id — BIGINT — 외래 키 → 리뷰.review_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL

리뷰태그마스터 (review_tags)

  • review_tag_id — BIGINT — 기본 키 — NOT NULL
  • tag_name — VARCHAR(50) — NOT NULL
  • tag_type — VARCHAR(20) — NOT NULL
  • sort_order — INT
  • is_active — BOOLEAN

리뷰태그매핑테이블 (review_tag_mappings)

  • review_tag_mapping_id — BIGINT — 기본 키 — NOT NULL
  • review_id (review_id2) — BIGINT — 외래 키 → 리뷰.review_id — NOT NULL
  • review_tag_id (review_tag_id2) — BIGINT — 외래 키 → 리뷰태그마스터.review_tag_id — NOT NULL

문의 (inquiries)

  • inquiry_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • status — VARCHAR(30)
  • created_at — DATETIME

문의답변 (inquiry_replies)

  • reply_id — BIGINT — 기본 키 — NOT NULL
  • inquiry_id — BIGINT — 외래 키 → 문의.inquiry_id — NOT NULL
  • admin_user_id — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME

배너정보 (banners)

  • banner_id — BIGINT — 기본 키 — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • link_url — VARCHAR(500)
  • sort_order — INT
  • is_visible — BOOLEAN

비회원예약 (reservation_guests)

  • reservation_guest_id — BIGINT — 기본 키 — NOT NULL
  • reservation_id — BIGINT — 외래 키 → 예약.reservation_id — NOT NULL
  • guest_name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(30)

설정(체크인시간 등) (system_settings)

  • setting_id — BIGINT — 기본 키 — NOT NULL
  • setting_key — VARCHAR(100) — NOT NULL
  • setting_value — VARCHAR(500)
  • description — VARCHAR(255)

소셜로그인 (user_social_accounts)

  • social_account_id — BIGINT — 기본 키 — NOT NULL
  • user_id (user_id2) — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • provider — VARCHAR(30) — NOT NULL
  • provider_user_id — VARCHAR(100) — NOT NULL
  • provider_email — VARCHAR(100)
  • provider_nickname — VARCHAR(100)
  • profile_image_url — VARCHAR(500)
  • connected_at — DATETIME

시즌정책 (season_policies)

  • season_policy_id — BIGINT — 기본 키 — NOT NULL
  • season_name — VARCHAR(50) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • price_rate — DECIMAL(5,2)

약관 (terms)

  • terms_id — BIGINT — 기본 키 — NOT NULL
  • terms_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • required — BOOLEAN
  • version — VARCHAR(20)
  • created_at — DATETIME

예약 (reservations)

  • reservation_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • hotel_id — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • check_in_date — DATE — NOT NULL
  • check_out_date — DATE — NOT NULL
  • guest_count — INT — NOT NULL
  • total_amount — INT — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME

예약QR (reservation_qr)

  • reservation_qr_id — BIGINT — 기본 키 — NOT NULL
  • reservation_id (reservation_id2) — BIGINT — 외래 키 → 예약.reservation_id — NOT NULL
  • qr_token — VARCHAR(255) — NOT NULL
  • qr_image_url — VARCHAR(500)
  • status — VARCHAR(20) — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • used_at — DATETIME
  • created_at — DATETIME

예약객실 (reservation_rooms)

  • reservation_room_id — BIGINT — 기본 키 — NOT NULL
  • reservation_id — BIGINT — 외래 키 → 예약.reservation_id — NOT NULL
  • room_id — BIGINT — 외래 키 → 객실.room_id — NOT NULL
  • room_count — INT — NOT NULL
  • price — INT — NOT NULL

자주하는질문 (faqs)

  • faq_id — BIGINT — 기본 키 — NOT NULL
  • question — VARCHAR(255) — NOT NULL
  • answer — TEXT — NOT NULL
  • is_visible — BOOLEAN
  • sort_order — INT

정산 (settlements)

  • settlement_id — BIGINT — 기본 키 — NOT NULL
  • hotel_id (hotel_id2) — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • settlement_year_month — CHAR(6) — NOT NULL
  • total_room_sales — INT — NOT NULL
  • total_facility_sales — INT — NOT NULL
  • total_sales — INT — NOT NULL
  • total_commission — INT — NOT NULL
  • total_point_discount — INT — NOT NULL
  • total_coupon_discount — INT — NOT NULL
  • total_refund_amount — INT — NOT NULL
  • net_amount — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • expected_payment_date — DATE
  • confirmed_at — DATETIME
  • paid_at — DATETIME
  • created_at — DATETIME

정산계좌 (settlement_bank_accounts)

  • settlement_bank_account_id — BIGINT — 기본 키 — NOT NULL
  • hotel_id (hotel_id2) — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • bank_name — VARCHAR(50)
  • account_number — VARCHAR(50)
  • account_holder — VARCHAR(100)
  • is_default — BOOLEAN

정산공지사항 (settlement_notices)

  • settlement_notice_id — BIGINT — 기본 키 — NOT NULL
  • title — VARCHAR(200)
  • content — TEXT
  • is_visible — BOOLEAN
  • created_at — DATETIME

정산로그 (settlement_logs)

  • settlement_log_id — BIGINT — 기본 키 — NOT NULL
  • settlement_id (settlement_id2) — BIGINT — 외래 키 → 정산.settlement_id — NOT NULL
  • admin_user_id — BIGINT
  • action_type — VARCHAR(30)
  • memo — VARCHAR(500)
  • created_at — DATETIME

정산상세 (settlement_details)

  • settlement_detail_id — BIGINT — 기본 키 — NOT NULL
  • settlement_id — BIGINT — 외래 키 → 정산.settlement_id — NOT NULL
  • settlement_date — DATE — NOT NULL
  • room_sales — INT — NOT NULL
  • facility_sales — INT — NOT NULL
  • total_sales — INT — NOT NULL
  • commission_amount — INT — NOT NULL
  • net_amount — INT — NOT NULL
  • status — VARCHAR(20)

정산수수료 (settlement_commissions)

  • settlement_commission_id — BIGINT — 기본 키 — NOT NULL
  • settlement_id (settlement_id2) — BIGINT — 외래 키 → 정산.settlement_id — NOT NULL
  • commission_name — VARCHAR(100)
  • commission_rate — DECIMAL(5,2)
  • amount — INT

찜리스트 (wishlist)

  • wishlist_id — BIGINT — 기본 키 — NOT NULL
  • user_id (user_id2) — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • hotel_id (hotel_id2) — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • created_at — DATETIME

체크인로그 (check_in_logs)

  • check_in_log_id — BIGINT — 기본 키 — NOT NULL
  • reservation_id (reservation_id2) — BIGINT — 외래 키 → 예약.reservation_id — NOT NULL
  • checked_by — BIGINT
  • check_in_at — DATETIME
  • method — VARCHAR(30)
  • memo — VARCHAR(255)

취소정책 (cancel_policies)

  • cancel_policy_id — BIGINT — 기본 키 — NOT NULL
  • hotel_id — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • days_before — INT — NOT NULL
  • refund_rate — DECIMAL(5,2) — NOT NULL
  • description — VARCHAR(255)

쿠폰 (coupons)

  • coupon_id — BIGINT — 기본 키 — NOT NULL
  • coupon_name — VARCHAR(100) — NOT NULL
  • discount_type — VARCHAR(20) — NOT NULL
  • discount_value — INT — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • status — VARCHAR(20)

편의시설 (hotel_facilities)

  • hotel_facility_id — BIGINT — 기본 키 — NOT NULL
  • hotel_id — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • facility_name — VARCHAR(100) — NOT NULL

포인트로그 (point_history)

  • point_history_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — NOT NULL
  • reservation_id — BIGINT
  • payment_id — BIGINT
  • point_type — VARCHAR(30) — NOT NULL
  • amount — INT — NOT NULL
  • balance_after — INT — NOT NULL
  • description — VARCHAR(255)
  • created_at — DATETIME

포인트정책 (point_policies)

  • point_policy_id — BIGINT — 기본 키 — NOT NULL
  • policy_name — VARCHAR(100) — NOT NULL
  • earn_rate — DECIMAL(5,2) — NOT NULL
  • min_payment_amount — INT
  • start_date — DATE
  • end_date — DATE
  • is_active — BOOLEAN
  • created_at — DATETIME

호텔 (hotels)

  • hotel_id — BIGINT — 기본 키 — NOT NULL
  • hotel_name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • address — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(30)
  • email — VARCHAR(100)
  • check_in_time — TIME
  • check_out_time — TIME
  • status — VARCHAR(20)
  • created_at — DATETIME
  • updated_at — DATETIME

호텔이미지 (hotel_images)

  • hotel_image_id — BIGINT — 기본 키 — NOT NULL
  • hotel_id — BIGINT — 외래 키 → 호텔.hotel_id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • sort_order — INT

환불 (refunds)

  • refund_id — BIGINT — 기본 키 — NOT NULL
  • payment_id — BIGINT — 외래 키 → 결제.payment_id — NOT NULL
  • refund_amount — INT — NOT NULL
  • reason — VARCHAR(255)
  • refund_status — VARCHAR(30)
  • refunded_at — DATETIME
  • created_at — DATETIME

회원 (users)

  • user_id — BIGINT — 기본 키 — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(30)
  • status — VARCHAR(20) — NOT NULL
  • point — INT
  • created_at — DATETIME
  • updated_at — DATETIME

회원권한매핑테이블 (user_roles)

  • user_role_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • role_id — BIGINT — 외래 키 → 권한마스터테이블.role_id — NOT NULL

회원보유쿠폰 (user_coupons)

  • user_coupon_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → 회원.user_id — NOT NULL
  • coupon_id — BIGINT — 외래 키 → 쿠폰.coupon_id — NOT NULL
  • used — BOOLEAN
  • used_at — DATETIME