Advice on table structure please!

[johnbirt]

I have a table which is part of a database for a specialist self-catering apartments booking system. The apartments have a fairly flexible occupancy criteria for adults and children and a correspondingly fairly complex pricing system. That is all do-able _ I think! The advice I need is on the structure of the table.

The table is called BookingDetails which currently is structured as follows.

PK BookingDetailsID
PK BookingID links to main Bookings table
PK FK RoomID links to Rooms table
?? GuestID links to Guests table holding guest names & addresses
.. NumAdults the number of adults to accomodated in that apartment
.. NumChildren the number of children to be accomodated

The GuestID should ideally be included as a 4th component for a PKey. However the booking enquiry will usually be of the form "I would like to accomodate the following adults/children... and the owner will then suggest the most suitable apartment(s). Otherwise the enquiry/booking is for a particular number of adults and children in a selection of apartments.

In other words the guests for a room are not defined at initial inquiry or booking and are filled in later. Consequently the GuestID's are initially necessarily NULL so can't be a PKey.

I am aware that the Numbers of Adults and Children will be repeated data and should probably be held seperately in another table but probably not essential to do so.

What is the best way to handle the GuestID field? I thought maybe initialise each booking details with a 'dummy guest' record. A bit messy to make sure that this isn't overwritten later when filling in the real data.

Any suggestions on how to deal efficiently and neatly with the fact that there must be a guest in each room eventually if not initially gratefully received.

The client who is booking/enquiring may not actually ultimately be a guest as they may be booking on behalf of others. They are simply the person to be billed, who may not or may not be present in one of the rooms and certainly not in all those booked!

Thanks.

John B

 

[MarkK]

Are you wanting to use the data from foreign keys as part of your primary key in BookingDetail? I wouldn't do that. It doesn't matter if there is a guest record or not, or if you must have one, create a blank one.

 

[johnbirt]

All booking information seems naturally to be fully described by (BookingID,RoomID,GuestID) and that trio together needs to form a unique trio of information.
A query on all three components and related data essentially provides all the data available so not sure why stipulating the above as primary keys is bad?
(BookingDetailsID, BookingID,RoomID,GuestID) with BookingDetailsID as PKey is not the same as insisting the 4 together form a Pkey. The latter three form a unique entity. Possibly the BookingDetailsID is probably not needed on reflection if I go with the 3 joined PK fields. On the other hand a guest cannot be in 2 rooms at once in the same booking or indeed a room cannot appear more than once for the same booking.
As you say a dummy blank record can be used. I just wondered whether there was an alternative way of dealing with the situation.
I will probably go as you suggest without stipulating PKeys other than BookingDetailsID and build in checking or filtering out of silly inputs.
Thanks for your input.

 

[MarkK]

What you can do is use BookingDetailID as an autonumber primary key, which is sufficient to uniquely identify the record, and your PK is handled.

Then, put a unique index on the three foreign key fields, BookingID, GuestID, RoomID, and the table will reject any duplication, which is your other concern, correct?

 

[johnbirt]

Indeed I eventually came to that conclusion and will adopt the 3 unique index foelds approach as you suggest which seems to be the simplest solution.
Thanks.

John B