多房间预订数据库设计：一对多 [英] Database Design for Multiple Room Reservation: One To Many

问题描述

主要实体：
客户
客人
预订
房间分配

我想实现一个多房间预订数据库设计。首先，我想首先解释这个概念：

1. 客户端是获得预订的人。 / li>
   
2. 客户端一次只能有一个预留


3. 客户端可以预留多个



4. 访客是分配到特定房间的人。

   
   
   

   所以对于表：

   
   
   

   客户端（client_id（PK），名称）

访客（guest_id（PK）名称）

    预约（reservation_id（PK），client_id（FK），roomAss_id（FK） checkInDate）; 
    RoomAssignment（roomAss_id（PK），guest_id（FK），roomno（FK））; 
   房间（room_id（PK），roomDetails）; 
     

//这里的问题是我不知道如何实现一对多关系。我的预订应该处理多个RoomAssignment？或者我的RoomAssignment将处理多个guest_id和roomno，然后我将通过1个roomass_id到我的预订表？

谢谢我真的很困惑这个1到许多关系。我希望有人能够帮助我而不是给我负面的意见。

房间（room_id（PK）房间详细信息）; 
客户端（client_id（PK），Name）
 Guest（guest_id（PK），Name）
预留（reservation_id（PK），client_id（FK），checkInDate）; 
预订（book_id（PK），reservation_id（FK），room_id（FK））; 
住宿（lodge_id（PK），guest_id（FK），book_id（FK））
  

（客户，房间，客人已经填写），添加预订，添加预订，添加住宿

这是否正确？

解决方案

（修改更改了我的建议，这更多地考虑了这个问题，这比我想象的更加困难。）

我的偏好是使预订与房间具有多对多关系（使用桥表）。调用这个表，使用ReservationID和RoomID外键，就像。 。 。 预订。也许你可以想到一个更好的名字。预订是预订的特定房间。然后，我会有另一个桥台，代表客人和预订之间的关系。你可以叫这个<住宿> 。一个住宿是一个特定的客人被分配到一个特定的预订（一个预订的房间）。

这是一个整洁的谜题。关键是你有自己的几个具体的东西，显然是必要的（预约，房间，客人），还有几个其他的概念，每个都有自己的属性，并且从这些事情之间的关系出发。如果这样做正确，你可能会得到比你猜到的更多的表。不要认为这更复杂了。拥有足够的正确表格就能最终简化这个。以下是一些其他建议：

• 花费时间在每个表的名称。我上面的建议是非常好的，但你可以改善他们。预订是其他事情之间的关系，但成为一个想法本身，另一个表上至少有一个外键。


• 你应该可以描述一个表上的记录代表什么。如果你不能这样做，那么你的桌子很吸引人。看到上面我怎么能描述一个预订和住宿。您的设计可能会有所不同，但是当您集体讨论不同的表格时，请确保您可以描述该表格上的记录。


• 考虑让Guest和客户端来自同一个表。他们都是联系人真的。有人可能是客人，但下个月可能是客户。当联系人是客户端时，您可能需要额外的数据表（1到0或-1）。如果有人要作为访客，则系统只会要求提供基本的联系信息，但如果他们充当客户，则只需要更多的联系信息。

Primary Entities: Client Guest Reservation RoomAssignment

I want to implement a multiple room reservation database design. First, I want to explain first the concept:

1. The Client is the one who acquires a reservation.
2. The Client can only have 1 reservation at a time
3. The Client can reserve multiple rooms.

4. The Guest is the one who is assigned into a specific room.

   So for the Table:

   Client (client_id(PK), Name)
Guest (guest_id(PK), Name)

   Reservation (reservation_id(PK), client_id(FK), roomAss_id(FK), checkInDate);
   RoomAssignment (roomAss_id(PK), guest_id(FK), roomno(FK));
   Room(room_id(PK), roomDetails);
   

//The problem here is that I don't know how to implement a 1 to many relationship. My Reservation should handle mutliple RoomAssignment? Or my RoomAssignment will handle multiple guest_id and roomno and then I will pass 1 roomass_id into my reservation table?

Thanks I am really confused about this 1 to many relationship. I hope somebody is so kind to help instead of giving me negative points. T_T

Another Attempt:

Room(room_id(PK), roomDetails);
Client (client_id(PK), Name)
Guest (guest_id(PK), Name)
Reservation (reservation_id(PK), client_id(FK), checkInDate);
Booking(book_id(PK), reservation_id(FK), room_id(FK));
Lodging(lodge_id(PK), guest_id(FK), book_id(FK))

(Client, Room, Guest are already filled), add Reservation, add Booking, add Lodging

Is This Correct??

解决方案

(Edit Changed my suggestion after thinking about this more. It's a deeper puzzle than I first thought.)

My preference would be to have the Reservation having a many-to-many relationship (using a bridge table) with rooms. Call this table, with ReservationID and RoomID foreign keys, something like . . . Booking. Maybe you can think of a better name. A Booking is a particular room being reserved. Then, I'd have another bridge table representing the relationship between a Guest and a Booking. You could maybe call that a Lodging. A Lodging is a particular guest being assigned to a particular Booking (a booked room).

This is a neat puzzle you have. The key is that you have several concrete things that exist on their own and are obviously necessary (reservation, room, guest), but also several other concepts, each with their own attributes, and spring from the relationship between these things. If this is going to be normalized correctly, you might end up with more tables than you guessed. Don't think of this as getting more complicated. Having the enough of the proper tables is what's going to ultimately simplify this. Here are some other suggestions:

• Spend time on the names of each table. My suggestions above are pretty good, but you can probably improve them. A booking is both a relationship between other things, but becomes a think itself, with at least one foreign key on another table.
• You should be able to describe what a record on a table represents. If you can't do that, then your tables suck. See above how I can describe what a booking and lodging are. Your design might end up being different, but as you brainstorm different tables, make sure you can describe what a record on that table actually is.
• Consider having Guest and Client being from the same table. They're both "Contacts" really. Someone might be a Guest at one point, but a Client the next month. You might ave an extra data table (one to 0-or-1) for when a Contact is the Client. You're system would only demand basic Contact information if someone is going to just act as a Guest, but more if they're acting as a Client.

这篇关于多房间预订数据库设计：一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！