Hotel Management Module - Data Models and Database Schema

Overview

This document describes the data models, entity classes, and database schema for the Hotel Management module. The system uses Java entity classes that map to relational database tables through a custom ORM layer (EntityFacade).

Entity Overview

The Hotel Management module consists of 4 primary entities:

Entity	Table (Inferred)	Purpose	Lines of Code
CHotel	HOTEL	Hotel master data	231
CHotelRoom	HOTEL_ROOM	Room type definitions	120
CRoomCalendarEntry	ROOM_CALENDAR_ENTRY	Daily pricing entries	463
CMealPlan	MEAL_PLAN	Meal plan definitions	77

Entity Relationship Diagram

┌──────────────────────────────────────────────────┐
│                   MEAL_PLAN                      │
│                                                  │
│  PK: planId                                      │
│      plan (RO, BB, HB, FB, AI, etc.)            │
│      name, planDesc, planOrder                   │
│      adultCost, childCost                        │
└──────────────────────────────────────────────────┘
                          ↑
                          │ Referenced by mealBase
                          │ Referenced in mealSupplements JSON
                          │
┌──────────────────────────────────────────────────┐
│                    HOTEL                         │
│                                                  │
│  PK: hotelId                                     │
│  UK: shDes (hotel code)                          │
│      name, shortName, hotelDesc                  │
│      country, area, city, stars                  │
│      direct, indirect (contract types)           │
│      infant, child (age thresholds)              │
│      chkIn, chkOut (times)                       │
│      fees, feesDesc, terms                       │
│      release (cancellation days)                 │
│      reservationContact                          │
│      productId (FK to PRODUCT - optional)        │
└──────────────────────────────────────────────────┘
                          │
                          │ 1
                          │
                          │ N
                          ↓
┌──────────────────────────────────────────────────┐
│                  HOTEL_ROOM                      │
│                                                  │
│  PK: roomId                                      │
│  FK: hotelId → HOTEL.hotelId                     │
│      name, roomDesc                              │
│      maxOccupancy, maxAdults                     │
│      extraBed, extraBedRequired                  │
│      bedding, notes                              │
└──────────────────────────────────────────────────┘
                          │
                          │ 1
                          │
                          │ N
                          ↓
┌──────────────────────────────────────────────────┐
│             ROOM_CALENDAR_ENTRY                  │
│                                                  │
│  PK: calendarEntryId                             │
│  FK: roomId → HOTEL_ROOM.roomId                  │
│  UK: (roomId, market, stayDate)                  │
│      market (UAE/GCC/ROW)                        │
│      stayDate (check-in date)                    │
│      bookFrom, bookTo (booking window)           │
│      promo, vendorId                             │
│      baseRate, specDayRate, specialDay           │
│      sdRateType (ABS/ADD/PCT/PCO)                │
│      adultRate, adultRateType                    │
│      childRate, childRateType                    │
│      adultSdRate, childSdRate (special day)      │
│      extraBedRate                                │
│      rateBase (SGL/DBL/TRPL/QPL)                 │
│      mealBase (FK to MEAL_PLAN.planId)           │
│      mealSupplements (JSON text)                 │
│      stopSale, available, onRequest              │
│      mlos, sdmlos (min length of stay)           │
│      mlosDesc, notes                             │
│                                                  │
│  Transient fields (not stored):                  │
│      breakdown (String[])                        │
│      calculatedCost (Double)                     │
└──────────────────────────────────────────────────┘

Entity Details

1. CHotel - Hotel Master Entity

File: tqapp/src/main/java/com/perun/tlinq/entity/hotel/CHotel.java

Purpose: Stores hotel master data including property details, contract information, check-in/out policies, and age thresholds.

Attributes

Attribute	Type	Nullable	Description	Example	Database Column
hotelId	Integer	No (PK)	Primary key, auto-generated	1042	HOTEL_ID
shDes	String(10)	No (UK)	Unique hotel code	"ATLJMB"	SH_DES
name	String(200)	No	Full official hotel name	"Atlantis The Palm"	NAME
shortName	String(50)	Yes	Abbreviated display name	"Atlantis Palm"	SHORT_NAME
hotelDesc	String(2000)	Yes	Marketing description	"Luxury resort on Palm..."	HOTEL_DESC
productId	Integer	Yes	Link to product catalog	2045	PRODUCT_ID
direct	Boolean	Yes	Direct contract flag	true	DIRECT
indirect	Boolean	Yes	Indirect/DMC contract	false	INDIRECT
country	String(3)	Yes	ISO country code	"UAE"	COUNTRY
stars	Integer	Yes	Star rating (1-5)	5	STARS
infant	Integer	Yes	Infant max age (years)	2	INFANT
child	Integer	Yes	Child max age (years)	12	CHILD
chkIn	String(5)	Yes	Check-in time (HH:MM)	"15:00"	CHK_IN
chkOut	String(5)	Yes	Check-out time (HH:MM)	"12:00"	CHK_OUT
fees	Double	Yes	Hotel fees amount	50.00	FEES
feesDesc	String(500)	Yes	Description of fees	"City tax, service..."	FEES_DESC
terms	String(2000)	Yes	Terms and conditions	"Cancellation 72h..."	TERMS
area	String(3)	Yes	Area/Emirate code	"DXB"	AREA
city	String(50)	Yes	City name	"Dubai"	CITY
release	Integer	Yes	Release days (cancellation)	7	RELEASE
reservationContact	String(200)	Yes	Email addresses	"reservations@hotel.com"	RESERVATION_CONTACT

