酒店预订系统的数据库设计

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??

如何将入住的记录保存在酒店?

2 个解决方案

#1

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...).

我有几个想法。我注意到“roomno”是“预订”表中的一个字段。我假设您也有一个“房间”表，它的主键是“roomno”，以及您可能在房间中跟踪的其他数据(phone_number、number_of_beds等)。

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).

我要做的是创建一个名为“占用”的表。这个表将有三个字段“client_id”、“roomno”和checkin_time。“client_id”和“roomno”都是外键(分别指向客户端和房间表)。为了确保唯一性，我将它们连接在一起以生成主键(毕竟，您可以让一个客户购买两个房间)。

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".

当客户端签出时，您将从“占用”表中删除这一行。您将希望将该行归档到另一个表中(“history”、“paststay”……)类似的东西)在那里你也想要记录“checkout_time”。

In terms of the changes that I have suggested or assumed, here is how they'd look:

关于我建议或假设的改变，以下是它们的样子:

table: Room
roomno INT
phone_number INT
number_of_beds INT
smoking_allowed BOOLEAN

table: Occupancy
client_id BIGINT (FK referencing Client) (part of PK)
roomno INT (FK referencing Room) (part of PK)
checkin_time DATETIME

table: pastStays
client_id BIGINT (FK referencing Client) (part of PK)
roomno INT (FK referencing Room) (part of PK)
checkin_time DATETIME
checkout_time DATETIME

#2

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

预订(id, reserverId, fromDate, toDate) -每个预订请求一个条目

RoomBooking (id, resId, roomNo, fromDate, toDate) - one entry per room per reservation

订房(id, resId, roomNo, fromDate, toDate) -每个房间每个房间有一个房间。

Check-ins (id, resId, guestId, checkInDate, checkOutDate) - one entry per guest

签到(id, resId, guestId, checkInDate, checkOutDate) -每位客人一项

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)

入住率(id, checkInId, fromDate, toDate)——每个房间在指定时间段内的一个条目(这与客房预订类似，但这是实际的，客房预订是按计划进行的)

#1