Algorithm : how to organize database search with multiple parameters?

I need to create a search script that would look into a table of house availability dates and match user search input.

This is sample data:

House is available for rent in these two date ranges: 

[0] => Array
(
  [start_date] => 2011-01-01
  [end_date] => 2011-03-31
  [nb_of_people] => 6
  [price_per_night] => 100
)

[1] => Array
(
  [start_date] => 2011-04-01
  [end_date] => 2011-04-30
  [nb_of_people] => 7
  [price_per_night] => 110
)

I have prepared script that merges continuous/overlapped dates in one db record, so the result would look like this:

(
  [start_date] => 2011-01-01
  [end_date] => 2011-04-30
  [availability_ids] => 0;1
)

---

UPDATED:

calculations were not clear so I updated the post:

Lets say user searched for house:

dates: from 2011-03-28 to 2011-04-08, for 6 people, and he is willing to spend from $90 to $108 dollars per night.

This is my manual calculations for this search:

1. dates match our merged dates interval
2. total number of dates user wants to stay is: 11 days
3. price for the first period 2011-03-28 to 2011-03-31 is 3days * $100 = $300
4. price for the second period 2011-04-01 to 2011-04-08 is 8days * $110 = $880
5. total average price per night = 300 + 880 / 11 = $107.27
6. price and number of people matches user input, so we display this house

---

If you merge dates and calculate average price per night for the given period, search script should match array of data provided above.

My question is this: How would you organize data in database and how search script algorithm should look lik开发者_运维技巧e to retrieve results from database instantly.

P.S. I am OK to run cron job to re-organize data on daily basis if needed.

Thanks for any advise.

---

You have a fairly complicated problem on your hands.

You're assuming that the customers are all right with switching in the middle of their stay. They might not want to. The example you have has them moving. Did you ask them whether this was all right?

You might also be assuming that they're all right with living in separate houses. Is two houses with 3 spaces apiece fine, or do they want just one house where everyone can stay together? Are they sharing quarters with people they don't know?

I'd look at your assumptions (those were just some examples; I'm sure there are more) and consider whether or not you want to mix in their preferences. Then this will lead you to how you combine query results -- or more importantly, how you don't combine them.