Best Practices for Structuring and Managing Statewide Resource-Tracking Databases

ADOT’s Financial Management Services’ (FMS) Resource Administration (RA) team uses a Microsoft© AccessTM database (informally referred to as the “RA Database”) to track a portfolio of over $1 billion a year of federally funded projects for ADOT, local public agencies, and statewide public-private partnerships. This database is used to track current and future projects’ lifecycles, histories, funding, allocations, apportionments, obligations, etc. Multiple groups across ADOT use this database to produce mission-critical reports used to track federal aid received by councils of governments (COGs) and metropolitan planning organizations (MPOs) as well as to track and monitor federal aid sub-allocated to local governments. Each year, FMS and the Information Technology Group (ITG) work to address various problems with the RA database, such as the database becoming unresponsive, users losing access to it, and updating it to be compatible with newer versions of MS OfficeTM. With each of these issues comes the risk of data loss in a mission-critical database. ITG currently has a project in place to migrate the database to an ITG-managed SQL server as a stop-gap approach. However, in order to find a permanent solution, FMS would like to investigate how other state departments of transportation are managing their own similar databases and to identify best practices for technology, policies, and staffing. Research objectives are as follows: (1) Review the state of the practice to determine how other state DOTs track federal funding for both state and local transportation projects, whether they build and maintain their own databases, utilize external tools or software, or use other solutions. The goal of this study is to deliver a comprehensive report that details innovative approaches to financial tracking used by other state DOTs. The study should, at minimum: (2) Document federal and state regulations governing the structure and use of similar financial tracking systems. (3) Include a review of published reports and documentation to identify innovative approaches among state DOTs. (4) Engage with innovative DOTs via surveys or focus groups to gather details about how the systems in use at those DOTs were developed and are used. Are the systems homegrown or off-the-shelf? Are they user friendly? Do they interface with the department’s construction-management system, finance system, the Federal (FMIS) system, and/or any other project-tracking systems? Do they track any other funding sources beyond federal aid?