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

Since you need to perform queries that seek exact dates, I guess you need to store the exact dates. Textual information ("on Mondays", "Winter Thursdays and Fridays"...) should be stored as well but merely with informative purposes (e.g., to be displayed in the tour info page).

As for the dates, you have two options:

1) Individual dates

tour_id date
======= ==========
      1 2010-12-01
      1 2010-12-02
      1 2010-12-05
      2 2010-01-15

2) Date ranges:

tour_id from_date  to_date
======= ========== ==========
      1 2010-12-01 2010-12-05
      2 2010-01-15 2010-01-15

I presume that #1 will be easier to maintain.

Answer 2

Hello,

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

Answer 3

Will you be doing anything beyond just showing dates in this database, e.g. bookings? Because that would strongly point towards going even further than the bit-wise solution: Having one record per departure date.

Storage space questions should be irrelevant unless you have trillions of tours, which is unlikely. But it would become very easy to query dates, build totally irregular intervals, and cancel select dates within a range.

The hard part with this is editing. When creating a new tour, you would have to create the date records according to the specified interval. If the interval changes later, you'd have to roll back creation.