Optimal way to store in MySQL available dates in multiple years

Question

We are developing a MySQL database (with frontend and backend) with holiday packages. We have everything figured out except for the best method to store the departure dates of the tour packages.

Some packages are available almost every day of the year; others are only available on tuesdays and wednesdays; others leave every monday between May and September; other packages depart just a few specific dates along the year; etc etc...

We need to associate each tour to the appropiate departure dates. Then, we need to perform queries of the type "give me those packages that are available on date X", "give me packages that I can take on the first week of January", "give me those tours between date X and Y", etc.

The trivial way to do it would be to store 365 bits, on bit per day, for each package. But this is obviously not a good solution in terms of storage space. Keeping ranges of dates would be a good solution it it weren't for the fact that most packages are available only on certain days of the week, and therefore, cannot be optimally codified in ranges.

Anyone can help us out with this? How can we keep the tour departure dates in the database?

Thanks a lot!

Ramon

Answer 1

Here's my proposed format.

Store your availability dates in a separate table as intervals (Start date, End date). An offer will have one or more intervals. For each of these intervals define a 7 bit restriction filter for the days of the week that are available (default will be all bits set to 1). If you need just one date you put Start = End. Of course this model does not cover all cases but it seems sufficient to me and it may prove useful if you have a lot if offers valid only on certain days of the week.

You may also want to exclude certain dates from you intervals. For that you will need another table where you store individual days.

Examples:

id | start | end | week_days
1 | 2011-01-01 | 2011-03-31 | 1111100 - The offer is available in any weekday from January to March
2 | 2011-01-31 | 2011-01-31 | 1111111 - An offer available just in one day

If you want to use exceptions you could invalidate offer #1 for the date you added offer #2:

id | id_period | date
1 | 1 | 2011-01-31