3

Assume a simple database for hotel reservations with three tables.

Table 1: Reservations This table contains a check-in and check-out date as well as a reference to one or more rooms and a coupon if applicable.

Table 2: Rooms This table holds the data of all the hotel rooms with prices per night and number of beds.

Table 3: Coupons This table holds the data of all the coupons.

Option #1: If you want to get an overview of the reservations for a particular month with the total cost of each reservation, you'd have to fetch the reservations, the rooms for each reservation, and the coupon (if one is present).

With this data, you can then calculate the total amount for the reservation.

Option #2: However, there is also another option, which is to store the total cost and discount in the reservation table so that it is much easier to fetch these calculations. The downside is that your data becomes much more dependent and much less flexible to work with. What I mean is that you have to manually update the total cost and discount of the reservation table every time you change a room or a coupon that is linked to a reservation.

What is generally recommended in terms of performance (option #2) version data independence (option #1).

UPDATE: It is a MySQL database with over 500 000 rows (reservations) at this point, but is growing rapidly. I want to optimize database performance at an early stage to make sure that the UX remains fast and responsive.

Bart Jacobs
  • 9,022
  • 7
  • 47
  • 88

3 Answers3

5

Let me start to answer this with a story. (Somewhat simplified.)

2011-01-01 I reserve a room for two nights, 2011-03-01 and 2011-03-02. You don't tell me which room I'll get. (Because you don't know yet which room I'll get.) You tell me it will cost $40 per night. I have no coupons. You enter my reservation into your computer, even though you're already fully reserved for both those nights. In fact, you already have one person on the waiting list for both those nights. (Overbooking is a normal thing, not an abnormal thing.)

2011-01-15 You raise the rates for every room by $5.

2011-02-01 I call again to make sure you still have my reservation. You confirm that I have a reservation for two nights, 2011-03-01 and 2011-03-02, at $40. (Not $45, your current rate. That wasn't our deal. Our deal was $40 a night.)

2011-02-12 One person calls and cancels their reservation for 2011-03-01 and 2011-03-02. You still don't yet have a room you know for certain that I'll be able to check in to. The other person from the waiting list now has a room; I'm still on the waiting list.

2011-02-15 One person calls and cancels their reservation for 2011-03-01 and 2011-03-02. Now I have a room.

2011-03-01 I check in with a coupon.

  • You can store the "current" or "default" price with each room, or with each class of rooms, but you need to store the price we agreed to with my reservation.
  • Reservations don't reserve rooms; they reserve potential rooms. You don't know who will leave early, who will leave late, who will cancel, and so on. (Based on my experience, once in a while a room will be sealed with crime scene tape. You don't know how long that will last, either.)
  • You can have more reservations than room-nights.
  • Coupons can presumably appear at any time before check out.

If you want to get an overview of the reservations for a particular month with the total cost of each reservation, you'd have to fetch the reservations, the rooms for each reservation, and the coupon (if one is present).

I don't think so. The price you agreed to should be in the reservation itself. Specific rooms can't resonably be assigned until the last minute. If there's one coupon per reservation, that might need to be stored with the reservation, too.

The only reporting problem is in making sure your reports clearly report how much expected revenue should be ignored due to overbooking.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • @Catcall: Thanks for the clear example. It shows how a simple example can be quite complex. However, your answer isn't exactly answering my initial question. The example was just an illustration of the problem, that is, is it best practice or recommended to have your data as independent as possible. In other words and to use the initial example, is it best to abstract cost away from the reservation altogether to make reservations only dependent on their relationships with rooms and coupons or, for performance reasons, is it better to include the cost (as a form of caching mechanism)? – Bart Jacobs Nov 04 '11 at 14:45
  • 1
    @BartJacobs: In databases, the number one problem--whether performance, flexibility, independence--is badly designed tables. Your example doesn't have too much or too little abstraction, flexibility, performance, or data independence. The reservation table is simply missing columns--the price we agreed on, for example. You're not "abstracting cost away from the reservation"; you're "omitting a necessary column". Adding the agreed price increases flexibility (decouples current and agreed prices), independence (changing rates don't affect earlier reservations), *and* performance (fewer joins). – Mike Sherrill 'Cat Recall' Nov 04 '11 at 15:42
  • Great insights. Thanks for this, Catcall. – Bart Jacobs Nov 04 '11 at 17:30
1

The response of your answer depends of the size of your database. For small database option #1 is better, but for huge database option #2 is better. So if you could say how many rows you got in table, and the database used (oracle, sqlserver etc.) you will have a more precise answer.

Toto
  • 149
  • 2
  • I see it's an hostel, so I think it's a small base. So Use a clean database: option #1. – Toto Nov 04 '11 at 11:48
0

You can add a table holds the data of the rooms`s historical prices and reason for change. Table 2 only records the latest price.

Red Butterfly
  • 1
  • 1
  • 1
    Welcome to stack-overflow .... your question not enough quality, please read help center, section `how to ask good question` here https://stackoverflow.com/help/how-to-ask and you can get tour here https://stackoverflow.com/tour – Amirhossein Aug 25 '21 at 07:04