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)
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
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.