Database Schema
This page describes the database schema and all of the tables used by the system to persist Onebusaway-NYC data. The data is stored in two databases given below. This page briefly describes the purpose of each database and its tables. There is one master database for each environment. There is also a read only database which replicates the data in the master database.
Real-time, Inference, and archived data, data that is produced by the archiver, is stored in the UTC time zone. All other data is stored in the locally configured time zone.
Three distinct reporting periods exist in OneBusAway as represented in the diagram below:
This diagram distinguishes the time frames of the various archival databases:
Real-Time database stores from now to 30 days. It does so by rolling partitions; adding a new partition for each day, and dropping partitions that are more than 30 days old
Reporting database stores yesterday’s data through two years back, dropping a few unnecessary fields along the way. It is updated daily.
Archive is permanent off-line storage of database dumps on a file server.
This database is called ‘onebusaway_nyc_app’ and stores data about users, their roles and service alerts in the system. This database is used by admin web application to authenticate users in the system. It has following tables
| Table | Description |
|---|---|
| oba_users | Stores users of the system |
| oba_user_indices | Stores user type as well as user credentials |
| oba_user_roles | Stores roles created in the system |
| oba_user_roles_mapping | Stores each user’s role in the system |
| obanyc_servicealerts | Stores service alerts received by the system |
| obanyc_servicealert_subscription | Stores service alert subscriptions |
The following diagram shows ‘App’ database design
This database is called ‘onebusaway_nyc’ and stores real time as well as inferred vehicle data. It also stores depot, vehicle and crew assignment data that is uploaded by the transit agency daily. This database is used by archiver webapp to persist vehicle data and its APIs to retrieve it. It has following tables
| Table | Description |
|---|---|
| obanyc_cclocationreport | Stores real time vehicle data |
| obanyc_inferredlocation | Stores vehicle inference data |
| obanyc_last_known_vehicle | Stores last known record for each vehicle. It is a combination of real time and inferred data for that vehicle |
| obanyc_invalidlocationreport | Stores errors when persisting real time and inference records |
| obanyc_crewassignment | Stores crew assignment data uploaded by Transit Agency daily |
| obanyc_vehiclepullout | Stores vehicle pullout data uploaded by Transit Agency daily |
| obanyc_depot | Stores depot data fetched from webservice that is persisted daily |
| obanyc_systemlog | Stores messages logged while performing actions such as user login by the system |
The following diagram shows ‘real-time’ database design
The reporting database, onebusaway_nyc_reporting, consists of a single table, obanyc_reporting, that is updated daily via a dump of the last day of real-time database. As part of the dump and load process, the real-time and inference tables are combined into a single table, and some unnecessary columns are dropped. The reporting database contains data from yesterday through to two years back.
The archive database consists of MySQL dumps of the real-time database used to copy data from the real-time to the reporting database. These dump files are copied to a file server for permanent archival.
The format of the dump is that of the real-time database. The archive database contains data from yesterday through the beginning of the project.
All the tables and their columns are described in detail in Database Tables page.