DEV Community

Chandra
Chandra

Posted on • Edited on

1

Hotel reservation Schema design (PostgreSQL)

Audit

created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMPTZ NULL
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode
  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
);
Enter fullscreen mode Exit fullscreen mode
  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)
);
Enter fullscreen mode Exit fullscreen mode
  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)
);
Enter fullscreen mode Exit fullscreen mode
  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)
);
Enter fullscreen mode Exit fullscreen mode
  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)
);
Enter fullscreen mode Exit fullscreen mode
  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');
Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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 :)

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Heroku

This site is powered by Heroku

Heroku was created by developers, for developers. Get started today and find out why Heroku has been the platform of choice for brands like DEV for over a decade.

Sign Up

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay