3

I have the standard tables you would expect, such as 'Room', 'Reservation', and so on. Everything is currently in a relational database.

The 'Reservation' table stores items such as room_id, check-in date, and check-out date.

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

My question is: How should I store these individual, agreed upon prices for each night when a reservation is made?

I'm considering using another table, 'PriceForNight' which would store the reservation id, price, and the date, for every night of the reservation.

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.

Would the 'PriceForNight' data be better stored in a NoSQL database or something similar?

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.

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.

Any input is much appreciated.

Alex
  • 31
  • 1
  • 2
  • Since you are making an entry for each night in that table anyway, why not store this data in the Reservation Table? You can add two columns, Rack Rate and Actual Rate. – Mark Kram Jan 28 '12 at 15:29
  • @MarkKram There is only one entry per reservation in the 'Reservation' table, no matter how many nights there are. – Alex Jan 28 '12 at 23:11

4 Answers4

3

The purely relational approach would be to have a ReservationNight table, which stored details about each night of a reservation, including the price. Yes, the table will grow quickly, but no matter how you store the prices, the data will grow quickly.

Ned Batchelder
  • 364,293
  • 75
  • 561
  • 662
  • Thank you for the input. So my 'PriceForNight' table is the same as what you're suggesting? 'reservation_id', 'price', and 'date'? – Alex Jan 27 '12 at 20:08
  • Yes, although I'm assuming that it could be more than just the Price being stored. The point is that you have information you need to store per reservation per night, so name the table ReservationNight. If it only has one interesting column (Price), so be it, that shouldn't go into the name of the table. – Ned Batchelder Jan 27 '12 at 20:15
  • I understand entirely. Thank you for your time. – Alex Jan 27 '12 at 20:25
1

In general, comma-delimited lists don't belong in SQL databases. I'd say your best bet would be a junction table.

StackOverflower Bill Karwin answered it best here: Is storing a comma separated list in a database column really that bad?

In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems:

  • Can't ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
  • Can't use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
  • Can't enforce uniqueness: no way to prevent 1,2,3,3,3,5
  • Can't delete a value from the list without fetching the whole list.
  • Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan.
  • Hard to count elements in the list, or do other aggregate queries.
  • Hard to join the values to the lookup table they reference.
  • Hard to fetch the list in sorted order.

To solve these problems, you have to write tons of application code, reinventing functionality that the RDBMS already provides much more efficiently.

Comma-separated lists are wrong enough that I made this the first chapter in my book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

There are times when you need to employ denormalization, but as @OMG Ponies mentions, these are exception cases. Any non-relational "optimization" benefits one type of query at the expense of other uses of the data, so be sure you know which of your queries need to be treated so specially that they deserve denormalization.

Community
  • 1
  • 1
zeroef
  • 1,949
  • 23
  • 32
  • I hadn't seen that answer before, thanks. The comma-delimited idea is definitely out. – Alex Jan 27 '12 at 20:24
0

You could use date ranges instead of a fixed date.

So if I make a reservation:

Night 27 01 2012: 20 $
Night 28 01 2012: 20 $
Night 29 01 2012: 30 $

In db:

reservationId from     to        price
5457848       20120127 20120128  20
5457848       20120129 20120129  30

If it is very common that every day has a diffident price this would not decrease the amount of rows. But it is verry rare then it would reduce most to 1 row.

Jeff
  • 1,871
  • 1
  • 17
  • 28
  • We also considered this earlier, but thought it would create a lot of processing overhead having to deconstruct the data every time the prices need to be shown. Also if we wish to generate financial reports or perform analysis, separate nights would mostly likely be easiest. – Alex Jan 27 '12 at 20:23
  • Then you should indeed with the new table. Its the best practice and it would create more rows. Maybe you can put a flag in the main reservation were you select if the cost is the same for all the nights and you pot the cost in that row. If its not then refer to the other table. – Jeff Jan 27 '12 at 21:12
  • Also a good suggestion. We'll take that one into consideration. Thank you for your time. – Alex Jan 27 '12 at 22:52
0

A table that stores each night's cost, one row per night seems sensible.

Each room can be rented for no more than 365 nights a year. (In the real world, that's almost true. The motel business is actually more complicated than it looks.)

If you have 200 rooms, you're looking at roughly 200 * 365 rows, or 73,000 rows per year. (Did I get that arithmetic right?) At that rate, if technology doesn't improve at all, you don't have to consider worrying about the performance of a SQL dbms for at least 100 years.

You might also find this answer useful. It follows the same line of thinking.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185