Design advise - many to many?

[Gavx]

I am creating a database for a travel agent.
In a nutshell it looks like this

tblBookings
BookingID PK
BookingName etc

tblGuests
GuestID PK
BookingID FK

tblProducts
ProductID PK
ProductName etc

tblGuestProducts
ProductID PK
GuestID PK
ProductType etc
This is the joining table

Some of the ProductTypes are
1. Accommodation
2. Transport
3. Various activities
In an attempt to Normalize the data I am putting disparate data in other tables. For example Accommodation requires a check in and check out field whereas Transport only requires a date. Some activities don’t even require a date.
So my approach is to have more tables, say
tblAccomodation
AccomodationID PK
ProductID FK

tblTransfer
TransportID PK
ProductID FK

1. Does this structure seem sound?
2. Given that a booking will often stay in more than one accommodation there would be a many to many relationship with tblBooking and tblAccommodation – but would I need a joining table given this structure?

 

[CJ_London]

I've done a few for this type of organisation - you really have not provided enough detail for someone to agree or disagree it is sound.

You will need start/end dates for booking, some means of grouping together parties with a party contact. You'll probably need some sort of history tracking for changes to the booking.

re your many to many question - if a guest can book many accommodations and an accommodation can have many guests - you have a many to many relationship, and these can only be managed through a joining table

 

[Gavx]

Some narrative;

Customer makes booking for himself and his friends (Guests of the Booking).

Within the Booking, products will be allocated to each guest, which are consolidated onto one Booking ID for which an invoice will be raised - for the Booking; for example but not limited to;

1. Meet and greet at airport
2. Apartment or property
3. Bus tickets
4. Ski lift tickets

Each of these different products has attributes that are not shared with other products. For example an Apartment has a room configuration (3 bedrooms), check in/out details whereas the Bus tickets will have a origin and destination as well as a service date.

A bus ticket might be included on the Booking Invoice which will be somewhat generic, say "Bus from A to B". At a much later date this guest (member of the booking) will be assigned a particular bus, say the 10:40.

[B]tblBookings [/B]---------------<[B]tblGuests[/B]
BookingID                     PKGuestID  PK
BookingName                BookingID  FK
One   to    

[B]tblGuests[/B]------<[B]tblGuestProducts[/B]>----------[B]tblProducts[/B]
GuestD PK        GuestProductID PK            ProductID  PK
One   to    Many   to   One


[B]tblGuestProducts[/B]-----------[B]tblAccomodationAttibute[/B]
GuestProductID  PK          AccommodationID   PK
                                       GuestProductID  FK
One    to   One


[B]tblGuestProducts[/B]-----------[B]tblBusTicketAttibute[/B]
GuestProductID  PK           BusTicketID   PK
                                  GuestProductID  FK
One    to   One

Am I on the right track? I am concerned about the one to one relationship between the joining table tblGuestProduct and the tblAccommodationAttribute or tblBusTicketAttribute

thanks for any advice.

 

[gemma-the-husky]

you also discussed this in your other thread.

personally, I would start from the booking:


booking (Autonumber ID)

guests (names and addresses linked to the booking)

products (products - eg excursions - linked to the booking)


so where it gets tricky is deciding what links you need. eg. say the booking is for 4 people, 2 adults and 2 children) - and they book a 2 person excursion - it depends whether you just need to know it is a 2-person excursion - or whether you need to specifically identify the guests taking the excursion.

assuming this is not just an academic exercise, then you probably need a more detailed examination of the system to decide on such requirements.

hope this helps


[edit - I don't see why the number of rooms in an apartment is significant. It might be a bit of information about a particular accommodation. As far as the booking is concerned surely the accommodation just needs to know things like.

a) maximum number of occupants eg 6
b) is baby accepted in addition eg yes/no, or maybe a number.
c) single sex parties permitted eg yes/no


I doubt if you would try to allocate guests to an apartment on the booking information]

 

[Gavx]

you also discussed this in your other thread.

personally, I would start from the booking:


booking (Autonumber ID)

guests (names and addresses linked to the booking)

products (products - eg excursions - linked to the booking)]

This is exactly what I have done. All booking details are in tblBooking. tblGuests holds all guest details. These 2 tables are linked in a one to many relationship.

