Hello,

I am designing a PHP web application to be very similar to a hotel booking system.

Several providers, each provider has dozens of different products and each product has a different price depending on the date (this price can be altered by the providers at anytime).

I have very basic experience designing databases, so the "simplest" way I found to do it is:

I already have a table called "ROOMS" holding all the "rooms" from all different providers linked to the rest of the database (providers,roomtype,facilities,bookings,etc..).

So at this moment, I have another table called "PRICES" with columns DATE, ROOM_ID, PRICE1, PRICE2, PRICE3, BOOKED, PENDING, etc... The three different prices would be the same as simple occupancy, double, triple, etc...
So for 1 year each "room" would have 365 entries. That means if I have 1000 "hotels" and each one has at an average of "10" bedrooms, adding up to more than 3 million entries for each year.

Another solution I found is to have a table with two columns: "date_start" and "date_end" to group consecultive dates with the same price in order to reduce the number of lines.

Is the first solution going to make the queries very slow along the time?

I feel there are more creative solutions for this problem that I am not aware of. Any better ideas of how to handle that??

Thanks a lot!!

I'm curious to know if you have relationships between any of the tables you mentioned above. I'm sure there are solutions that can better fit what you're looking for. Can you explain this a little better, or maybe upload a picture of the database so we can better assist you.

Martin