Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Designing database to get the price based on date and service provider.

    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!!

  2. #2
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by kraeski View Post
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •