Database Schema¶

Overview¶

The Gustaffo Reservations Application uses PostgreSQL as its primary database management system. The database schema is designed to support the reservation, payment, and hotel management workflows.

Entity Relationship Diagram¶

erDiagram
    PROPERTY ||--o{ UNIT : contains
    PROPERTY ||--o{ RATE_PLAN : offers
    PROPERTY ||--o{ RESERVATION : has
    UNIT ||--o{ RESERVATION_OFFER : assigned_to
    RATE_PLAN ||--o{ RESERVATION_OFFER : applies_to
    GUEST ||--o{ RESERVATION : makes
    RESERVATION ||--o{ RESERVATION_OFFER : contains
    RESERVATION ||--o{ PAYMENT : has
    PAYMENT }|--|| PAYMENT_METHOD : uses
    PAYMENT }|--|| PAYMENT_STATUS : has
    PAYMENT ||--o{ PAYMENT_TRANSACTION : generates

    PROPERTY {
        string property_id PK
        string name
        string address
        string city
        string country
        string postal_code
        string phone
        string email
        string website
        string currency
        string timezone
    }

    UNIT {
        string unit_id PK
        string property_id FK
        string unit_type
        string name
        int max_occupancy
        int base_occupancy
        string description
        boolean active
    }

    RATE_PLAN {
        string rate_plan_id PK
        string property_id FK
        string name
        string description
        string policy_code
        boolean active
        enum policy_type
        decimal base_price
        string currency
    }

    GUEST {
        string guest_id PK
        string first_name
        string last_name
        string email
        string phone
        string address
        string city
        string country
        string postal_code
        string language
    }

    RESERVATION {
        string reservation_id PK
        string property_id FK
        string guest_id FK
        date check_in
        date check_out
        datetime creation_date
        enum status
        decimal total_amount
        string currency
        int adults
        int children
        string special_requests
        string booking_channel
    }

    RESERVATION_OFFER {
        string reservation_offer_id PK
        string reservation_id FK
        string unit_id FK
        string rate_plan_id FK
        decimal price
        string currency
        date date_from
        date date_to
        int adults
        int children
        boolean breakfast_included
    }

    PAYMENT {
        string payment_id PK
        string reservation_id FK
        decimal amount
        string currency
        datetime payment_date
        string payment_method_id FK
        string payment_status_id FK
        string reference_number
        string description
    }

    PAYMENT_METHOD {
        string payment_method_id PK
        string name
        string description
        boolean active
    }

    PAYMENT_STATUS {
        string payment_status_id PK
        string name
        string description
    }

    PAYMENT_TRANSACTION {
        string transaction_id PK
        string payment_id FK
        string type
        decimal amount
        string currency
        datetime transaction_date
        string status
        string provider_reference
        string provider_response
    }

Tables Description¶

PROPERTY¶

Stores information about hotels and properties in the system.

Column	Type	Description
property_id	string	Unique identifier for the property
name	string	Property name
address	string	Property street address
city	string	Property city
country	string	Property country
postal_code	string	Property postal code
phone	string	Property phone number
email	string	Property contact email
website	string	Property website
currency	string	Default currency used by the property
timezone	string	Property timezone

UNIT¶

Represents rooms or accommodation units within a property.

Column	Type	Description
unit_id	string	Unique identifier for the unit
property_id	string	Reference to the property this unit belongs to
unit_type	string	Type of unit (e.g., Standard Room, Suite)
name	string	Unit name
max_occupancy	int	Maximum number of guests allowed
base_occupancy	int	Standard occupancy for pricing
description	string	Unit description
active	boolean	Whether the unit is active and bookable

RATE_PLAN¶

Defines different rate plans offered by properties.

Column	Type	Description
rate_plan_id	string	Unique identifier for the rate plan
property_id	string	Reference to the property offering this rate plan
name	string	Rate plan name
description	string	Rate plan description
policy_code	string	Code representing the cancellation/deposit policy
active	boolean	Whether the rate plan is active
policy_type	enum	Type of policy (DEPOSIT, GUARANTEE, NONE, etc.)
base_price	decimal	Base price for the rate plan
currency	string	Currency for the rate plan

GUEST¶

Stores information about guests making reservations.

Column	Type	Description
guest_id	string	Unique identifier for the guest
first_name	string	Guest's first name
last_name	string	Guest's last name
email	string	Guest's email address
phone	string	Guest's phone number
address	string	Guest's address
city	string	Guest's city
country	string	Guest's country
postal_code	string	Guest's postal code
language	string	Guest's preferred language

RESERVATION¶

Represents a booking made by a guest.

Column	Type	Description
reservation_id	string	Unique identifier for the reservation
property_id	string	Reference to the property being booked
guest_id	string	Reference to the guest making the reservation
check_in	date	Check-in date
check_out	date	Check-out date
creation_date	datetime	When the reservation was created
status	enum	Reservation status (CONFIRMED, CANCELLED, etc.)
total_amount	decimal	Total amount for the reservation
currency	string	Currency for the reservation
adults	int	Number of adults
children	int	Number of children
special_requests	string	Special requests from the guest
booking_channel	string	Channel through which the booking was made

RESERVATION_OFFER¶

Links reservations to specific units and rate plans.

Column	Type	Description
reservation_offer_id	string	Unique identifier for the reservation offer
reservation_id	string	Reference to the reservation
unit_id	string	Reference to the unit being booked
rate_plan_id	string	Reference to the rate plan applied
price	decimal	Price for this specific offer
currency	string	Currency for the price
date_from	date	Start date for this offer (for split stays)
date_to	date	End date for this offer (for split stays)
adults	int	Number of adults for this offer
children	int	Number of children for this offer
breakfast_included	boolean	Whether breakfast is included

PAYMENT¶

Records payments associated with reservations.

Column	Type	Description
payment_id	string	Unique identifier for the payment
reservation_id	string	Reference to the associated reservation
amount	decimal	Payment amount
currency	string	Payment currency
payment_date	datetime	When the payment was made/initiated
payment_method_id	string	Reference to the payment method used
payment_status_id	string	Reference to the payment status
reference_number	string	External reference number
description	string	Payment description

PAYMENT_METHOD¶

Defines available payment methods.

Column	Type	Description
payment_method_id	string	Unique identifier for the payment method
name	string	Payment method name
description	string	Payment method description
active	boolean	Whether the payment method is active

PAYMENT_STATUS¶

Defines possible payment statuses.

Column	Type	Description
payment_status_id	string	Unique identifier for the payment status
name	string	Status name
description	string	Status description

PAYMENT_TRANSACTION¶

Stores individual payment transactions.

Column	Type	Description
transaction_id	string	Unique identifier for the transaction
payment_id	string	Reference to the associated payment
type	string	Transaction type (AUTHORIZATION, CAPTURE, REFUND, etc.)
amount	decimal	Transaction amount
currency	string	Transaction currency
transaction_date	datetime	When the transaction occurred
status	string	Transaction status
provider_reference	string	Reference from the payment provider
provider_response	string	Response from the payment provider

Indexing Strategy¶

The database employs the following indexing strategy to optimize query performance:

Primary keys are indexed by default
Foreign keys are indexed to speed up join operations
Additional indexes on frequently queried columns such as:
- check_in and check_out dates in RESERVATION
- status in RESERVATION
- payment_date in PAYMENT
- transaction_date in PAYMENT_TRANSACTION

Data Migration and Versioning¶

The database schema is versioned using Flyway migration scripts. Each schema change is captured in a versioned migration script, allowing for controlled evolution of the database schema over time.