SQL Online booking system

Amaru

I want to create an online booking system, like you'd use for a hotel or rentals agency, using PHP and SQL(probably MySQL). A normal booking system, where people book only for a single day, is easy, but my problem lies with date ranges. I don't know how to manipulate date ranges(like the start date and end date for the activity) in SQL, or even if its possible to do so. I want it so that if somebody tries to book something that is unavailble during the date range, they will receive a message saying so. Does anybody know if this can be done in SQL, or if not, what is the alternative?

davej

mysql has some quite powerful date and time functions, which would easily allow you to do what is required. Check out the online documentation:

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

http://dev.mysql.com/doc/mysql/en/Date_calculations.html

davej

Amaru

So what do you suggest? Create a table for each individual item that can be occupied, and then put in an "occupied" start and end date for each time somebody wants to book it?

leeroybrown

An example. If you had to check the number of available rooms that fit a booking enquire you would just need to add the two conditions (not valid SQL):

NOT "Existing Booking End Date" < "New Booking Start Date" <= "Existing Booking Start Date"
NOT "Existing Booking Start Date" <= "New Booking End Date" < "Existing Booking End Date"

That form of logic is applicable to any situation. Just massage the dates with the date/time functions to suit your situation.

Edit:

So what do you suggest? Create a table for each individual item that can be occupied, and then put in an "occupied" start and end date for each time somebody wants to book it?

I would suggest that you create a rooms table with one row for each room/item that can be occupied. Each room will be uniquely identified by it's room number.

Then create a bookings table. Each individual row will represent one booking with it's details, room number, start date and end date. This way when you want to check availability you will just need to apply the above logic based on proposed booking dates. The information in your bookings table depends on whether you wish to create a customers table as a seperated entity and link each to their bookings (probably a tidier and more expandable approach).

bonkey

You can take the above idea further by categorising rooms into room-types, with a RoomType table, with each room being allocated a roomTypeID. Tie Room-types to the pricing, and then you can search for dates across all rooms of a specific type, rather than for specific rooms.

Using startDate and endDate as described above is definitely the best way to go. A bit of ingenuity and you can even add queries to allow "+-n day" searches for people who have a degree of flexibility (swiss.com have this on their airline booking and for planning trips home its a great help in finding cheap fares...which makes me go back there).

jc