解耦MySQL数据与易用性 [英] Decoupling MySQL data versus ease of use

问题描述

假设有三个表的酒店预订的简单数据库。

表1：预订
此表包含

$ b此表保存所有酒店房间的数据，每晚价格和床数。

表3：优惠券
此表格包含所有优惠券的数据。

选项＃1：
如果您想了解预订每个预订的总费用，您必须提取预订，每个预订的房间和优惠券（如果有的话）。

使用这些数据，您可以计算预订总金额。

选项＃2：还有另一个选项，即在预留表中存储总成本和折扣，以便更容易获取这些计算。缺点是，你的数据变得更加依赖和更不灵活的工作。我的意思是，你必须手动更新预订表的总成本和折扣，每次更改房间或预订链接的优惠券。

通常建议在性能（选项＃2）版本数据独立性（选项＃1）方面。

更新：
它是一个MySQL数据库500 000行（保留）在这一点上，但是增长迅速。我想在早期阶段优化数据库性能，以确保UX保持快速响应。

解决方案

让我开始回答这个故事。 （有点简化。）

2011-01-01我预订一个房间两晚，2011-03-01和2011-03-02。你不告诉我我会得到哪个房间。 （因为你不知道我会得到哪个房间。）你告诉我，它将每晚花费40美元。我没有优惠券。您输入我的预订到您的计算机，即使您已经完全保留这两个晚上。事实上，你已经有一个人在这两个晚上的等待名单上。 （超量预订是一个正常的事情，而不是异常的事情。）

2011-01-15你将每个房间的房价提高5美元。

2011-02-01我再打电话，以确保您仍然有我的预订。您确认我已预订两晚，2011-03-01和2011-03-02，价格为$ 40。 （不是45美元，这是我们的交易，我们的交易是每晚40美元。）

2011-02-12一个人打电话并取消预订为2011-03-01和2011-03-02。你还没有一个房间，你知道确定，我可以检查。来自候补名单的另一个人现在有一个房间;

2011-02-15一人呼吁并取消对2011-03-01和2011-03-02的预订。现在我有了一个房间。

2011-03-01我用优惠券办理入住手续。

• 您可以存储每个房间的当前或默认价格，或
  房间的每个类别，但您需要存储我们同意的价格与我的
  预订。


• 预订不预订房间;他们预留潜在的房间。你
  不知道谁会早点离开，谁会离开，谁将
  取消，等等。 （根据我的经验，有一段时间，一个房间将
  用犯罪现场磁带密封，你不知道持续多长时间。）


• 你


• 优惠券可能会在退房之前随时出现。

如果您想查看每次预订总额为
的特定预订的概览，您必须提取
预订，每个预订的房间和优惠券（如果有一个
）。

我不这么认为。你同意的价格应该在预订本身。具体的房间不能共鸣，直到最后一分钟。如果每次预订有一张优惠券，那么也可能需要与预订一起存储。

唯一的报告问题是确保您的报告清楚地报告由于超量预订而应该忽略的预期收入。

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.

这篇关于解耦MySQL数据与易用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！