|Best way to store calendar data in a db?|
Would like to make availability calendar for a rental site
| 8:42 am on Oct 16, 2005 (gmt 0)|
I'm having trouble planning the database schema to store the data for an "availability calendar" of a rental site that has a dozen apartments. That is, I show a full year of dates and each day is colored differently depending on whether the apartment is available for rental that day.
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... :(
| 9:14 am on Oct 16, 2005 (gmt 0)|
I would be inclined to create booking records in your database rather than trying to hold data for individual days.
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):
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.
| 9:59 am on Oct 16, 2005 (gmt 0)|
Thanks, dmorison, for your very fast reply. Yes, I had also thought of storing in the database, not days and their status, but bookings. The problem is that I also need each apartment-owner to be able to run their calendar, and enter a spread of days that's either "available" or "not available" or "don't know". And I'm trying to think about how to reset the status of each days. I guess I could determine an algo that would go through the existing booking-records and check for overlapping dates and reset booking records accordingly. It's giving me a headache for now!
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?)
btw I do not need to store actual booking info - who the renter is, etc.
| 11:43 am on Oct 16, 2005 (gmt 0)|
The view you need is just a report from the database.
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.
| 2:56 pm on Oct 16, 2005 (gmt 0)|
|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.
| 2:58 pm on Oct 16, 2005 (gmt 0)|
Yes, aspdaddy, I realize that page is just a display of the data, it was just to illustrate a little better than my explanation, my needs :)
oh, wow do I have a lot to learn! :(
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]
| 3:03 pm on Oct 16, 2005 (gmt 0)|
|I wouldn't offer an interface to set days as "available". |
I have to allow an owner to set a property to available, because renters sometimes cancel.
|Use the same "indate" and "outdate" fields, and then your logic for rendering an availability calendar is unaffected. |
hm - that sounds a bit complex. What if I have:
indate = 2005-10-01
outdate = 2005-10-15
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.
| 3:17 pm on Oct 16, 2005 (gmt 0)|
If someone cancels, just delete the associated booking record.
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).
| 9:38 pm on Oct 16, 2005 (gmt 0)|
Actually, the idea is not to permit booking on-line. It's an availability calendar that will be maintained by the owner only.
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.