Need to store multiple date range values and retrieve them via sql

I'm building a home rental system. The system stores profiles of homes and users can book homes for rent for periods as between one day to a year. I've got the booking part all set up except I am faced with a requirement from teh client to be able to set certain dates and date ranges as un bookable for homes.

A home can be available for a whole year for re开发者_运维百科nt, or can be available for 6 months, or be unavailable on discreet days eg: Holidays and weekends for summer homes etc.

I'm perplexed as how would I be able to set up a database table to store this information considering that the information must be retrievable by a sql query. I mean consider the following situation, a home can be rented through out the year except on wednesdays, the 4th of July, 10 November to 25th December and 31st December.

How do I store this in a database and be able to run a query to check for a homes availability between set dates. I'm wokring in php MySql

---

There are two different concepts that you're describing: the "on Wednesdays" is not as much of a date range as it is a recurrence pattern. The same goes for "weekends".

You're probably looking at two different tables in addition to your property table that define these unavailable dates: one that represents specific date ranges and one that represents recurrence patterns.

                         Property
                            |
              -------------- ---------------
             |                              |
PropertyUnavailableRecurrence PropertyUnavailableRange 

(Bear in mind that you might want to figure out shorter names)

PropertyUnavailableRecurrence would need to store the information necessary for turning "Wednesdays" and "weekends" into viable decision logic. I can't model this for you, since all of you've presented in this pattern are specific days of the week, but I'd imagine that you'd need to be able to account for "First of the month" or "Second Wednesday of the month", but I don't know. In any case, this is where you'd need to store that information.

PropertyUnavailableRange would just contain simple From and To dates that define the range. This part is pretty simple.

Of course, an alternative would be to take the recurrence patterns specified in the application and turn them into discreet PropertyUnavailableRange records, but you'd still need to set up a table to store these recurrences and associate the discreet records with a recurrence so that you could manage them.

---

One approach is to have a table, PropertyUnavailable, with the following structure:

create table PropertyUnavailable
(
  property_id number not null,
  when        date not null
);

This table would have a row for each day that the property is not available because of a black out period (e.g., every Wednesday, Holiday, etc). I am ignoring how you will store the meta information of the pattern -- all this table wants are rows for each day where the property is not available because of a blackout period.

I assume you will also have a table for reservation days, PropertyReserved, with the same structure as above plus a foreign key to reservation_id (or something similar).

Now to see what day's are unavailable/reserved for a given date range, the sql would be something like this:

SELECT a.when, 'blackout'
FROM PropertyUnavailable a
WHERE a.when between <from_date> to <to_date>
UNION ALL (
SELECT b.when, 'reserved'
FROM PropertyReserved b
WHERE b.when between <from_date> to <to_date>
);

If nothing is returned with the query, then the property is available between the date range specified (from_date, to_date).

---

Did you consider simply booking those "unbookable" dates in the name of the system?

---

It appears that you are not clear about what is required to be stored in teh database; and what is required in code segments. Set the "unbookable" dates aside for a moment, assume you only have actual booked dates. Catcall has a point. What does your current code look like, when you search for available dates ?

SQL is quite capable of handling dates and performing date arithmetic. My NonSQL is not, you will have to store more in the databse than in real SQL. But you do not need to store rows per date. The Reservation table needs FromDate and ToDate only.