Model Database Schema for TIM Performance Measurement

The guidance has previously identified and discussed the data necessary for the calculation of TIM performance measures. In order to organize and transform these data as elements in a database that can be used to support TIM performance measurement, a database schema was developed. Click to view the model TIM performance database schema diagram. This schema incorporates data elements critical to the basic calculation of three national TIM performance measures(RCT, ICT, and secondary crashes); data elements that will allow for the calculation of other TIM performance measures that might also be on interest to an agency (incident detection time, incident verification time, and incident response time); and data elements that will allow analysts to put the performance measures into context by drilling down into the data for a finer analysis of TIM performance (e.g., what is the ICT for major freeway incidents that occur in work zones).

The model database schema is presented as an industry standard Extended (or Enhanced) Entity-Relationship (EER) diagram. EER diagrams are simply a way to model the data and the relationships between the different data elements using standard symbols. While a schema is critical for the construction aspect of the database, it has little use for the end-user. In fact, schema diagrams can be difficult to understand as they can express complex attributes of the data.

At a high level, the model TIM performance measurement database schema revolves around an INCIDENT.  For each INCIDENT, there are relationships to the other data elements in the database model.  For example, there are data elements associated with CONDITIONS, SEVERITY TYPE, and INCIDENT TYPE, and these data elements help describe an incident.  There are also data elements associated with the four “actors” in an INCIDENT, and these actors have their own tables in the schema:  the ROADWAY, the PARTICIPANTS, the VEHICLES, and the RESPONDENTS.  These tables contain data elements that describe the actors (e.g., name of the roadway, number of participants, whether a heavy vehicle was involved).  In some cases, these tables are linked to extended tables, which contain additional data elements that provide more details about an incident.

All of the data elements contained in the schema are listed below (click the headings to expand the lists).  In parentheses following each data element is the actual name assigned to the data element inside the database (as shown in the schema diagram).

Incident Timeline, Conditions and Severity
  • Date of incident (incident_date)
  • Description of incident (description)
  • Time of first recordable awareness of an incident by a responsible agency  (incident_first_recordable_awareness)
  • Time incident verified (incident_verified)
  • Time first response dispatched (first_response_dispatched)
  • Time first response arrived (first_response_arrived)
  • Time of first confirmation that all are lanes available for traffic flow (all_lanes_opened)
  • Time at which last responder has left incident scene (last_respondent_departed)
  • Time normal flow returns (normal_flow_returned)
  • Occurrence of secondary crash(es) (parent_incident_id)
  • Incident type (incident_type_id)
  • Incident severity (severity_type)
  • Weather conditions (weather_type)
  • Lighting conditions (lighting_type)

 

Roadway and Location of Incident
  • Roadway name (name)
  • Roadway type (roadway_type)
  • Roadway direction (direction)
  • Roadway location (locationvalue)
  • Incident location reference type (location_type)
  • Surface condition (surface_condition)
  • Work zone (workzone)

 

Lanes Involved in Incident
  • Lanes involved (lanes_involved)
  • Total roadway lanes at scene (total_lanes_at_scene)
  • Closing and opening of each lane involved (lane_number, time_closed, time_open)

 

Vehicles Involved in the Incident
  • Number of vehicles involved (number_vehicles)
  • Heavy vehicle (heavy_vehicle_involved)
  • Hazmat vehicle (hazmat_involved)

 

Participants Involved in Incident
  • Number of participants involved (participants_count, id)
  • Number of injuries and injury type (pdo, serious_injuries, minor_injuries, fatalities)
  • Participant types (participant_type)
  • Injury incident (injury)

 

Emergency Responders and Vehicles
  • Responders involved (number_people, id, time_on_scene, time_off_scene)
  • Response vehicle type, arrival, and departure (number_vehicles, type, time_on_scene, time_off_scene)
  • Response organization (organization_type)