Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Moddeling problem

Moddeling problem

Moddeling problem

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?


RE: Moddeling problem

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

Coach table:

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


You should have a unique index on CoachServiceID and CustomerID.


Daniel Vlas
Systems Consultant


RE: Moddeling problem

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close