Moddeling problem

[mrcamo]

Hi Im trying to model the following scenario:

- 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?

 

[danvlas]

Additionally you will need the following (minimal) structures. Try to read about many-to-many relationships.

Coach table:
CoachID
PlateNumber
NumberOfSeats

CoachService(bridge table, many to many between Excursion and Coaches)
CoachServiceID
StaffID
CoachID
ExcursionID
(you should have a unique index on CoachID and ExcursionID)

Tickets:
TicketID
CoachServiceID
CustomerID

You should have a unique index on CoachServiceID and CustomerID.




Daniel Vlas
Systems Consultant

 

[mrcamo]

Thanks.. but the point is that everyday, x amount of coaches will be booked from the local depot, ... these could have a different registration plate ever day, so how feasibile is it holding a coach liscence plate number if there may only ever be one coach wil that liscence plate used on one day and then never seen again.

I was thinking more of modfelling in some way where i could have an internally controllable service number, that the liscence of a coach doesn't depend on

I need the ability to track customers on coaches, by the internally allocated service number...