Suitability of relationships (1 Viewer)

I am constructing a database for a travel agent and was wondering if anyone can advise the suitability of this relationship structure.

The narrative is;

• Customer requests quote for travel package
• Customer is created
• Booking is constructed assigning a Customer
• Guests are assigned to booking
• Products are attached to Guests

Attached is the relationship table.

I can't help wondering that this structure is too complicated and this is causing problems when I try to edit transactions (I have already commenced developing and testing).

Hard to say withou knowing anything about your business, but inuitively, the daisy chain required to find out which product was bought by which customer is waaay too long.

WHat is the relation (IRL) between customer and guest(s) ? pls explain
Your Guesttable seems to contain products _ ski lift/hire/accommodation?
I'd have thought a customer books 4 beds, one pool, 2 sets of skis, 4 ski lift cards... is it necessary to know which guest booked what? (except airlinetickets perhaps). So long as the time period is equal for each.

BTW: tables are mostly plurals of what they contain, not singulars - that's kind of logical if you think about it.

Guest table does not contain products. They are check boxes which launch code that inserts products into BookingProduct table.

Customer is really the heading for a group of guests. Each one of those guests require products. I am tracking them by guest because as you have identified, the time period for each guest's stay may vary.
Note that a customer one year may accompany guests Mary and John say but the following year accompany completely different companions. Hence the one to many relationship between Customer and Booking. Another point - the customer will also be one of the guests.

Hope this is clear.

Yes, the table headings could all be in the plural tense.

thanks

I think you should create some test data and scenarios based on your business facts/rules, and test them against the model to see if it supports your needs.
I think you may get some ideas from this model re different bookings,

Your BookingId can for a guest be retrieved from different places -i.e. different paths to the same information (in different locations). You should rethink that part.

You say a customer will be one of the guests - if that is the case then you are going to be entering a customer twice, one as a customer and again as a guest

But what about the scenario where the customer is not a guest, for example a company books some rooms for their staff?

I think your 'primary record' needs to be a booking

> A booking has guests - one of which will be the primary contact (who may also be the customer)

> and guests have products, occupation dates etc

spikepl said:

Your BookingId can for a guest be retrieved from different places -i.e. different paths to the same information (in different locations). You should rethink that part.

Click to expand...

I was wondering about that as well. Deleting the BookingID field in the BookingProduct table I would think is the solution - do you agree?

CJ_London said:

You say a customer will be one of the guests - if that is the case then you are going to be entering a customer twice, one as a customer and again as a guest

But what about the scenario where the customer is not a guest, for example a company books some rooms for their staff?
I think your 'primary record' needs to be a booking

Click to expand...

The Customer is the entity that is being transacted with - who is making the booking on behalf of the Guests.

The Customer may be a person or an organisation - such as a company.

The Customer is the contact and the entity that will pay the account.

A Customer, over time, might have more than one booking and the user might want to track sales history of this Customer. Hence the one to many between Customer and Booking.

I am appreciative of the feedback.
I know I am not experienced and am not asking for someone to write this for me but little points like you have mentioned help me build my knowledge.

Thanks