How should i store availability calendar information in mysql? for [id] [date] [available/unavailable]

tags:

mysql
php

views:

answers:

+1 Q:

How should i store availability calendar information in mysql? for [id] [date] [available/unavailable]

I'm building a simple calendar for holiday cottages to show when they are booked or available.

What would be the fastest mysql table design for this, bearing in mind when users mark dates as available/booked they will do so via a start date and an end date.

i can see 2 obvious options

Store 'booked' data for every day [more rows]
or, store 'booked' data with 2 columns a start_date and end_date [more processing?]

Which is best or is there another method i'm missing?

The data is to show a visual calendar on each property's page

Definitely option 2. Index both start_date and end_date columns and you'll be fine. Your code will need to ensure there are no overlaps for a given cottage.

RedFilter 2010-04-16 14:58:43

would rendering the output onto a visual calendar in php not be slower with the start and end date method?

Haroldo 2010-04-16 15:02:38

I don't see how, but would have to see your code to really know.

RedFilter 2010-04-16 15:05:14

there's no code yet, but i was thinking id have to loop through the days between the start and end dates, creating dates with a class of booked...

Haroldo 2010-04-16 15:07:05

You are likely going to need to loop through every day of the month to create your calendar anyway. While doing that, you check to see if that date is in between the 'start' and 'end' dates of any of the bookings for that cottage. If so, you set the class to booked.

RedFilter 2010-04-16 15:21:18

Well, exactly how to approach this depends a bit on the entirety of your model, but my first thought would be to model this in terms of bookings. That is, I would have an entry for each booking, and store the start and end dates. I wouldn't worry too much about efficiency here. With the amount of data it sounds like you're talking about it should be plenty efficient as long as you don't do anything unreasonable.

T Duncan Smith 2010-04-16 15:01:42

the data is to show a visual calendar on each property's page, so needs to be looping through in php

Haroldo 2010-04-16 15:04:16

Well, depending on exactly what you need and what database you're using you might be able to push some of the processing onto the DB, either in SQL or by using a stored procedure. But the main point is that you really probably don't need to worry about efficiency here as long as you do something sane. How many dates are you going to display in this calendar? Even if you show a whole year at a time that's only 365 (or 366.) Unless you are doing something horrifically expensive inside that loop this shouldn't be very expensive.On the other hand storing each date is going to be a _real_ pain.

T Duncan Smith 2010-04-16 15:17:30

ansaurus

tags:

views:

answers:

How should i store availability calendar information in mysql? for [id] [date] [available/unavailable]

related questions