Area Codes

Common area codes for UAE: - DXB: Dubai - ADB: Abu Dhabi - SHJ: Sharjah - AJM: Ajman - RAK: Ras Al Khaimah - FUJ: Fujairah - UAQ: Umm Al Quwain

Business Rules

1. Unique Code: shDes must be unique across all hotels
2. Contract Type: At least one of direct or indirect must be true
3. Star Rating: If provided, must be 1-5
4. Age Thresholds:
5. Default infant age: 2
6. Default child age: 12
7. Infant < Child always
8. Times: Check-in time typically later than check-out time
9. Release Days: Number of days before check-in that free cancellation is allowed

build() Method Logic

The build() method in CHotel.java:231 constructs an entity from a Map (JSON deserialization):

public CHotel build(Map ent) {
    this.hotelId = (ent.containsKey("hotelId") ?
        TypeUtil.extractInteger(ent.get("hotelId")) : null);
    this.shDes = (String)ent.get("shDes");
    this.name = (String) ent.get("name");
    this.shortName = (String) ent.get("shortName");
    // ... (continues for all 22 attributes)
    return this;
}

Key Features: - Null-safe extraction using TypeUtil helper methods - Handles type conversions (String to Integer, String to Double, Object to Boolean) - Returns this for method chaining - Used by API layer when creating/updating hotels

---

2. CHotelRoom - Room Type Entity

File: tqapp/src/main/java/com/perun/tlinq/entity/hotel/CHotelRoom.java

Purpose: Defines room types for a hotel, including occupancy limits and bedding configuration.

Attributes

Attribute	Type	Nullable	Description	Example	Database Column
roomId	Integer	No (PK)	Primary key, auto-generated	5012	ROOM_ID
hotelId	Integer	No (FK)	Foreign key to Hotel	1042	HOTEL_ID
name	String(100)	No	Room type name	"Deluxe Ocean View"	NAME
roomDesc	String(1000)	Yes	Room description	"35 sqm, king bed..."	ROOM_DESC
maxOccupancy	Integer	No	Maximum total persons	3	MAX_OCCUPANCY
maxAdults	Integer	No	Maximum adults	2	MAX_ADULTS
extraBed	Boolean	No	Extra bed available	true	EXTRA_BED
extraBedRequired	Boolean	No	Extra bed mandatory for 3rd adult	true	EXTRA_BED_REQUIRED
bedding	String(100)	Yes	Bedding description	"1 King or 2 Twin"	BEDDING
notes	String(500)	Yes	Room notes/restrictions	"No smoking"	NOTES

Occupancy Logic

The relationship between occupancy fields:

maxOccupancy = Maximum total persons (adults + children)
maxAdults = Maximum adults allowed
maxChildren = maxOccupancy - maxAdults (implicit)

Examples:
1. maxOccupancy=2, maxAdults=2 → 2 adults only
2. maxOccupancy=3, maxAdults=2 → 2 adults + 1 child
3. maxOccupancy=4, maxAdults=2 → 2 adults + 2 children
4. maxOccupancy=3, maxAdults=3 → 3 adults (needs extra bed)

Extra Bed Logic

If extraBed = true AND extraBedRequired = true:
    - 3rd adult requires extra bed
    - Extra bed charge applies

If extraBed = true AND extraBedRequired = false:
    - Extra bed optional (room has sofa bed/daybed)
    - Extra bed charge applies only if requested

If extraBed = false:
    - No extra bed available
    - Occupancy strictly limited

Business Rules

1. Occupancy Constraints:
2. maxAdults ≤ maxOccupancy
3. maxOccupancy ≥ 1

4. maxAdults ≥ 1

5. Room Naming: Should be unique within a hotel (not enforced at DB level)

6. Extra Bed:

7. If extraBedRequired = true, then extraBed must be true
8. Extra bed typically increases max occupancy by 1

build() Method Logic

public CHotelRoom build(Map ent) {
    this.roomId = (ent.containsKey("roomId") ?
        TypeUtil.extractInteger(ent.get("roomId")) : null);
    this.hotelId = TypeUtil.extractInteger(ent.get("hotelId"));
    this.name = (String)ent.get("name");
    // ... (continues for all 10 attributes)
    return this;
}

---

3. CRoomCalendarEntry - Rate Calendar Entity

File: tqapp/src/main/java/com/perun/tlinq/entity/hotel/CRoomCalendarEntry.java

Purpose: Stores daily pricing for room types by market segment. This is the most complex entity with 31 attributes handling sophisticated pricing logic.

Attributes

Identification Fields

