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 three databases given below, and also offline on a file server. This page briefly describes the purpose of each database and its tables. Typically there is one master database for each environment (production, staging, developement). There is optionally a read only database in each environment which replicates in real-time the data in the master database, provided for querying purposes without affecting write performance of the master.
Also note that queue messaging is used to exchange real-time data for this application; the tables below represent ancillary purposes such as authentication, service alerts, and data archiving.
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.
Reporting database stores yesterday’s data through two years back, dropping a few unnecessary fields along the way. It is updated daily via cron job.
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 for:
- authenticating user to the admin web application;
- authenticate API key requests to the front-end;
- store active service alerts and service alerts subscriptions managed by the TDM;
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.
The obanyc_cclocationreport and obanyc_inferredlocation_record tables partition by day on the time_received and time_reported columns respectively. In this manner, partitions older than 30 days can be dropped by an external cron job without taking the database off-line.
The real-time database 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 the 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. Like the real-time database, each day is a partition use the time_received column; the partitions of data older than 2 years are simply dropped by an external cron job called daily.
The following diagram shows the ‘reporting’ database design
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 with the following filename convention:
obanyc_reporting.YYYY-MM-DD.sql.gz
All the tables and their columns are described in detail in Database Tables page.