8

Just a quick architecture question really on storing calendar data.

Basically, I have a database of services for rental. On the front end, there is a calendar to show either "Available" or "Unavailable" for every future date. In the back-end the user can set any date/date range to available or unavailable (1 or 0) on a jQuery calendar.

The question I have is how would you go about storing this data in mysql and retrieving it on the front end?

Possible have all dates available and store the unavailable dates? Then if they are set to available again, remove the record for that date?

Cheers, RJ

TMPilot
  • 247
  • 8
  • 17

2 Answers2

6

Possible have all dates available and store the unavailable dates? Then if they are set to available again, remove the record for that date?

Yes, I'd go with that, except I would not remove the record when renting expires - you'll easily know a renting expired because it's in the past, so you automatically keep the history of renting as well.

After all, there is infinite number of available dates1, so you'd have to artificially limit the supported range of dates if you went the other way around (and stored free dates).

1 In the future. And, in some sense, in the past as well.

Also, I'm guessing you want some additional information in case a service is rented (e.g. name of the renter) and there would be nowhere to store that if renting were represented by a non-existent row!

Since the granularity of renting is a whole day, I think you are looking at a database structure similar to this:

enter image description here

Note how RENTING_DAY PK naturally prevents overlaps.

Alternatively, you might ditch the RENTING_DAY and have START_DATE and END_DATE directly in RENTING, but this would require explicit range overlap checks, which may not scale ideally.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Great answer, thanks. And thanks for the link to overlap checks. It's important I store the date range so I'll run checks for overlaps and update/replace as necessary. – TMPilot Sep 11 '12 at 14:03
  • +1 After having thought about it some more and while answering a [related SO question](http://stackoverflow.com/questions/12374023/store-dates-and-rates-in-a-database), I think single day records are easier to manage for changes/modifications that occur during that date range. OTOH, if there can be no updates to single days between `start_` and `end_date` then he could go either way. – aneroid Sep 12 '12 at 09:33
1

Decide whether the default is Available or Unavailable.

Possible have all dates available and store the unavailable dates?

So default is Available?

Then you can put unavailable_start and unavailable_end - store it as a date field. For single days, unavailable_start = _end. Then it's easy to query for a month or any date range and return the unavailability periods in that range. Then have jQuery parse it to display the calendar details for those dates.

aneroid
  • 12,983
  • 3
  • 36
  • 66