Results 1 to 5 of 5

Thread: Please Help

  1. #1
    Join Date
    Nov 2015
    Posts
    23

    Unanswered: Each Driver must have one Bus.

    I have 4 Tables
    tblDrivers
    tblBuses
    tblSchools
    tblSchoolTrips

    I have database for a company Where there are 150 Drivers and Buses and 31 Schools.. Some Drivers have only one Trip in a School while Some have two
    Trips in Two different schools with the same Bus they have in the first Trip..
    I want only one Driver must have one Bus.. One Bus should not be Driven by more than one Driver and vise versa..
    No Problem if each Driver has the same bus in Two different Schools..
    I am attaching a Screenshot of my tables.. Please Help!!Click image for larger version. 

Name:	Screenshot.jpg 
Views:	5 
Size:	121.5 KB 
ID:	16665
    Last edited by alipezu; 12-14-15 at 06:24.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And what, exactly, is your question?

    What have you tried?

    We're here to help and advise...but we don't design entire databases.

    Welcome to dBforums!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Well one tip Id be happy to suggest is ALWAYS, ALWAYS enforce relational integrity when defining relationships, otherwise there seems little point in using a relational database

    you can always enforce single use through constraints through effective design / use of primary / unique indexes.


    ferinstance defining the primary key of an order item as being order number and product n umber automatically enforces a rule that you can only ever include one line detail for a specific product in a specific order (that doesn't mean you can only order one product... you could have a quantity ordered to indicate how many instances of each product have been ordered

    as to the rest of your post, sorry this has more than the whiff of school/uni/homework, which I won't do. On top of which there doesn't seem to be an actual design problem question
    Last edited by healdem; 12-14-15 at 09:32.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2015
    Posts
    23
    My question is simple
    Every Driver must not have more than one bus..
    For Example: Steve has a bus number (A2233) in a (School12) So David can not take the bus number (A2233) because it has already been taken by Steve..

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have an intersection table that identifies what driver is allocated to which bus. the pk of that intersectiont able will (probably) be bus & driver.
    you'd probably also need to define unique indexes in that intersection table such that you can only ever have one row with a bus in and one row with a driver in it. the requirments is pants.... how do you model the situation where, say a bus breakdown and has to be replaced, or a driver breaks down and has to be replaced or say a driver is re allocated to do a different route either on the same day or subsequent days. how do you handle a planned replacment bus (eg whilst its in for service or an end of life replacement).

    Im guessing what your assignment requires is some form of template that identifies a route and as part of that there is a classification of what type of vehicle can be assigned (eg capacity 999 seats, a x b c dimensions, GVW etc) and from that you can allocate a vehicle to a route for a specific day. and be able to offer repalcement vehicle should the usual one fail. I'd also expect some template that woudl identify what charactersitics (eg licence qualifications) a driver would need for that vehicle and for the driver something that identifies what qualifications they have (eg licence categories, criminal records checks etc..., what sort of age groupo they can or cannot transport
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •