Forum Moderators: open
When the availability is changed for a given period, should I store every date of that period and the availability status? So for each apartment , that would mean 365 records?
I'm a bit lost here... :(
I'm kinda figuring that appartment rental is similar to hotel room booking, in that you can have a new tennant checking in on the same day as the previous tennant checks out.
So, when a booking is made (worry about availability in a moment), you create a new booking record, containing amongst other things (such as your customer data):
appartment_id
indate
outdate
Now, when you want to render your display of available days for an appartment, first select all outstanding booking records for that appartment (where outdate > today), and then populate an array of occupied dates by traversing each booking record and filling in your array accordingly. So, for example, if one record contains:
indate = 2005-11-23
outdate = 2005-11-25
You would set the following in an associative array (or functional equivalent structure in whatever language you are using):
$occupied[2005-11-23] = 1;
$occupied[2005-11-24] = 1;
(note that you would not black-out the 25th if you are able to take a new tennant on the same day as the previous tennant checks out). You can then use the $occupied array as you render your availablility display. When deciding whether to show availability or not, simply refer to the $occupied array.
An example of the sort of calendar I'm trying to render is this (hm, I'm not sure whether I'm allowed to post such an explanatory url?)
[holiday-rentals.com...]
btw I do not need to store actual booking info - who the renter is, etc.
Personally I'd use a crosstabs query or pivot table to transform normalised data into this report , but a dates table and a left outer join (as suggested by txtbakers in another post!) or an array as suggested above are other good solutions.
and enter a spread of days that's either "available" or "not available" or "don't know"
I wouldn't offer an interface to set days as "available". Instead, just assume that every day is available unless otherwise stated, either by a booking record or a black-out record, created by the landlord of the property.
In its easiest implementation, the landlord could simply book unavailable days to themselves; or you could take it a step further and have a flag in your booking table that indicates that the booking is either a normal customer booking, or a period set by the landlord as unavailable. Use the same "indate" and "outdate" fields, and then your logic for rendering an availability calendar is unaffected.
oh, wow do I have a lot to learn! :(
crosstabs query?
pivot table?
Would you have a url for that "left outer join as suggested by txtbakers in another post"? I searched and couldn't find it.
[edited by: louponne at 3:04 pm (utc) on Oct. 16, 2005]
I wouldn't offer an interface to set days as "available".
Use the same "indate" and "outdate" fields, and then your logic for rendering an availability calendar is unaffected.
and then the owner declares a new period:
indate = 2005-10-10
outdate = 2005-10-20
my script is going to have to manage the overlap, either creating several periods with indates and outdates, or merging the periods into one.
Similarly, if a landlord decides to make available a period which they had previously blacked-out, again just delete the record.
There's no need for your availability display script to worry about overlap because that should be "worried" about elsewhere, predominantly when an actual booking request is made.
When a booking request is made, you must check the table again for availability in a single-threaded process before creating the booking record (thus ensuring no double-bookings).
And as I know these folks, we do have to deal with overlapping - there's no way to be sure that the dates they enter for start and end of period won't overlap already existing slots.
