- a company organises excursions
- for each excursion a member of staff books the required number of coaches for the number of customers whom have booked on that day to take them on the excursion
-multiple excursions run in parralell to different places
-customers recieve a ticket for the coach with the number of the coach they should get on (all coaches leave from the same spot
- need to keep a record of which customer is on which bus
I need to record details of which coach is going on which excursion ( considering the physical coaches may never be the same but the survice number that the company assigns to the coaches needs to reflect the excursion it is going on (and so does thre passengers ticket), also for one particular excursion it may require 3 coaches to carry 100 passengers booked for that day, where as another excursion running on that day may only require 2 coaches as only 60 poeople have booked.
Currently I have the following tables:
Excursion(ExcursionID, Description, Date, Max_guests)
Staff (StaffID, Name etc..)
Customer(CustomerID, Name etc...)
Im really struggeling how to model the coach allocation to excusrisons...
Origionally I was thinking that I could have an entity for coach one for excursion and then an entity between coach and excursion to show which coaches are going on which excursions (considering multiple excursions run on one day which could have multiple coache for each one)
But then im struggeling on thinking .. its all well and good saying that coachservice number 123 is going on excursion ID 001 if the coach service numbers remain constant for each excursion type (but then why not just keep an attribute of coach service number in the excursion table ---- but then how do I show which member of staff booked how many coaches for that ? im really baffled
how can i track which customers are on which coaches?
Does this make sense? can anyone help me?