# Data Modeling Architecture Blueprint ## PHASE 1: ENTITY-RELATIONSHIP ANALYSIS **Business Domain:** Hotel Reservation System. To accurately model this domain, we need the following core entities and relationships: 1. **Hotel:** The main property. (1:N with RoomType, 1:N with Room) 2. **RoomType:** Categorization of rooms (e.g., Standard, Suite). (1:N with Room) 3. **Room:** The physical room available for booking. (1:N with Reservation) 4. **Guest:** The customer making the booking. (1:N with Reservation) 5. **Reservation:** The booking event linking a Guest to a Room for a specific timeframe. *Relationship Resolution:* A reservation typically maps a single guest to a single room for a specific date range. While a guest can have many reservations, a specific reservation record relates to one room and one guest. ## PHASE 2: SCHEMA DESIGN & DATA TYPES - **hotels:** Uses UUID for PK. Stores basic info. - **room_types:** Uses UUID for PK. Stores name and base_price (DECIMAL for currency precision). - **rooms:** Uses UUID for PK. Links to hotels and room_types. Uses VARCHAR for room_number. - **guests:** Uses UUID for PK. Stores contact info. - **reservations:** Uses UUID for PK. Links rooms and guests. Stores check_in_date, check_out_date (DATE type), and total_price (DECIMAL). Includes a 'status' enum (Pending, Confirmed, Cancelled). We include a deleted_at column for soft deletes as requested. ## PHASE 3: SQL DDL IMPLEMENTATION CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE TYPE reservation_status AS ENUM ('Pending', 'Confirmed', 'Cancelled'); CREATE TABLE hotels ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name VARCHAR(255) NOT NULL, address TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, deleted_at TIMESTAMP WITH TIME ZONE ); CREATE TABLE room_types ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name VARCHAR(100) NOT NULL, description TEXT, base_price DECIMAL(10, 2) NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, deleted_at TIMESTAMP WITH TIME ZONE ); CREATE TABLE rooms ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), hotel_id UUID NOT NULL REFERENCES hotels(id), room_type_id UUID NOT NULL REFERENCES room_types(id), room_number VARCHAR(20) NOT NULL, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, deleted_at TIMESTAMP WITH TIME ZONE, UNIQUE (hotel_id, room_number) ); CREATE TABLE guests ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, phone_number VARCHAR(20), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, deleted_at TIMESTAMP WITH TIME ZONE ); CREATE TABLE reservations ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), guest_id UUID NOT NULL REFERENCES guests(id), room_id UUID NOT NULL REFERENCES rooms(id), check_in_date DATE NOT NULL, check_out_date DATE NOT NULL, status reservation_status DEFAULT 'Pending', total_price DECIMAL(10, 2) NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, deleted_at TIMESTAMP WITH TIME ZONE, CONSTRAINT check_dates CHECK (check_out_date > check_in_date) ); ## PHASE 4: INDEXING STRATEGY **Recommendation:** We must index foreign keys to speed up joins. Additionally, querying reservations by date range and room availability are the most critical access paths. CREATE INDEX idx_rooms_hotel_id ON rooms(hotel_id); CREATE INDEX idx_rooms_room_type_id ON rooms(room_type_id); CREATE INDEX idx_reservations_guest_id ON reservations(guest_id); CREATE INDEX idx_reservations_room_id ON reservations(room_id); CREATE INDEX idx_reservations_dates ON reservations(check_in_date, check_out_date);