Database Schema

Diagram

id: An identification number associated with a significant location/event tracked by Shuttle Tracker
tracker_id: An identification number linking a location to a specific vehicle tracker associated with the event, whose id should be found within the "vehicles" table
latitude: The latitudinal coordinate of the event's location on the world map
longitude: The longitudinal coordinate of the event's location on the world map
heading: The compass direction (in degrees) that the vehicle associated with the event is moving towards
speed: The speed at which the vehicle associated with the event is moving
time: The date/time the event occurred at this location
route_id: An identification number linking a location to a specific route id, whose id should be found within the "routes" table
created: The date/time the location/event was recorded within Shuttle Tracker

id: An identification number associated with each message
message: The contents of the message displayed to users within Shuttle Tracker
enabled: A boolean dictating whether or not the message should be shown to users
created: The date/time the message was originally created
updated: The date/time the message was last modified

id: An identification number associated with each route schedule
route_id: An identification number linking the route schedule to a specific route, whose id should be found within the "routes" table
start_day: The day of the week the route begins, where 0 represents Sunday and 6 represents Saturday
start_time: The time (in the UTC time zone) the route begins on a day it is active
end_day: The day of the week the route ends, where 0 represents Sunday and 6 represents Saturday
end_time: The time (in the UTC time zone) the route ends on a day it is active

id: An identification number associated with each route
name: The name of the route seen by users with Shuttle Tracker
created: The date/time the route was originally created
enabled: A boolean dictating whether or not the route is visible/utilized by Shuttle Tracker
width: The width of the route line on the map shown within Shuttle Tracker
color: The color of the route line on the map, represented as a hexadecimal color code
points: An array of coordinates used to represent the route on the world map, where each entry in the array is a tuple containing the latitude,longitude of the coordinate (example: [(40.1,33.0),(40.3,33.0),(30.3,33.2)] )

id: An identification number associated with each route stop
route_id: An identification number linking the route stop to a specific route, whose id should be found within the "routes" table
stop_id: An identification number linking the route stop to a specific stop, whose id should be found within the "stops" table
order: The number stop in which a vehicle will visit on the route (first, second, et cetera)

id: An identification number associated with each shuttle stop
name: The name of the shuttle stop seen by users within Shuttle Tracker
description: A description containing more information on the shuttle stop
latitude: The latitudinal coordinate of the shuttle stop's location on the world map
longitude: The longitudinal coordinate of the shuttle stop's location on the world map
created: The date/time the shuttle stop was added to Shuttle Tracker
updated: The date/time the shuttle stop was last modified

id: An identification number associated with each admin user registered with Shuttle Tracker
username: The username used by the admin to login and access the administration panel

id: An identification number associated with each vehicle being tracked
name: The name of the vehicle seen by users within Shuttle Tracker
created: The date/time the vehicle was added to Shuttle Tracker
updated: The date/time the vehicle's attributes were last modified
enabled: A boolean dictating whether or not the vehicle should be shown/tracked by Shuttle Tracker
tracker_id: An identification number associated with the tracker installed within each vehicle