酒店预订系统的数据库设计
我想为酒店预订系统设计一个数据库结构。
我的架构：
client (
client_id,
lastname
)
reservation (
reservation_id,
client_id,
checkIndate,
roomno
)
我的问题出在签入部分。我已经创建了预订；我还需要创建签到表吗？如果是这样，它的架构应该是什么？
顾客进入酒店有两种方式：
- 通过预订（到达前预订房间）或
- 通过入住（没有预订就进入酒店）
如何存储在酒店入住的记录？
I want to design a database structure for a hotel reservation system.
My schema:
client (
client_id,
lastname
)
reservation (
reservation_id,
client_id,
checkIndate,
roomno
)
My problem is in the check-in part. I have already created a reservation; do I still need to create a check-in table? If so, what should its schema be?
There are two ways customers can enter a hotel:
- by reservation (reserving a room prior to arrival) or
- by checkIn (goes to the hotel without reservation)
How Do I store the records that are checked-IN in the hotel??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只是几个想法。我注意到“roomno”是“reservation”表中的一个字段。我假设您还有一个“房间”表，其中的主键为“roomno”，以及您可能跟踪房间的任何其他数据（电话号码、床位数量等）。
我要做的是创建一个名为“占用”的表。该表将具有三个字段“client_id”、“roomno”和 checkin_time。 “client_id”和“roomno”都是外键（分别是客户端和房间表）。为了确保唯一性，我会将它们连接起来作为主键（毕竟，您可以让一个客户购买两间房间）。
当客户签出时，您将从“占用”表中删除该行。您需要将该行存档到另一个表（“history”、“pastStays”...类似的东西）中，您还需要记录“checkout_time”。
就我建议或假设的更改而言，它们的外观如下：
Just a couple of thoughts. I notice that "roomno" is a field in the "reservation" table. I assume then that you also have a "room" table, which has "roomno" as its primary key and whatever other data that you might keep track of on a room (phone_number, number_of_beds, etc...).
What I would do, is create a table called "Occupancy". This table would have three fields "client_id", "roomno", and checkin_time. "client_id" and "roomno" would both be foreign keys (to the client and room tables, respectively). To ensure uniqueness, I'd concatenate them both to make the primary key (after all, you could have one client buy two rooms).
When the client checks-out, you would remove that row from the "Occupancy" table. You'll want to archive that row in another table ("history", "pastStays"...something like that) where you'll also want to log the "checkout_time".
In terms of the changes that I have suggested or assumed, here is how they'd look:
我认为最好将预订与办理入住脱钩。也可以通过第三方预订系统（分配给他们的房间块）进行预订。此外，单次预订可以为多位客人提供，但登记入住只能为一位客人提供。此外，您可能需要单独的房间预订表 - 每个房间一个条目。通常情况下，单次入住的客人也可能使用多个房间（不同的入住时间）。因此，将入住与入住脱钩可能很有用。此外，您可能需要单独保存库存（房间可用性）。这意味着下表。
预订（id、reserverId、fromDate、toDate） - 每个预订请求一个条目
RoomBooking（id、resId、roomNo、fromDate、toDate） - 每个预订每个房间一个条目
入住（id、resId、guestId、checkInDate、checkOutDate） -每位客人可输入一项 占用
情况（id、checkInId、fromDate、toDate） - 每个房间在指定时间内可输入一项（这与客房预订类似，但此实际情况，RoomBooking 按计划）
I think it is better to decouple reservation from check-in. It is also possible that reservations may be done though third party reservations systems (a block of rooms allotted to them). Also single reservation can be for multiple guests, but a checkin is for a single guest. Also you may need separate roomBooking table - one entry per room. It is also typically possible that for single check in guest may use multiple rooms (for different duration). Hence it may be useful to decouple check-in from occupancy. Also you may need to keep inventory (room availability) separately. That means the following tables.
Reservations (id, reserverId, fromDate, toDate) - one entry per reservaion request
RoomBooking (id, resId, roomNo, fromDate, toDate) - one entry per room per reservation
Check-ins (id, resId, guestId, checkInDate, checkOutDate) - one entry per guest
Occupancies (id, checkInId, fromDate, toDate) - one entry per room for specified duration (this is similar to room booking but this is actual, RoomBooking is as planned)