So, I’m desinging a web application that is going to work for a Hotel and it should eventually become an easy way to install for every hotel website with some extra code. The application is working with php and mysql. So, as you probably guessed, I’m pretty confused with the database structure. Though, I’ve made some research and I’m not just asking for a full-fledged system for bookings.
So, owner of the hotel has to have many types of rooms. So probably a table for rooms is a “must” I suppose. Here we go:
---------- Rooms Table (example) ID NAME ADULTS ROOM_COUNT PRICE/NIGHT 1 DoubleRoom 2 5 45$ 2 Family 3 2 75$ 3 Single 1 3 35$
This is pretty straightforward. Rooms can be added through my application and I’ll also attach a proper description as well as a variety of images for each room. But let’s get back to the point. People should make bookings so…
---------- Bookings Table (example) ID ROOM_ID BOOKING_USER CHECKIN (timestamp) CHECKOUT (timestamp) 1 2 A full name 208420842042 308230482042824 2 3 A full name 208423242525 529752972595272 ...
So, this is –hopefully– the database structure. Now, I’m thinking of a way to check for availability. I know, it might sound stupid but I can’t figure out a smart and easy way. In the form of the website, there should be the following fields: check-in date
and check-out-date
which will be then transformed to timestamps and.. well here’s the time I stuck!
How can I check for availabily of rooms when I only have the above tables. Is there any way or I need to change my database structure? Even if I manage to check for availability, how comes the total number of rooms (ROOM_COUNT
) in count? I mean, how can I check availability for multiple types of rooms? I thought something like:
SELECT count(ROOM_ID) FROM bookings WHERE bookings.checkin < DATE_GIVEN_AS_CHECKIN_INPUT and bookings.checkout > DATE_GIVEN_AS_CHECKOUT_INPUT
then depending on the output of this query I would know if there’s a room available or not. But what about room count? Is there any better ways?
I’d be really glad if someone here could enlight me on this. Best regards and thanks in advance, even for reading this!
Blane
P.S. Oh, it’s not a homework! 😛 I don’t go to school anyway 😀
Advertisement
Answer
the following query will find all rooms which have no bookins, or bookings which are before or after the checkin/checkout input.
SELECT r.* FROM rooms r LEFT JOIN bookins b ON b.room_id = r.id WHERE b.id IS NULL OR (b.checkout >= DATE_GIVEN_AS_CHECKIN_INPUT AND b.checkin >= DATE_GIVEN_AS_CHECKOUT_INPUT) OR (b.checkout <= DATE_GIVEN_AS_CHECKIN_INPUT AND b.checkout <= DATE_GIVEN_AS_CHECKOUT_INPUT)
to get the room count just replace SELECT r.*
with SELECT COUNT(DISTINCT r.id)