Database Schema
This page describes database schema and all the tables used by the system to persist OBANYC data. The data is stored in two databases given below. This page briefly describes the purpose of each database and the tables. There is one master database for each environment. There is also a read only database which replicates the data in the master database.
This database is named ‘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 named ‘onebusaway_nyc’ and stores vehicle data and the data uploaded by MTA daily such as UTS and SPEAR data. This databases is used by archiver webapp APIs to return vehicle data. 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 UTS crew assignment data uploaded by MTA that is persisted daily |
| obanyc_vehiclepullout | Stores UTS vehicle pullout data uploaded by MTA that is persisted daily |
| obanyc_depot | Stores depot data fetched from SPEAR webservice that is persisted daily |
| obanyc_systemlog | Stores messages logged while performing actions such as user login by the system |
All the tables and their columns are described in detail here.