Attribute	Type	Nullable	Description	Database Column
calendarEntryId	Integer	No (PK)	Primary key	CALENDAR_ENTRY_ID
roomId	Integer	No (FK)	Foreign key to Room	ROOM_ID
market	String(3)	No (UK)	Market segment (UAE/GCC/ROW)	MARKET
stayDate	Date	No (UK)	Check-in date for this rate	STAY_DATE

Unique Constraint: (roomId, market, stayDate) must be unique

Booking Window Fields

Attribute	Type	Nullable	Description	Database Column
bookFrom	Date	Yes	Earliest booking date	BOOK_FROM
bookTo	Date	Yes	Latest booking date	BOOK_TO

Logic: Room can only be booked if current date is between bookFrom and bookTo

Vendor and Promotion Fields

Attribute	Type	Nullable	Description	Database Column
vendorId	Integer	Yes	Vendor/partner ID	VENDOR_ID
promo	String(20)	Yes	Promotional code	PROMO

Base Rate Fields

Attribute	Type	Nullable	Description	Database Column
baseRate	Double	No	Base room rate (normal day)	BASE_RATE
rateBase	String(4)	No	Rate basis (SGL/DBL/TRPL/QPL)	RATE_BASE

Rate Base Options: - SGL: Single occupancy (1 person) - DBL: Double occupancy (2 persons) - TRPL: Triple occupancy (3 persons) - QPL: Quadruple occupancy (4 persons)

Special Day Fields

Attribute	Type	Nullable	Description	Database Column
specialDay	Boolean	No	Is this a special day (weekend/holiday)?	SPECIAL_DAY
specDayRate	Double	Yes	Special day rate (overrides baseRate)	SPEC_DAY_RATE
sdRateType	String(3)	Yes	Special day rate calculation type	SD_RATE_TYPE

Special Day Rate Calculation Types: - ABS (Absolute): Use specDayRate directly, ignore baseRate - ADD (Additive): finalRate = baseRate + specDayRate - PCT (Percentage Add): finalRate = baseRate + (baseRate × specDayRate / 100) - PCO (Percentage Of): finalRate = baseRate × (specDayRate / 100)

Example Calculations:

Given: baseRate = 500, specDayRate = 100

ABS: finalRate = 100 (absolute override)
ADD: finalRate = 500 + 100 = 600
PCT: finalRate = 500 + (500 × 100/100) = 1000
PCO: finalRate = 500 × (100/100) = 500

Extra Occupant Fields (Normal Day)

Attribute	Type	Nullable	Description	Database Column
adultRate	Double	Yes	Extra adult supplement	ADULT_RATE
adultRateType	String(3)	Yes	Adult rate calc type (ABS/ADD/PCT/PCO)	ADULT_RATE_TYPE
childRate	Double	Yes	Extra child supplement	CHILD_RATE
childRateType	String(3)	Yes	Child rate calc type	CHILD_RATE_TYPE

Logic: Applied when occupancy exceeds rateBase (e.g., 3 adults in a DBL room)

Extra Occupant Fields (Special Day)

Attribute	Type	Nullable	Description	Database Column
adultSdRate	Double	Yes	Extra adult supplement (special day)	ADULT_SD_RATE
childSdRate	Double	Yes	Extra child supplement (special day)	CHILD_SD_RATE

Logic: Same as normal day supplements, but applied on special days

Extra Bed Fields

Attribute	Type	Nullable	Description	Database Column
extraBedRate	Double	Yes	Extra bed charge per night	EXTRA_BED_RATE

Meal Plan Fields

Attribute	Type	Nullable	Description	Database Column
mealBase	Integer	Yes (FK)	Base meal plan ID included in rate	MEAL_BASE
mealSupplements	String(JSON)	Yes	Additional meal plan options	MEAL_SUPPLEMENTS

mealSupplements JSON Structure:

[
  {
    "mealPlan": "BB",
    "adlRate": [60.0, 70.0],   // [normal, special day]
    "chldRate": [30.0, 35.0]
  },
  {
    "mealPlan": "HB",
    "adlRate": [100.0, 120.0],
    "chldRate": [50.0, 60.0]
  },
  {
    "mealPlan": "FB",
    "adlRate": [150.0, 180.0],
    "chldRate": [75.0, 90.0]
  }
]

Usage: If customer wants HB instead of base RO: - Add adlRate[0] per adult (normal day) or adlRate[1] (special day) - Add chldRate[0] per child (normal day) or chldRate[1] (special day)

Availability Fields

Attribute	Type	Nullable	Description	Database Column
available	Boolean	No	Room available for booking	AVAILABLE
stopSale	Boolean	No	Room on stop sale	STOP_SALE
onRequest	Boolean	No	Room available on request only	ON_REQUEST

Availability Logic:

If stopSale = true:
    → Room cannot be booked, shown as "Not Available"

If available = false:
    → Room not available, hidden from search

If onRequest = true:
    → Room shown but marked "On Request" (requires confirmation)

If available = true AND stopSale = false:
    → Room bookable immediately

Minimum Length of Stay (MLOS) Fields

