The optimal way to store available dates in MySQL for several years

Question

We are developing a MySQL database (with interface and backend) with holiday packages. We have all figured out, except for the best method for storing travel package departure dates .

Some packages are available almost every day of the year; others are only available on tuesdays and in environments; others leave every Monday from May to September; other packages depart only a few specific dates throughout the year; etc. etc.

We need to associate each tour with suitable departure dates. Then we need to execute queries like “give me the packages that are available on date X”, “give me packages that I can take in the first week of January,” “give me these tours between the dates X and Y”, etc.

A trivial way to do this: store 365 bits per bit per day for each packet . But this is obviously not a good solution in terms of storage space. Keeping date ranges would be a good solution, but this was not due to the fact that most packages are only available on certain days of the week and therefore it is not possible to optimally encode in ranges.

Anyone can help us with this? How can we save the departure dates of the tour in the database?

Thank you so much!

Ramon

Answer 1

.

( , ). . 7- ( 1). , Start = End. , , , , , .

. , .

:

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

, №1 # 2:

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

Answer 2

, , , . ( " ", " "...) , (, ).

, :

1)

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

2) :

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

, # 1 .

Answer 3

-, , . ? , , : .

Storage space issues should be inappropriate if you do not have trillions of tours, which is unlikely. But it would be very easy to query for dates, plot completely irregular intervals, and deselect dates within a range.

The hard part of this edit. When creating a new tour, you will need to create date entries in accordance with the specified interval. If the interval changes later, you will have to cancel the creation.