Hotel Management System
Relational Model
Total tables: 37
Legend: primary_key (bold + solid underline) foreign_key (italic + dashed underline)
Reference Tables
COUNTRY (country_id, country_name)
GENDER (gender_id, name)
ID_DOCUMENT_TYPE (doc_type_id, name)
Guest & Booking
GUEST (guest_id, first_name, last_name, email, phone, date_of_birth, gender_id,
country_id, preferences)
GUEST_ID_DOCUMENT (doc_id, guest_id, doc_type_id, doc_number, expiry_date,
issuing_country)
BOOKING (booking_id, guest_id, room_id, promotion_id, check_in_date,
check_out_date, num_guests, status, special_requests)
BOOKING_GUEST (booking_guest_id, booking_id, guest_id, is_primary)
CHECK_IN (checkin_id, booking_id, staff_id, checkin_time, notes)
CHECK_OUT (checkout_id, booking_id, staff_id, checkout_time, notes)
Property Management
BUILDING (building_id, name, address, num_floors)
FLOOR (floor_id, building_id, floor_number, description)
ROOM_TYPE (room_type_id, name, base_price, max_occupancy, bed_config)
ROOM (room_id, floor_id, room_type_id, room_number, status)
AMENITY (amenity_id, name, category)
ROOM_AMENITY (room_amenity_id, room_id, amenity_id)
Service Management
SERVICE_CATEGORY (category_id, name)
SERVICE (service_id, category_id, name, price)
SERVICE_ORDER (order_id, booking_id, staff_id, ordered_at, status)
SERVICE_ORDER_ITEM (item_id, order_id, service_id, quantity, unit_price)
Restaurant & Dining
RESTAURANT (restaurant_id, name, capacity)
MENU_CATEGORY (menu_cat_id, restaurant_id, name)
MENU_ITEM (item_id, menu_cat_id, name, price, is_available)
DINING_TABLE (table_id, restaurant_id, capacity, status)
FOOD_ORDER (food_order_id, booking_id, table_id, staff_id, ordered_at, status)
FOOD_ORDER_ITEM (fo_item_id, food_order_id, menu_item_id, quantity, unit_price)
Billing, Payment & Loyalty
INVOICE (invoice_id, booking_id, total_amount, discount_amount, status,
generated_at)
INVOICE_ITEM (inv_item_id, invoice_id, description, amount, item_type)
PAYMENT_METHOD (method_id, name)
PAYMENT (payment_id, invoice_id, payment_method_id, amount, paid_at, reference)
PROMOTION (promo_id, code, discount_pct, valid_from, valid_to, is_active)
BOOKING_PROMOTION (bp_id, booking_id, promo_id, discount_applied)
LOYALTY_PROGRAM (program_id, guest_id, points_balance, tier, enrolled_at)
LOYALTY_TRANSACTION (lt_id, program_id, booking_id, points_change,
transaction_type, transacted_at)
Staff & HR
DEPARTMENT (dept_id, name)
ROLE (role_id, name)
STAFF (staff_id, dept_id, role_id, first_name, last_name, email, hire_date,
is_active)
SHIFT (shift_id, name, start_time, end_time)
STAFF_SHIFT (staff_shift_id, staff_id, shift_id, work_date)
Housekeeping & Maintenance
HOUSEKEEPING_TASK (task_id, room_id, booking_id, task_type, status, scheduled_at,
completed_at)
HOUSEKEEPING_ASSIGNMENT (assignment_id, task_id, staff_id, assigned_at)
MAINTENANCE_REQUEST (request_id, room_id, reported_by, assigned_to, priority,
status, description, reported_at, resolved_at)
Inventory & Suppliers
INVENTORY_CATEGORY (cat_id, name)
SUPPLIER (supplier_id, name, contact_email, contact_phone)
INVENTORY_ITEM (item_id, cat_id, supplier_id, name, unit, quantity,
min_threshold, location)
STOCK_TRANSACTION (txn_id, item_id, staff_id, txn_type, quantity_change,
transacted_at)
Feedback & Reviews
REVIEW_CATEGORY (review_cat_id, name)
GUEST_REVIEW (review_id, booking_id, guest_id, review_cat_id, rating, comment,
submitted_at)