Hotel Reservation system Database schema

Question

I am about to develop a online hotel reservation system...using php and mysql... I have some doubts about my current database schema and the business logic to get the hotels in which rooms are free between two particular dates...

Does anyone know of some kind of tutorial where i can get some idea about the hotel reservation schema and the business logics that should be used in the system...?

Thanks for your suggestions....

Edit : I've figured out most of the logic... The points i am not clear about are the following...

1. If a user selects more than one room in a particular hotel between two particular dates how can i represent in the following reservation table...?

   Table : Reservation

    Field 1 : reservation_id
    Field 2 : room_id
    Field 3 : no. of Rooms
    Field 4 : check-in date
    Field 5 : check-out date
    Field 6 : Customer id
   

2. How can i check what rooms are available between two dates based on the reservation table and the following rooms table...?

   Table : Room

        Field 1 : hotel_id
        Field 2 : room_id
        Field 3 : total_num_rooms
   

Note : The db contains more than one hotel... So it's like a user can select a city and look for rooms available in hotels in that area between two particular dates...

Also say if there are 10 numbers of room of a particular type in a hotel, i need to show only the number of rooms that are free in that particular time period.....

Solution 1

As a general thought, apply divide and conquer. Always.

For example, why do you think a specific customer should be able to have 'number of rooms' for a certain time span associated? What if, for example, I'm on a business trip and have my family follow me a few days later. Now, for the given time span the number of rooms is no longer a constant.

That doesn't really matter? True, you could just add another entry for the same customer. But then again, you could have done that in the first place and simplify your logic saying that a customer can only have one room at a time in a single row, but there can be rows that create overlaps in time spans for a given customer.

Also, make sure you separate Reservation and a ReservationRequest. The latter is comprised of a set of Reservations I think - because I want that room for me and my family and both criteria must be matched.

Just a few ideas. Note that this is the ivory tower approach and it can lead to massively overblown solutions. In the RealWorld (TM), stick to Marcs suggestions: Analyze the actual customers need. If handling 1% of the requests increases development time by 200%, he's not gonna like (or need) it, and vice versa.

Solution 2

There isn't a perfect way of representing something like an hotel reservation system.

Try talking to your client or people working in hotels to understand what they are doing now and base your system on this.

I'd guess:

A Room has a RoomType
A Customer can Book 1..n Room(s)
A RoomType has a name and a price

... and so on.

If you just use a tutorial, you might end up creating a system that doesn't fit the requirements of possible users. So talk to these future end users, figure out the business logic and start coding :)

Comments

[mnemosyn]

So this is homework? Otherwise, why would you want to use that data structure?

[SpikETidE]

This isn't homework... i'm just not sure about the business logic i worked out.. so i need some better suggestions...

[xorinzor]

Just my bit of thought, there is no reason why a customer wouldn't be able to have multiple rooms, if you setup your tables correctly you would have separate tables for users, rooms, reserverations, etc. and connect them by ID. this would allow for a single user to have multiple rooms at different reservations.