酒店预订系统：如何存储预订每晚的单独价格？
我有您期望的标准表格，例如“房间”、“预订”等。目前一切都在关系数据库中。
“Reservation”表存储诸如 room_id、入住日期和退房日期等项目。
现在，简单地说，当进行预订时，系统会根据“RoomPrice”表进行检查并获取每晚预订的费用（取决于日期、入住情况等） - 每晚的费用可能会有所不同，具体取决于根据当前价格。
显然，预订后，每晚的价格都是固定的。因此，即使房价在事后更新，该预订仍保持约定的价格，因为它是在价格更改之前进行的。
我的问题是：我应该如何存储预订时每晚商定的个人价格？
我正在考虑使用另一个表“PriceForNight”，它将存储预订的每晚的预订 ID、价格和日期。
我认为唯一可能的问题是可扩展性。如果平均预订时长为 5 晚，则意味着“PriceForNight”表的增长速度将比“Reservation”表快 5 倍左右。
“PriceForNight”数据是否最好存储在 NoSQL 数据库或类似数据库中？
正在考虑的另一个选项是将每晚的价格作为逗号分隔的字符串存储在“预订”表行的单列中，例如：“150.00,175.00,175.00,200.00,150.00”表示 5 晚的预订。
我可能想得太多了，因为真正的问题只有在增长速度加快 1000 倍时才可能存在，但我喜欢做正确的事情，所以我想我应该向社区伸出援手。
任何意见都非常感谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
纯粹的关系方法是拥有一个 ReservationNight 表，其中存储预订每晚的详细信息，包括价格。是的，表会快速增长，但无论您如何存储价格，数据都会快速增长。
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.
一般来说，逗号分隔的列表不属于 SQL 数据库。我想说你最好的选择是连接表。
StackOverflower Bill Karwin 在这里回答得最好：真的在数据库列中存储逗号分隔列表吗不好？
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?
您可以使用日期范围而不是固定日期。
因此，如果我进行预订：
在数据库中：
如果每天都有不同的价格是很常见的，这不会减少行数。但这种情况很少见，最多会减少到 1 行。
You could use date ranges instead of a fixed date.
So if I make a reservation:
In db:
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.
一张存储每晚费用的表，每晚一行似乎是明智的。
每间客房每年可出租不超过365晚。 （在现实世界中，这几乎是正确的。汽车旅馆业务实际上比看起来更复杂。）
如果您有 200 个房间，您会看到大约 200 * 365 行，或者每个房间 73,000 行年。 （我的算术正确吗？）按照这个速度，如果技术根本没有进步，那么至少 100 年内您不必考虑担心 SQL dbms 的性能。
您可能还会发现这个答案很有用。它遵循同样的思路。
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.