Storing Calendar Data in MySQL

问题:

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

回答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.

回答2:

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.