id, id_roomtype, date, num_booked, booked_out
Also a ROOMTYPE table that has columns: id, id_property, name, max_sunday, max_monday, max_tuesday, etc.
Check the "booked_out" column for each date
e.g.
SELECT date
FROM bookingdate
WHERE date BETWEEN <checkin> AND <checkout>
AND booked_out = 1If it returns any rows then tell user that "date" is booked out.
If no rows returned then keep going:
For each reservation date increment the "num_booked" (or create a new row if one doesn't exist)
Check "booked_out" in case someone has made a booking since inital check.
Check updated "num_booked" <= corresponding "max" column in ROOMTYPE and set "booked_out" flag if it's at capacity.
Remember you must also do the reverse if a booking is cancelled as it may no longer be "booked_out" anymore.
Use transactions when doing this sort of stuff or else you'll make a *big mess*.
This will work with hotels too, just set the "max" columns to be all the same value for a given room type :)
If you were going to use triggers and stored procs then separating the "max" columns into a separate entity would be probably be needed (and would allow for different room capacities at different times of the year).
You probably would want to use change <checkout> to (<checkout> minus a day) in the SQL because they are checking out on that day, i.e. the room isn't required
-- timmah
Claude Raiola spoke the following wise words on 8/07/2003 11:02 PM EST:
if anyone can assist and shed some light on a functional structure to meet the needs of both a serviced apartments environment (where the total available room nights available from one night to the next may alter) and hotel structure (where the total number of room nights available is contestant every night of the week) I would appreciate your perls of wisdom
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
