mysql数据解耦,解耦MySQL数据与易用性

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.

解决方案

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.