so where it gets tricky is deciding what links you need. eg. say the booking is for 4 people, 2 adults and 2 children) - and they book a 2 person excursion - it depends whether you just need to know it is a 2-person excursion - or whether you need to specifically identify the guests taking the excursion.

Need to know who is taking the excursion. In fact all of the excursions or for that matter accommodations or bus trip generate a response or task from the Travel Agent that must be actioned. This is one of the purposes of the db - to generate a task list in response to the products that have been purchased.

assuming this is not just an academic exercise, then you probably need a more detailed examination of the system to decide on such requirements.

hope this helps

[edit - I don't see why the number of rooms in an apartment is significant. It might be a bit of information about a particular accommodation. As far as the booking is concerned surely the accommodation just needs to know things like.

a) maximum number of occupants eg 6
b) is baby accepted in addition eg yes/no, or maybe a number.
c) single sex parties permitted eg yes/no

Number of rooms will help determine suitability of the apartment for the number of guests and also provide confirmation of what the customer is buying.

I doubt if you would try to allocate guests to an apartment on the booking information

Quite possibly, but it might be a case that as all other Products are being allocated to a Guest why not Accommodation.

 

[gemma-the-husky]

re my last.

I meant in my last comment, that I didn't think you would allocate guests to a room within an apartment. of course you would allocate them to an apartment.

ADD ONS

if addons need to be allocated to guests, then the best way would be this, I think.

tables

booking(BookingID (PK), other stuff)
addon(AddonID(PK), other stuff)
guest(GuestID (PK), bookingid (FK) other stuff)

combined table
extra (ExtraID (PK) AddonID (FK), BookingID (FK) GuestID (FK), other stuff) or maybe

separate tables
extra-guest (ExtraID (PK) AddonID (FK), GuestID (FK), other stuff) or maybe
extra-general (ExtraID (PK) AddonID (FK), BookingID (FK), other stuff)


note that in the case of the combined table, it may be worth adding a dummy value for "all guests", as you will get problems asserting a unique index where one component is null.

(ie - you would be able to duplicate the same extra (ie addon and booking) if the guestid was null.)

So instead, you may decide to have 2 tables - one for booking related addons, and one for guest related addons.

an example of a booking related addon might be an extended departure time for a given room, or a supplement for Wi-Fi in the room.

hope this is clear enough to understand

 

[Gavx]

if addons need to be allocated to guests, then the best way would be this, I think.

tables

booking(BookingID (PK), other stuff)
addon(AddonID(PK), other stuff)
guest(GuestID (PK), bookingid (FK) other stuff)

combined table
extra (ExtraID (PK) AddonID (FK), BookingID (FK) GuestID (FK), other stuff) or maybe

separate tables
extra-guest (ExtraID (PK) AddonID (FK), GuestID (FK), other stuff) or maybe
extra-general (ExtraID (PK) AddonID (FK), BookingID (FK), other stuff)


note that in the case of the combined table, it may be worth adding a dummy value for "all guests", as you will get problems asserting a unique index where one component is null.

(ie - you would be able to duplicate the same extra (ie addon and booking) if the guestid was null.)

So instead, you may decide to have 2 tables - one for booking related addons, and one for guest related addons.

hope this is clear enough to understand

Afraid not.
Understand the booking and guest tables.
Do not understand the Addon table. In my scenario I have a Products table - and each record is allocated to a guest. As this is a many to many relationship I have a joining table between tblGuests and tblProducts called tblGuestProducts.
Because the guest table has a relationship with the Booking table I anticipate being able to report either Products per Guest or Products per Booking.

Each Product type has a different set of attributes. For example the type "Accomodation" has a check in and out fields - as well as some other details. Product type "Transport" has attributes such as Service date and Destination from and to.

To facilitate this I would create tables called say tblAccommodation and tblTransport which would have a one to one relationship with the joining table tblGuestProducts.

Is your Addon table = to my tblProducts? And if so where is the joining table?
Is your extra-general table = to my tblAccommodation and tblTransport tables?

In my scenario my concern is the one to one relationship between tblGuestProduct and my attribute table tblAccommodation and tblTransport.
When is a one to one relationship justified?

thanks