Database Schema

This document provides a comprehensive overview of the Hotelaro system's database schema, including table structures, relationships, and the multi-tenant data isolation patterns. It covers the database design that supports the hotel booking system, restaurant operations, user management, and system configuration.

For information about how this database schema integrates with the overall system architecture, see System Architecture. For details on authentication and user management that utilizes these tables, see Authentication & Security.

Multi-Tenant Database Architecture

The Hotelaro system implements a shared database multi-tenant architecture where all hotel properties share the same database tables, but data is isolated using an admin_uid field as the tenant identifier.

Core System Tables

User Management Tables

Table	Purpose	Key Fields
user	Main user accounts and hotel properties	id, email, subdomain, firstname, lastname, is_admin, usertype
setting	Global system settings	Configuration parameters
plans	Subscription plans for hotel properties	id, name, price_monthly, description

Hotel Booking Tables

The hotel booking system uses tables prefixed with hotel_booking_ to manage reservations, rooms, and related data.

Core Booking Tables

Table	Purpose	Multi-Tenant
hotel_booking_bookings	Guest reservations and booking details	Yes (admin_uid)
hotel_booking_rooms	Individual room configurations	Yes (admin_uid)
hotel_booking_room_type	Room type definitions	Yes (admin_uid)
hotel_booking_floors	Hotel floor management	Yes (admin_uid)
hotel_booking_setup	Hotel-specific configuration	Yes (admin_uid)

Additional Booking Tables

Table	Purpose
hotel_booking_amenitis	Hotel amenities and features
hotel_booking_webbuilder_sections	Website customization data

Database Access Patterns

Multi-Tenant Query Pattern

All queries in the system follow a consistent pattern for tenant isolation:

SELECT * FROM hotel_booking_[table] WHERE admin_uid = ?

This pattern is implemented across all model classes:

Domain-to-Tenant Resolution

Table Naming Conventions

The system follows consistent naming patterns:

Prefix	Purpose	Example
hotel_booking_	Core booking functionality	hotel_booking_rooms
hotel_booking_item_	Restaurant/menu items	hotel_booking_item_foods
No prefix	System-wide tables	user, setting, plans

All hotel-specific tables include an admin_uid field for tenant isolation, while system-wide tables do not require this field.

Key Relationships and Constraints

Primary Relationships

User to Hotel Data: user.id → hotel_booking_*.admin_uid
Room Management: hotel_booking_rooms.type → hotel_booking_room_type.id
Floor Assignment: hotel_booking_rooms.floor → hotel_booking_floors.id
Booking to Room: hotel_booking_bookings.room_id → hotel_booking_rooms.id
Menu Hierarchy: hotel_booking_item_category.parentid → hotel_booking_item_category.id

Data Integrity

The system maintains referential integrity through:

Foreign key relationships between booking and room tables
Hierarchical category structures for restaurant menus
Status fields for soft deletion across all entities