Introduction to the Model Database

Performance measurement is the use of statistical evidence to determine progress towards specific, defined organizational objectives. The systematic, ongoing performance measurement process involves collecting and analyzing data to determine if these organizational objectives have been met and then reporting the findings internally, as well as to stakeholders and customers. To this point, the guidance has focused on the benefits and challenges of TIM performance measurement, the three national TIM performance measures, the importance and benefits of using incident data to put the performance measures into context as a way of further understanding performance, and sources of TIM performance measurement data.

As may have has become apparent throughout this guidance, the analysis of TIM performance can require a lot of incident-related data, which may come from various data sources. This section of the guidance provides further direction on the organization, use, and analysis of these data using a relational database. A relational database is a structured set of data that recognizes relationships between data elements, or individual items of information. Internal to the database, these data elements are arranged in tables. One table may contain information about the vehicles involved in an incident, while another table may contain information about the weather conditions at the time of the incident. The relationship between these tables and the elements they contain provides unique capabilities for searching, aggregating, and reporting on vast amounts of incidents. This is the overall goal of TIM incident management reporting—understanding how the TIM program is operating and being able to see the trends in the data.

The creation and operation of a database used to be a task best left to highly skilled information technology professionals. Database development and usage was time consuming, challenging, and expensive for the average agency. Over the past several years, however, substantial progress has been made on all these fronts. Databases do not have to be expensive. In fact, many are free and open-source, which has allowed many agencies to gain significant insight into their data by creating and populating databases.

It is the conviction of this guidance that having TIM data organized in a standard way in a relational database will drive the collection of TIM performance data; provide a centralized repository for the data; and facilitate the analysis, reporting, and comparison of TIM performance data not only internal to an agency, but across agencies and regions. As many agencies have begun only recently to collect and analyze TIM performance data, it is timely and opportune to provide guidance on the implementation and use of a TIM performance measurement database for data analysis and reporting. The ultimate goal of developing a TIM performance measures database is to provide a mechanism for analyzing TIM program performance to demonstrate transparency, provide program justification, and determine future improvements and strategies.

This section of the guidance provides a framework for developing a TIM performance measurement relational database. This framework is intended to be downloaded, implemented, and used by agencies as a starting point for their TIM performance measurement database or as supplemental and supporting information to an existing TIM database.

The framework includes the following:

  • A list of data elements pertaining to TIM performance measurement.
  • A database schema that logically groups individual data elements into related areas.
  • A database script that can be used to generate a database using the schema presented in this guidance.
  • A database script that can be used to populate the database look-up tables with values (e.g., all of the roadways in an area where TIM activities are tracked).

After presenting the data elements and the database schema and discussing how to create the database and populate the look-up tables, options for populating the database with incident data are presented and discussed.

Finally, applications of the database are illustrated through example organizational objectives and associated database scripts that might be of interest to agencies. These applications include a presentation of the database outputs, along with more appealing visualizations of the data using various graphics.

The products of this guidance are intended to be used by an agency to download and apply to a database software package to quickly generate its own TIM performance measurement database. In order to accomplish this objective, the database, scripts, data, and illustrations from this model TIM performance measurement database were built and tested on a live product. MySQL was chosen as the development platform due to its open-source background, cost to agencies, and ability to integrate with numerous other products in the database marketplace. An accompanying tool, Navicat, was used to create the schema and produce the visual display of the relationships between the data elements. The database scripts were developed using Structured Query Language (SQL), a standard database language for managing data in a relational database. It should be noted that agencies could choose to take these concepts and apply them to their own database software, although some slight modifications of the scripts would likely be necessary.