Hotel reservation Schema design (PostgreSQL)

Audit

created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMPTZ NULL

For Auditing purpose we need:

1. created_at: The timestamp when the record is created.
2. updated_at: The timestamp when the record is last updated.
3. deleted_at: A soft delete timestamp for the record, useful for data recovery and audit trails.
4. TIMESTAMPTZ: use timestamptz for scalability and consistency across time zones.

User account

CREATE TABLE user_account (
    user_id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    oauth_provider VARCHAR(50),
    oauth_uid VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ NULL
);

1. user_id: A randomly generated UUID assigned when the user is authenticated (generated only at first-time login).
2. email: The user's email address.
3. full_name: The user's full name.
4. oauth_provider: The name of the OAuth provider (e.g., Google, Facebook).
5. oauth_uid: The unique ID provided by the OAuth provider.

I am using OAuth for authentication and authorization in my application to avoid the complexities and responsibilities of managing my own authentication system.

Hotel Room

CREATE TABLE room (
    room_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_number VARCHAR(20) NOT NULL,
    room_type VARCHAR(20) NOT NULL,
    description TEXT,
    availability_status room_availability_status DEFAULT 'AVAILABLE',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ NULL
);

1. room_id: I am using INT as the unique identifier because I assume the number of hotel rooms will not exceed 2,147,483,647 (the maximum value for an INT in PostgreSQL).
2. room_number: The room number, which I expect to be no more than 10 characters (e.g., SB001).
3. room_type: The type of room, such as single or double.
4. description: A description of the room.
5. availability_status: An enum indicating the room's availability status (e.g., 'AVAILABLE', 'RESERVED', 'BOOKED', 'BLOCKED').

Room pricing

CREATE TABLE room_pricing (
    pricing_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id INT,
    price_per_night DECIMAL(10, 2) NOT NULL,
    available_date TIMESTAMPTZ NOT NULL,
    pricing_type pricing_type,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ NULL,
    FOREIGN KEY (room_id) REFERENCES room(room_id)
);

1. pricing_id: Using INT as the unique identifier, considering that the 2. number of pricing records related to rooms will not exceed 2,147,483,647.
2. room_id: Foreign key referencing the room table.
3. price_per_night: The price of the room per night.
4. pricing_type: An enum indicating the pricing type (e.g., 'STANDARD', 'DISCOUNTED', 'PREMIUM').

User order

CREATE TABLE user_order (
    order_id UUID PRIMARY KEY
    user_id UUID,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ NULL,
    FOREIGN KEY (user_id) REFERENCES user_account(user_id)
);

1. order_id: A UUID used as a strong unique identifier.
2. user_id: Foreign key referencing the user_account table for the customer who made the order.

Room booking

CREATE TABLE room_booking (
    booking_id UUID PRIMARY KEY,
    order_id UUID,
    room_id INT,
    check_in_date TIMESTAMPTZ NOT NULL,
    check_out_date TIMESTAMPTZ NOT NULL,
    booking_status booking_status,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ NULL, -- Soft delete column with timezone
    FOREIGN KEY (order_id) REFERENCES user_order(order_id),
    FOREIGN KEY (room_id) REFERENCES room(room_id)
);

1. booking_id: A UUID used as a strong unique identifier.
2. order_id: Foreign key referencing the user_order table.
3. room_id: Foreign key referencing the room table.
4. check_in_date: The date and time when the user checks in.
5. check_out_date: The date and time when the user checks out.
6. booking_status: An enum indicating the booking status (e.g., 'CONFIRMED', 'CANCELLED', 'CHECKED_IN', 'CHECKED_OUT').

Payment

CREATE TABLE payment (
    payment_id UUID PRIMARY KEY,
    order_id UUID NOT NULL,
    payment_reference_id VARCHAR(255) UNIQUE,
    payment_status payment_status,
    payment_date TIMESTAMPTZ,
    payment_method payment_method,
    payment_gateway VARCHAR(100),
    amount_paid DECIMAL(10, 2) NOT NULL,
    currency CHAR(3) DEFAULT 'USD',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ NULL,
    FOREIGN KEY (order_id) REFERENCES user_order(order_id)
);

1. payment_id: A UUID used as a strong unique identifier. I am not using payment_reference_id as the primary key because it is dependent on external services.
2. order_id: Foreign key referencing the user_order table.
3. payment_reference_id: Reference ID from the payment gateway.
4. payment_status: The status of the payment (e.g., 'PENDING', 'COMPLETED', 'FAILED').
5. payment_date: The date and time of the payment transaction.
6. payment_method: The method of payment (e.g., 'CREDIT_CARD', 'DEBIT_CARD').
7. payment_gateway: The payment gateway provider.
8. amount_paid: The total amount paid in the transaction. currency: ISO 4217 currency code (e.g., 'USD').

Seeding data

-- Enum for Room Availability Status
CREATE TYPE room_availability_status AS ENUM ('AVAILABLE', 'RESERVED', 'BOOKED', 'BLOCKED');

-- Enum for Pricing Type
CREATE TYPE pricing_type AS ENUM ('STANDARD', 'DISCOUNTED', 'PREMIUM');

-- Enum for Booking Status
CREATE TYPE booking_status AS ENUM ('CONFIRMED', 'CANCELLED', 'CHECKED_IN', 'CHECKED_OUT');

-- Enum for Payment Status
CREATE TYPE payment_status AS ENUM ('PENDING', 'COMPLETED', 'FAILED');

-- Enum for Payment Method
CREATE TYPE payment_method AS ENUM ('CREDIT_CARD', 'DEBIT_CARD');

Indexing

-- Indexes for user_account table
CREATE INDEX idx_user_account_email ON user_account(email);

-- Indexes for room table
CREATE INDEX idx_room_room_number ON room(room_number);
CREATE INDEX idx_room_room_type ON room(room_type);
CREATE INDEX idx_room_availability_status ON room(availability_status);

-- Indexes for room_pricing table
CREATE INDEX idx_room_pricing_room_id ON room_pricing(room_id);
CREATE INDEX idx_room_pricing_available_date ON room_pricing(available_date);

-- Indexes for user_order table
CREATE INDEX idx_user_order_user_id ON user_order(user_id);

-- Indexes for room_booking table
CREATE INDEX idx_room_booking_order_id ON room_booking(order_id);
CREATE INDEX idx_room_booking_room_id ON room_booking(room_id);
CREATE INDEX idx_room_booking_check_in_date ON room_booking(check_in_date);
CREATE INDEX idx_room_booking_check_out_date ON room_booking(check_out_date);
CREATE INDEX idx_room_booking_booking_status ON room_booking(booking_status);

-- Indexes for payment table
CREATE INDEX idx_payment_order_id ON payment(order_id);
CREATE INDEX idx_payment_payment_status ON payment(payment_status);

Walkthrough

1. When a user logs in, a record is created in the user_account table. Admins input data for room and room_pricing, where each room can have multiple prices depending on the date (e.g., normal days or special events like Christmas).
2. When a user books a room, records are created in the user_order, room_booking, and payment tables. If the booking is pending, the booking_status will be NULL, room_availability_status will be RESERVED, and payment_status will be PENDING.
3. If the payment is successful, the payment_status will be COMPLETED, booking_status will be CONFIRMED, and room_availability_status will be BOOKED.

 
any feedback will be helpful :)