Storing Calendar Data in MySQL

Question

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

Answer 1

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 dates¹, so you'd have to artificially limit the supported range of dates if you went the other way around (and stored free dates).

¹ 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:

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.