CODEWITHSUNDEEP
phpdatabasephpmyadmin
Jonathan
Jonathan

Reputation: 113

Database design/structures for multi-hotel booking system

I want to develop a site similar to OTA websites like (booking.com & agoda.com). But i'm having trouble when making my table. also hoteliers can close specific rooms on specific dates so i must have like a close open field somewhere. I'm not sure if I'm currently doing it correctly therefore I need some extra brains to assist me to design it better.

I've tried searching in Google for database designs but they do not fit my requirement, most of them are for only a single hotel structure and has only a fixed price whereas mine the prices are based on the dates. Prices might be higher on PH and Weekends and can be changed by the hoteliers.

What i have in mind for my tables:

hotel {
    hotelID (INT) PK
    hotelName (VARCHAR)
}

rooms {
    roomID (INT) PK
    roomType (VARCHAR)
}

I plan to use INT on availability (0 = close & 1 = open)

rates {
    roomID (INT) 
    date (datetime)
    price (double)
    available (INT)
}

bookings {
    bookingID (INT) PK
    hotelID (INT)
    roomID (INT)
    checkInDate (datetime)
    checkOutDate (datetime)
    price (double)
}

Upvotes: 0

Views: 747

Answers (1)

Shudhansh Shekhar
Shudhansh Shekhar

Reputation: 720

May be this design model will help you to understand the basic logic for a hotel management system.

hotels {
    id INT 11
    code VARCHAR 50
    name VARCHAR 70
    street_address TEXT 
    city_id INT 11
    state_id INT 11
    country_id INT 11
    zip_code VARCHAR 30
    added_on DATETIME
    added_by INT 11 ( ADMIN ID )
    updated_on DATETIME
    updated_by INT 11 ( ADMIN ID )
    flag TINYINT 2 DEFAULT 0
}

rooms {
    id INT 11
    hotel_id INT 11
    room_number INT 11
    floor_number INT/VARCHAR ..
    room_type INT 5 ( FK: room_types )
    added_on DATETIME
    added_by INT 11 ( ADMIN ID )
    flag TINYINT 2 (0/1) DEFAULT 0 AVAILABLE / NOT AVAILABLE
}

room_types {
    id INT 11
    hotel_id INT 11
    ......
}

room_facilities {
    id INT 11
    hotel_id INT 11
    room_id INT 11
    bed_type ( 'single', 'double', etc. )
    ac_available ...
    ..... etc.
}

room_rates {
    id INT 11
    hotel_id INT 11
    room_id INT 11
    price INT 11
    .. other price ..
    price_on DATETIME 11 ( DATE )
    added_on DATETIME
    added_by INT 11 ( ADMIN ID )
    flag TINYINT 2
}

bookings {
    id INT 11
    hotel_id INT 11
    room_id INT 11
    check_in DATETIME
    check_out DATETIME
    booking_person_id INT 11
    flag
}

booking_customer {
    id INT 11
    booking_id INT 11
    first_name ..
    last_name ..
    contact_number ..
    id_number ..
    added_on
    flag
}

booking_payment {
    id
    booking_id
    customer_id
    total_paid
    -- other price
    payment_type ( credit card/cash )
    status ( paid/ not paid ) 
}

booking_person {
    id
    hotel_id
    first_name
    last_name
    contact_number
    email
    address
    profile_picture
    added_on
    updated_on
    flag
}

You can develop your database based on these criterias/design. Always try to segregate multiple type of data into seperate tables.

Upvotes: 1

Related Questions