Hi all.

I've been developing web-based application that stared with few tables and that was fine. But slowly it's grown and tables have been added until now. I got to a point where I have to stop and think about it. It starts to be not easy, masochistic and so on.

I'd be grateful for your help, which way to go.

I use as db MySQL and I have these tables:

hotels (main table)
hotels_rooms (rooms which belong to hotel, so it has a bond to hotels)
hotels_rooms_services (this is M:N table for hotels_rooms and services)
hotels_rooms_facilities (same as services, but another type)
hotels_properties_locations (locations for hotels, hotel contains and id of location)

accommodation_status
accommodation_availability
accommodation_rates

These accommodation tables look like:

start_date, to_date, id_room, some_data_filed, ... another_data_filed
(data fields are specific for each type of table)

And I need to select all hotels where are available rooms (it means for some dates, status is 1, availability is > 0 and there are rates for all days)

For now this is my solution

I have a table tmp_days, like
2010-07-01
2010-07-02
....
2015-12-31

And I join accommodation tables to this one, select rooms which are available, that's fine. But now I save it into temporary table (the result)

And now I need to select rooms which meet other criteria like having some services, facilities.
Also I need to select hotels where these rooms are in and hotels also are filtered by city, location, type of accommodation etc.

Now I use temporary tables and joining, but it's slow and it's not so clear.

What is best way of filtering data like these? Store it to application and use SELECT * FROM where IN ( ... ) ?
Or use simple but quick selects, but there will be too much queries and maybe overhead of each query might cost too much.

I use a perl + DBI for working and processing data.

If anyone has read all the text and would point me somewhere, I really would be grateful.