title: Hotel reservation system: How to store individual price for each night of a reservation?
body: <p>I have the standard tables you would expect, such as 'Room', 'Reservation', and so on. Everything is currently in a relational database.</p>
  
  <p>The 'Reservation' table stores items such as room_id, check-in date, and check-out date.</p>
  
  <p>Now, to put it simply, when a reservation is made, the system checks against a 'RoomPrice' table and gets the cost of each night reserved (depending on date, occupancy, etc.) - the cost can be different for each night depending on the current prices.<br>
  Obviously when a reservation is made, the price of each night is fixed. So even if room prices are updated after the fact, that reservation still stays at the agreed upon price, as it was made before the price change.</p>
  
  <p>My question is: How should I store these individual, agreed upon prices for each night when a reservation is made?</p>
  
  <p>I'm considering using another table, 'PriceForNight' which would store the reservation id, price, and the date, for every night of the reservation.</p>
  
  <p>The only possible issue I see with this is scalability. If the average reservation length is 5 nights, that means the 'PriceForNight' table will be growing about 5 times faster than the 'Reservation' table.</p>
  
  <p>Would the 'PriceForNight' data be better stored in a NoSQL database or something similar?</p>
  
  <p>Another option being considered is storing the prices for each night as a comma-delimited string in a single column also in the 'Reservation' table row, for example: "150.00,175.00,175.00,200.00,150.00" for a 5 night reservation.</p>
  
  <p>I could be over-thinking this, as a real problem might only exist if it was growing 1000 times faster, but I like to do things right so I thought I'd reach out to the community.</p>
  
  <p>Any input is much appreciated.</p>