Is the Route the important thing or the bus. A bus could be assigned to another route.
Wouldn't you want Day (Wed, Fri) - charting bar graph would be clearer.
Season (spring,fall), Weather (rain, sun), what questions are the database supposed to answer? What responses might be taken?
You are correct, the route is the important factor...although this field is harder to define...it could be a text list of stops, or a map of the route. Also, the mobile device will be associted with the driver, so I think I will need a field for that as well (since different drivers could be assigned to different routes or busses. The purpose of the database is to track individual student ridership each day; TO monitor whether a student was on the bus in the morning or the afternoon.
A table with a record for each route linked to a related table with a record for each stop might be useful. This way, you can update a route by adding/removing/changing records in the stops table whenever you need to update the route.
Buses----<Routes----<Stops (--< means "one to many" )
Buses::__pk_BusID = Routes::_fk_BusID
Routes::__pk_RouteID = Stops::_fk_RouteID
and a portal to Stops on your Routes layout would be very useful for viewing and modifying the stops for a given route.
True...I've updated some of my bus info to reflect this...however, the most critical factor for my supervisor is the student...whether they got on or off on a given day, which I'm not sure how to configure so I can set up a layout that allows the selection of specific dates. I think the specific bus flexibility might be asked for later, so I'm glad to put some thought in now. So...how do I relate the tables so I can track the student ride data?
True, but the student will be linked at the very least to a route and could even be linked to a specific stop.
Your ridership table is a start in the right direction, but you need two different ways to link students to a given route. A "roster" of who is assigned to a given route and an attendance record of who actually got on and off the bus for that route on a given date/time.
In terms of the "roster" can the same exact list of students be used for both Am and Pm in every case or is it possible for there to be an exception where a student rides the bus one of those two times and not the other? Say a student rides to school with parent, but then rides bus home...
Hey Gary, I don't know if you already found a solution for your problem or not, and this is maybe not completely tailor made for your particular situation, but I made a little demo file for attendance tracking:
If you are interested you could have a look.
Thanks...this is really helping with the tables...