Need Feedback

[prabha_friend]

 

[XPS35]

We do not know your business (rules), so it is hard to say anything about this.
What strikes me is that tickets are linked to a certain bus and a certain driver. I would expect a passenger to buy a ticket to get from A to B. Your planning is something different I think.
Also, it seems that A and B should be on the same route. Can't passengers transfer to another route? In your model, a stop can in any case only be on 1 route. That makes switching to another route indeed impossible.

 

[XPS35]

BTW I also miss dates in the model. For example, about the validity of tickets and in the schedule (different drivers on a route on different dates?).

 

[prabha_friend]

BTW I also miss dates in the model. For example, about the validity of tickets and in the schedule (different drivers on a route on different dates?).

TimeVal in Schedule Table

 

[prabha_friend]

 

[prabha_friend]

Revised

 

[Pat Hartman]

It is not "cool" to name all your autonumberd "ID" All that does is to make the schema harder to see without a picture. StopID is a good PK and the matching FK is also named StopID to make the pairing obvious.

Do you have to support transfers, Ie. Take the A bus to Main St. Then switch to the B bus? Or would this entail purchasing two tickets?

 

[GinaWhipp]

Hmm, I would not use *Number* as a field name as it is a reserved word for Access and will cause you issues. For a complete list of reserved words see the below links.
http://allenbrowne.com/AppIssueBadWord.html
http://www.utteraccess.com/forum/Access-Reserved-Words-t539419.html

I also would combine tables Driver and Agent and to perhaps Entity and use a field to identify what type of Entity, i.e. Driver or Agent or...

I feel like there should be a junction table tblRouteSchedules. As I would image schedules are the same like routes. Just seems like schedules would be duplicated along with routes but maybe I am missing something/

Not sure what Coach is supposed to represent. Perhaps you could elaborate.

 

[prabha_friend]

Hmm, I would not use *Number* as a field name as it is a reserved word for Access and will cause you issues. For a complete list of reserved words see the below links.
http://allenbrowne.com/AppIssueBadWord.html
http://www.utteraccess.com/forum/Access-Reserved-Words-t539419.html

I also would combine tables Driver and Agent and to perhaps Entity and use a field to identify what type of Entity, i.e. Driver or Agent or...

I feel like there should be a junction table tblRouteSchedules. As I would image schedules are the same like routes. Just seems like schedules would be duplicated along with routes but maybe I am missing something/

Not sure what Coach is supposed to represent. Perhaps you could elaborate.

 

[GinaWhipp]

Oh, okay that makes sense, thanks for clearing that up.

 

[prabha_friend]

We do not know your business (rules), so it is hard to say anything about this.
What strikes me is that tickets are linked to a certain bus and a certain driver. I would expect a passenger to buy a ticket to get from A to B. Your planning is something different I think.
Also, it seems that A and B should be on the same route. Can't passengers transfer to another route? In your model, a stop can in any case only be on 1 route. That makes switching to another route indeed impossible.

Planning for "Day Pass" concept if required...