Attribute	Type	Nullable	Description	Database Column
mlos	Integer	Yes	Minimum nights (normal day)	MLOS
sdmlos	Integer	Yes	Minimum nights (special day)	SDMLOS
mlosDesc	String(100)	Yes	MLOS description	MLOS_DESC

Example:

mlos = 1, sdmlos = 3
→ On normal days: 1-night stay OK
→ On weekends: Must book at least 3 nights

Notes Field

Attribute	Type	Nullable	Description	Database Column
notes	String(500)	Yes	Special notes for this date	NOTES

Transient Fields (Not Stored in Database)

Attribute	Type	Purpose
breakdown	String[]	Cost breakdown for display (transient)
calculatedCost	Double	Computed total cost (transient)

Usage: Populated during search operations for UI display

Entity Methods

1. build(Map ent) - CHotelRoom.java:294

Creates new entity from JSON map:

public CRoomCalendarEntry build(Map ent) {
    DateUtil du = DateUtil.getInstance();

    // Extract all fields from map
    this.roomId = TypeUtil.extractInteger(ent.get("roomId"));
    this.market = (String) ent.get("market");
    this.stayDate = du.fromString(
        (String) ent.get("stayDate"),
        DateUtil.WEB_DATE_FORMAT  // dd-MM-yyyy
    );
    this.baseRate = TypeUtil.extractDouble(ent.get("baseRate"));
    // ... (31 total fields)

    return this;
}

2. update(Map ent) - CHotelRoom.java:340

Updates existing entity from partial JSON map:

public CRoomCalendarEntry update(Map ent) {
    // Only update fields present in map
    if(ent.containsKey("baseRate"))
        this.baseRate = TypeUtil.extractDouble(ent.get("baseRate"));

    if(ent.containsKey("specialDay"))
        this.specialDay = TypeUtil.extractBool(ent.get("specialDay"));

    // ... (for all updatable fields)

    // IMPORTANT: stayDate is NOT updatable (line 379)
    this.stayDate = null; // We do not update the date!!

    return this;
}

Key Feature: The stayDate field is intentionally NOT updatable. To change the date, you must delete and recreate the entry.

3. copy(CRoomCalendarEntry ent) - CHotelRoom.java:429

Copies all fields from another entry:

public CRoomCalendarEntry copy(CRoomCalendarEntry ent) {
    this.roomId = ent.roomId;
    this.market = ent.market;
    this.stayDate = ent.stayDate;
    this.baseRate = ent.baseRate;
    // ... (copies all 31 fields)

    return this;
}

Usage: Used by "Copy Rates" feature to duplicate calendar entries across date ranges or markets.

Complex Pricing Calculation Algorithm

This entity supports sophisticated pricing calculations performed at the API layer:

For a given booking (checkIn, checkOut, adults, children, mealPlan):

1. Query all calendar entries for (roomId, market) WHERE stayDate BETWEEN checkIn AND (checkOut - 1)
2. If any night missing → Room not available for this period
3. For each night:
   a. Determine if special day: specialDay = true
   b. Get effective base rate:
      - If specialDay AND specDayRate exists:
          Apply sdRateType calculation
      - Else:
          Use baseRate
   c. Determine occupancy from rateBase (SGL=1, DBL=2, TRPL=3, QPL=4)
   d. Calculate extra adults:
      extraAdults = max(0, adults - rateBase_occupancy)
   e. Calculate extra adult charges:
      - Use adultSdRate if special day, else adultRate
      - Apply adultRateType calculation for each extra adult
   f. Calculate extra child charges:
      - Use childSdRate if special day, else childRate
      - Apply childRateType calculation for each child
   g. Add extra bed charge if required:
      - If extraBedRequired AND adults > maxAdults:
          nightCost += extraBedRate
   h. Add meal supplement if different from mealBase:
      - Find meal in mealSupplements JSON array
      - Get adlRate[0 or 1] based on special day
      - Get chldRate[0 or 1] based on special day
      - nightCost += (adlRate × adults) + (chldRate × children)
   i. Sum: nightCost = baseRate + adultCharges + childCharges + extraBed + mealSupplement

4. Total cost = sum of all night costs
5. Sell price = totalCost × (1 + margin%)
6. Return with breakdown for display

Business Rules

1. Unique Constraint: (roomId, market, stayDate) must be unique
2. Date Logic: stayDate is the check-in date; check-out is not stored (implied by next day's entry)
3. Booking Window: Current date must be between bookFrom and bookTo
4. Rate Base: Must match one of {SGL, DBL, TRPL, QPL}
5. Special Day Logic: If specialDay = true, use special day rates; otherwise use normal rates
6. MLOS Validation: If check-in is on special day, stay length must be ≥ sdmlos; otherwise ≥ mlos
7. Stop Sale Priority: stopSale = true overrides all other availability settings
8. Meal Supplements: JSON array must be valid; invalid JSON treated as empty array

---

4. CMealPlan - Meal Plan Entity

File: tqapp/src/main/java/com/perun/tlinq/entity/hotel/CMealPlan.java

Purpose: Defines standard meal plan options available system-wide.

Attributes

Attribute	Type	Nullable	Description	Example	Database Column
planId	Integer	No (PK)	Primary key	3	PLAN_ID
plan	String(10)	No	Meal plan code	"BB"	PLAN
name	String(50)	No	Display name	"Bed & Breakfast"	NAME
planDesc	String(200)	Yes	Description	"Includes daily breakfast"	PLAN_DESC
planOrder	Integer	Yes	Sort order for display	2	PLAN_ORDER
adultCost	Double	Yes	Default adult supplement	50.00	ADULT_COST
childCost	Double	Yes	Default child supplement	25.00	CHILD_COST

Standard Meal Plans

Plan Code	Name	Typical planOrder	Description
RO	Room Only	1	No meals included
BB	Bed & Breakfast	2	Daily breakfast
HB	Half Board	3	Breakfast + Dinner
FB	Full Board	4	Breakfast + Lunch + Dinner
AIL	All Inclusive Lite	5	All meals + local drinks
AI	All Inclusive	6	All meals + premium drinks
AIP	All Inclusive Plus	7	AI + premium services

Business Rules

1. Hierarchical Nature: Meal plans form a hierarchy from least (RO) to most (AIP) inclusive
2. Cost Progression: adultCost and childCost should increase with planOrder
3. Child Discount: childCost typically 50% of adultCost
4. Default Costs: The costs in this table are defaults; actual costs are stored in CRoomCalendarEntry.mealSupplements
5. Plan Order: Used for UI sorting and display order

No build() Method

Unlike other entities, CMealPlan has no build() method. It's a relatively static reference table, typically pre-populated and rarely modified.

---

Database Schema (Inferred)

Table: HOTEL

CREATE TABLE HOTEL (
    HOTEL_ID          INTEGER        PRIMARY KEY AUTO_INCREMENT,
    SH_DES            VARCHAR(10)    NOT NULL UNIQUE,
    NAME              VARCHAR(200)   NOT NULL,
    SHORT_NAME        VARCHAR(50),
    HOTEL_DESC        TEXT,
    PRODUCT_ID        INTEGER,
    DIRECT            BOOLEAN        DEFAULT FALSE,
    INDIRECT          BOOLEAN        DEFAULT FALSE,
    COUNTRY           VARCHAR(3),
    STARS             INTEGER,
    INFANT            INTEGER        DEFAULT 2,
    CHILD             INTEGER        DEFAULT 12,
    CHK_IN            VARCHAR(5)     DEFAULT '14:00',
    CHK_OUT           VARCHAR(5)     DEFAULT '12:00',
    FEES              DECIMAL(10,2),
    FEES_DESC         VARCHAR(500),
    TERMS             TEXT,
    AREA              VARCHAR(3),
    CITY              VARCHAR(50),
    RELEASE           INTEGER,
    RESERVATION_CONTACT VARCHAR(200),

    CONSTRAINT CHK_STARS CHECK (STARS BETWEEN 1 AND 5),
    CONSTRAINT CHK_CONTRACT CHECK (DIRECT = TRUE OR INDIRECT = TRUE),
    CONSTRAINT CHK_AGE CHECK (INFANT < CHILD)
);

CREATE INDEX IDX_HOTEL_AREA ON HOTEL(AREA);
CREATE INDEX IDX_HOTEL_COUNTRY ON HOTEL(COUNTRY);
CREATE INDEX IDX_HOTEL_NAME ON HOTEL(NAME);

Table: HOTEL_ROOM

CREATE TABLE HOTEL_ROOM (
    ROOM_ID            INTEGER        PRIMARY KEY AUTO_INCREMENT,
    HOTEL_ID           INTEGER        NOT NULL,
    NAME               VARCHAR(100)   NOT NULL,
    ROOM_DESC          TEXT,
    MAX_OCCUPANCY      INTEGER        NOT NULL,
    MAX_ADULTS         INTEGER        NOT NULL,
    EXTRA_BED          BOOLEAN        NOT NULL DEFAULT FALSE,
    EXTRA_BED_REQUIRED BOOLEAN        NOT NULL DEFAULT FALSE,
    BEDDING            VARCHAR(100),
    NOTES              VARCHAR(500),

    FOREIGN KEY (HOTEL_ID) REFERENCES HOTEL(HOTEL_ID) ON DELETE CASCADE,
    CONSTRAINT CHK_OCCUPANCY CHECK (MAX_ADULTS <= MAX_OCCUPANCY),
    CONSTRAINT CHK_EXTRA_BED CHECK (
        EXTRA_BED_REQUIRED = FALSE OR EXTRA_BED = TRUE
    )
);

CREATE INDEX IDX_ROOM_HOTEL ON HOTEL_ROOM(HOTEL_ID);
CREATE INDEX IDX_ROOM_NAME ON HOTEL_ROOM(HOTEL_ID, NAME);

Table: MEAL_PLAN

CREATE TABLE MEAL_PLAN (
    PLAN_ID      INTEGER        PRIMARY KEY AUTO_INCREMENT,
    PLAN         VARCHAR(10)    NOT NULL UNIQUE,
    NAME         VARCHAR(50)    NOT NULL,
    PLAN_DESC    VARCHAR(200),
    PLAN_ORDER   INTEGER,
    ADULT_COST   DECIMAL(10,2),
    CHILD_COST   DECIMAL(10,2)
);

CREATE INDEX IDX_MEAL_PLAN_ORDER ON MEAL_PLAN(PLAN_ORDER);

-- Seed data
INSERT INTO MEAL_PLAN (PLAN, NAME, PLAN_ORDER, ADULT_COST, CHILD_COST) VALUES
('RO',  'Room Only',            1,    0.00,   0.00),
('BB',  'Bed & Breakfast',      2,   50.00,  25.00),
('HB',  'Half Board',           3,  100.00,  50.00),
('FB',  'Full Board',           4,  150.00,  75.00),
('AIL', 'All Inclusive Lite',   5,  200.00, 100.00),
('AI',  'All Inclusive',        6,  250.00, 125.00),
('AIP', 'All Inclusive Plus',   7,  300.00, 150.00);

Table: ROOM_CALENDAR_ENTRY

CREATE TABLE ROOM_CALENDAR_ENTRY (
    CALENDAR_ENTRY_ID  INTEGER        PRIMARY KEY AUTO_INCREMENT,
    ROOM_ID            INTEGER        NOT NULL,
    MARKET             VARCHAR(3)     NOT NULL,
    STAY_DATE          DATE           NOT NULL,
    VENDOR_ID          INTEGER,
    PROMO              VARCHAR(20),
    BOOK_FROM          DATE,
    BOOK_TO            DATE,
    BASE_RATE          DECIMAL(10,2)  NOT NULL,
    SPEC_DAY_RATE      DECIMAL(10,2),
    SPECIAL_DAY        BOOLEAN        NOT NULL DEFAULT FALSE,
    SD_RATE_TYPE       VARCHAR(3),
    ADULT_RATE         DECIMAL(10,2),
    ADULT_RATE_TYPE    VARCHAR(3),
    CHILD_RATE         DECIMAL(10,2),
    CHILD_RATE_TYPE    VARCHAR(3),
    ADULT_SD_RATE      DECIMAL(10,2),
    CHILD_SD_RATE      DECIMAL(10,2),
    EXTRA_BED_RATE     DECIMAL(10,2),
    RATE_BASE          VARCHAR(4)     NOT NULL,
    MEAL_BASE          INTEGER,
    MEAL_SUPPLEMENTS   TEXT,          -- JSON array
    STOP_SALE          BOOLEAN        NOT NULL DEFAULT FALSE,
    AVAILABLE          BOOLEAN        NOT NULL DEFAULT TRUE,
    ON_REQUEST         BOOLEAN        NOT NULL DEFAULT FALSE,
    MLOS               INTEGER,
    SDMLOS             INTEGER,
    MLOS_DESC          VARCHAR(100),
    NOTES              VARCHAR(500),

    FOREIGN KEY (ROOM_ID) REFERENCES HOTEL_ROOM(ROOM_ID) ON DELETE CASCADE,
    FOREIGN KEY (MEAL_BASE) REFERENCES MEAL_PLAN(PLAN_ID),

    UNIQUE KEY UQ_ROOM_MARKET_DATE (ROOM_ID, MARKET, STAY_DATE),
    CONSTRAINT CHK_MARKET CHECK (MARKET IN ('UAE', 'GCC', 'ROW')),
    CONSTRAINT CHK_RATE_BASE CHECK (RATE_BASE IN ('SGL', 'DBL', 'TRPL', 'QPL')),
    CONSTRAINT CHK_RATE_TYPE CHECK (
        SD_RATE_TYPE IS NULL OR SD_RATE_TYPE IN ('ABS', 'ADD', 'PCT', 'PCO')
    ),
    CONSTRAINT CHK_BOOKING_WINDOW CHECK (BOOK_FROM IS NULL OR BOOK_TO IS NULL OR BOOK_FROM <= BOOK_TO)
);

CREATE INDEX IDX_CAL_ROOM ON ROOM_CALENDAR_ENTRY(ROOM_ID);
CREATE INDEX IDX_CAL_DATE ON ROOM_CALENDAR_ENTRY(STAY_DATE);
CREATE INDEX IDX_CAL_MARKET ON ROOM_CALENDAR_ENTRY(MARKET);
CREATE INDEX IDX_CAL_SEARCH ON ROOM_CALENDAR_ENTRY(ROOM_ID, MARKET, STAY_DATE);
CREATE INDEX IDX_CAL_AVAILABLE ON ROOM_CALENDAR_ENTRY(AVAILABLE, STOP_SALE);

Database Indexes Strategy

Primary Indexes (Auto-created)

• Primary keys: HOTEL_ID, ROOM_ID, PLAN_ID, CALENDAR_ENTRY_ID
• Unique constraints: HOTEL.SH_DES, MEAL_PLAN.PLAN, ROOM_CALENDAR_ENTRY(ROOM_ID, MARKET, STAY_DATE)

Performance Indexes

Hotel Searches: - IDX_HOTEL_AREA: Filter by area/emirate - IDX_HOTEL_NAME: Search by name (prefix matching) - IDX_HOTEL_COUNTRY: Filter by country

Room Lookups: - IDX_ROOM_HOTEL: Get all rooms for a hotel - IDX_ROOM_NAME: Search room by hotel and name

Calendar Searches (Critical for Performance): - IDX_CAL_SEARCH: Composite index (roomId, market, stayDate) - supports unique constraint and fast lookups - IDX_CAL_DATE: Range queries on stay dates - IDX_CAL_AVAILABLE: Filter by availability status

Meal Plans: - IDX_MEAL_PLAN_ORDER: Ordered display of meal plans

---

Data Integrity and Constraints

Foreign Key Relationships

1. HOTEL_ROOM.HOTEL_ID → HOTEL.HOTEL_ID
2. CASCADE on delete: If hotel deleted, all rooms deleted

3. RESTRICT on update: Hotel ID cannot be changed

4. ROOM_CALENDAR_ENTRY.ROOM_ID → HOTEL_ROOM.ROOM_ID

5. CASCADE on delete: If room deleted, all calendar entries deleted

6. RESTRICT on update: Room ID cannot be changed

7. ROOM_CALENDAR_ENTRY.MEAL_BASE → MEAL_PLAN.PLAN_ID

8. SET NULL on delete: If meal plan deleted, calendar entry retains pricing but meal plan reference is nulled
9. RESTRICT on update: Meal plan ID cannot be changed

Check Constraints

1. Hotel Constraints:
2. Star rating between 1-5
3. At least one contract type (direct or indirect) must be true

4. Infant age < Child age

5. Room Constraints:

6. Max adults ≤ Max occupancy

7. If extra bed required, extra bed must be available

8. Calendar Constraints:

9. Market must be UAE, GCC, or ROW
10. Rate base must be SGL, DBL, TRPL, or QPL
11. Rate calculation types must be ABS, ADD, PCT, or PCO
12. Booking window: book_from ≤ book_to

Unique Constraints

1. HOTEL.SH_DES: Hotel code must be unique across all hotels
2. MEAL_PLAN.PLAN: Meal plan code must be unique
3. (ROOM_ID, MARKET, STAY_DATE): Only one rate per room/market/date combination

---

Data Volume Estimates

Typical Deployment

Table	Estimated Rows	Growth Rate	Notes
HOTEL	100-500	Slow (10-20/year)	Contract hotels only
HOTEL_ROOM	500-5,000	Slow	Avg 10 room types per hotel
MEAL_PLAN	7-15	Very slow	Reference data
ROOM_CALENDAR_ENTRY	500,000-5,000,000	Fast	Grows daily

Calendar Entry Growth

Per room per market:
365 days/year × 2 years advance = 730 entries
1000 rooms × 3 markets × 730 days = 2,190,000 entries

Growth Pattern: - Continuous: New dates added as calendar extends forward - Bulk operations: Common to create 90-365 days at once - Updates: Frequent rate changes, especially near booking dates - Purge strategy: Archive entries older than 1 year

---

JSON Data Structures

mealSupplements Field

Storage: TEXT column in ROOM_CALENDAR_ENTRY table Format: JSON array of meal plan supplements

Structure:

[
  {
    "mealPlan": "BB",
    "adlRate": [60.0, 70.0],
    "chldRate": [30.0, 35.0]
  },
  {
    "mealPlan": "HB",
    "adlRate": [100.0, 120.0],
    "chldRate": [50.0, 60.0]
  }
]

Field Descriptions: - mealPlan (String): Meal plan code (matches MEAL_PLAN.PLAN) - adlRate (Array[2]): Adult supplement [normal_day, special_day] - chldRate (Array[2]): Child supplement [normal_day, special_day]

Parsing Logic (JavaScript - hotelmgmt.js):

let mealSupplements = [];
if (calEntry.mealSupplements) {
    try {
        mealSupplements = JSON.parse(calEntry.mealSupplements);
    } catch(e) {
        console.error("Invalid meal supplements JSON", e);
        mealSupplements = [];
    }
}

// Find supplement for requested meal plan
let supplement = mealSupplements.find(s => s.mealPlan === requestedMealPlan);
if (supplement) {
    let isSpecialDay = calEntry.specialDay;
    let adultSupplement = isSpecialDay ? supplement.adlRate[1] : supplement.adlRate[0];
    let childSupplement = isSpecialDay ? supplement.chldRate[1] : supplement.chldRate[0];

    totalMealCost = (adults × adultSupplement) + (children × childSupplement);
}

---

Entity Lifecycle

Hotel Lifecycle

1. CREATE
   - User fills hotel form
   - POST /hotel/saveHotel with hotelId = null
   - Database assigns new hotelId
   - Hotel added to search results

2. READ
   - POST /hotel/listHotels (search)
   - POST /hotel/hotelLookup (autocomplete)
   - POST /hotel/getHotel (details)

3. UPDATE
   - User edits hotel form
   - POST /hotel/saveHotel with existing hotelId
   - Database updates record

4. DELETE
   - No explicit delete endpoint
   - Manual database operation (cascades to rooms and calendar)

Room Lifecycle

1. CREATE
   - User selects hotel, clicks "Add Room"
   - Fills room form
   - POST /hotel/saveRoom with roomId = null
   - Database assigns new roomId
   - Room added to hotel's room list

2. READ
   - POST /hotel/listHotelRooms with hotelId
   - Returns all rooms for hotel

3. UPDATE
   - User edits room form
   - POST /hotel/saveRoom with existing roomId
   - Database updates record

4. DELETE
   - No explicit delete endpoint
   - Manual database operation (cascades to calendar)

Calendar Entry Lifecycle

1. CREATE (Bulk)
   - User selects room, market, clicks "Set Pricing"
   - Fills calendar creation form with date range
   - POST /hotel/createRoomCalendar
   - System creates one entry per day in range
   - Special days automatically flagged (weekends)
   - Returns count of entries created

2. READ
   - POST /hotel/listRoomCalendar with roomId, market, date range
   - Returns array of calendar entries for editing

3. UPDATE (Bulk)
   - User edits date range in calendar view
   - POST /hotel/updateRoomCalendar with entry updates
   - System updates all entries in selection
   - Can update rates, availability, MLOS, etc.

4. COPY
   - User selects source range, clicks "Copy Rates"
   - POST /hotel/copyRoomCalendar with source and target params
   - System copies entries to new dates/markets
   - Preserves all pricing logic

5. DELETE
   - No explicit delete endpoint
   - Manual database operation

Meal Plan Lifecycle

Meal plans are relatively static reference data:

1. CREATE
   - Typically pre-populated during system setup
   - Or manually added via SQL

2. READ
   - POST /hotel/listMealPlans
   - Returns all available meal plans

3. UPDATE
   - POST /hotel/updateMealPlans (bulk update)
   - Updates costs for multiple plans

4. DELETE
   - Not supported (reference data)

---

Data Validation Rules

Field-Level Validation

CHotel

• shDes: Required, 1-10 chars, alphanumeric, unique
• name: Required, 1-200 chars
• stars: Optional, integer 1-5
• infant: Optional, integer 0-17, default 2
• child: Optional, integer 0-17, default 12, must be > infant
• chkIn, chkOut: Optional, format "HH:MM" (24-hour)
• fees: Optional, non-negative decimal
• release: Optional, non-negative integer (days)

CHotelRoom

• hotelId: Required, must exist in HOTEL table
• name: Required, 1-100 chars
• maxOccupancy: Required, integer ≥ 1
• maxAdults: Required, integer ≥ 1, ≤ maxOccupancy
• extraBed: Required, boolean
• extraBedRequired: Required, boolean; if true, extraBed must be true

CRoomCalendarEntry

• roomId: Required, must exist in HOTEL_ROOM table
• market: Required, must be "UAE", "GCC", or "ROW"
• stayDate: Required, date format dd-MM-yyyy (web) or yyyy-MM-dd (db)
• baseRate: Required, positive decimal
• rateBase: Required, must be "SGL", "DBL", "TRPL", or "QPL"
• sdRateType: Optional, must be "ABS", "ADD", "PCT", or "PCO" if provided
• bookFrom, bookTo: Optional, bookFrom ≤ bookTo
• mlos, sdmlos: Optional, integer ≥ 1
• mealSupplements: Optional, valid JSON array if provided

CMealPlan

• plan: Required, 1-10 chars, unique
• name: Required, 1-50 chars
• adultCost, childCost: Optional, non-negative decimal

Business Logic Validation

1. Date Range Validation:
2. Stay period: from < to

3. Booking window: bookFrom < bookTo < stayDate

4. Occupancy Validation:

5. Booking adults ≤ room.maxAdults (or + extra bed)

6. Booking (adults + children) ≤ room.maxOccupancy

7. Rate Calculation Validation:

8. If sdRateType provided, specDayRate must be provided

9. If adultRateType provided, adultRate must be provided

10. MLOS Validation:

11. Booking length ≥ mlos (normal days)
12. Booking length ≥ sdmlos (if special day included)

---

Summary

The Hotel Management data model consists of 4 entities forming a hierarchical structure:

1. CHotel: Hotel master data (22 attributes)
2. CHotelRoom: Room types per hotel (10 attributes)
3. CRoomCalendarEntry: Daily pricing per room/market (31 attributes)
4. CMealPlan: Meal plan reference data (7 attributes)

Key Features: - Flexible pricing: Supports multiple markets, special days, occupancy-based rates - Rate calculation types: ABS, ADD, PCT, PCO for dynamic pricing - Meal plan supplements: JSON-based flexible meal pricing - MLOS restrictions: Different minimums for normal and special days - Availability control: Stop sale, on-request, booking windows - Bulk operations: Calendar creation, updates, and copying

Database Design: - Normalized structure: Clear foreign key relationships - Performance indexes: Optimized for search and calendar queries - Data integrity: Check constraints and unique constraints - JSON flexibility: mealSupplements field allows structured data without additional tables

This data model supports complex hotel contracting scenarios common in travel agencies while maintaining performance for real-time availability searches.