Designing, implementing, and maintaining a database that will work for your program.

By Dirk S.G. Brown

Automation has become a dominant theme of the new millennium. The promise of technology is that operations will become more cost-efficient and cost-effective through the use of information management systems. These assumptions are based on the notion that the delivery of customer service is best enhanced through the storage, manipulation, and retrieval of data elements in an automated format.

The purpose of this article is to explore the importance of defining data elements, data tables, and databases in the stormwater context. More specifically, it outlines the issues associated with the design, implementation, and maintenance of a stormwater utility database.

Suppose a community has decided to create a stormwater utility. Perhaps it was driven by National Pollutant Discharge Elimination System (NPDES) Phase II permitting requirements. Whether it is formed to generate funding to resolve flooding complaints or water-quality issues or both, the decision to establish a utility is a foregone conclusion. Having surmounted the conceptual obstacle, the planning team is flush with confidence. Now it is time to tend to the details. But, alas, some smart aleck in the back row reminds the team of the adage, "The devil is in the details."

Providing some details that can be assessed for suitability is the objective of this article. In times of belt-tightening, no government organization wants to be perceived as wasting public tax dollars and fees simply to reinvent the wheel. It is crucial to be efficient when using limited resources. The question is: Where to begin?

Competing interests demand attention, and many questions arise simultaneously. What type of database should the utility use? How should the data be distributed? What security protocol ought to be implemented? Wherever you turn, there is another vendor proposing to save you money if only you let the vendor manage the process of shaping your requirements. What should a planner do? Let me share some lessons learned, and perhaps someone will be better off for it in the end.

Clearly there are many approaches to data collection and storage. For stormwater management, concerns arise about best management practices (Clary et al., 2001; Urban Water Resources Research Council, 2002). To attempt to address all of these areas is beyond the scope of this article. Therefore, I propose to confine this discussion to the data tables that the City of Columbus utility has found important for the purpose of legitimacy, maintenance, and customer-service delivery. In this way, perhaps NPDES Phase II applicants can avoid some of the pitfalls we've encountered on the journey.

Keeping track of various data elements of a stormwater utility is essential to ensure efficient and cost-effective deployment of resources. The development, implementation, and administration of a utility requires dedicated and thoughtful attention to the creation of a database that incorporates the varied, sometimes complex, and always revealing status of the utility's mission.

By definition, developing a stormwater utility requires dedicating resources to specific program activities. Some articles have emphasized particular aspects of stormwater utility development and administration (Brown, 1997; 2002). This article addresses various theoretical concerns and does not answer which database format is the most appropriate for a particular circumstance.

Stormwater management, organizationally speaking, poses no greater demands than the delivery of any other public service. As with any organization, private or public, services are to be provided in the most rational manner possible. The greatest number and most reliable benefits should be delivered at the lowest costs in an exceptionally timely fashion. Before the advent of the computer, documentation of various aspects of an organization was tracked on paper, subject to degradation and deterioration over time. Computer tracking of a program is subject to similar concerns of misplacement, destruction, and corruption. But the computer has introduced elements of cross-referencing and analysis of documents at efficiency levels that are impossible to ignore.

Developing stormwater utility demands a rational method for tracking revenues, expenses, complaints, capital improvement projects, plan reviews, sediment and erosion control, water-quantity and -quality monitoring, and myriad other known and unknown aspects. The collection, storage, and retrieval of data can be a means of tracking project progress or meeting the reporting requirements of another public agency‹federal or state.

Conceptual Data Issues

What data should one track? The basics would suggest just enough to generate an accurate bill. One side of the coin is to generate a so-called master account file that allows the billing system to generate accounts receivable, post receipts to a ledger system, and allow for collection. This raises the question of data that are system-specific. Can you find a billing system that will allow for the entry of impervious area measurements, or do you have to translate that number into a billing unit, such as equivalent residential units, that facilitates the generation of an account receivable? Does the utility have a credit program that requires more involved algorithms to incorporate a dollar or percentage discount into the billing system? (The question of the appropriate billing system for supporting a stormwater utility is an entirely different subject, one that must also be considered.)

All we are concerned with here is identifying the necessary data that capture important elements of ongoing stormwater utility administration. The master account file provides a starting point; contained within it is the impervious area that was measured via a geographic information system (GIS) (see Brown, 1997, for a discussion of the use of a GIS to develop a stormwater utility). But the account file reflects only existing development at a point in time. The database must track more information. Beyond that, I submit that there are six key maintenance activities that need to be tracked: new construction, redevelopments, additions, demolitions, annexations, and detachments.

By tracking these activities, the utility is in the best position to track growth and minimize rate increases through revenue base expansion. Columbus's city code requires developers to apply for building permits. Accompanying the application is a paper copy of the proposed site plan. The Division of Sewerage and Drainage (DOSD) has gone further and requires digital copies of plans that require a sewer permit. Thus, key data elements are unique plan identifiers that can be cross-referenced by different agencies for varied purposes. From the Development Department comes a 15-digit permit number, and from DOSD comes a five-digit number known as a CC plan number. The hard-copy plan that is forwarded by the Development Department also might include the CC plan number, facilitating the use of electronic copies of maps that streamline the process of digitizing new impervious area. Not all of these plans come with the CC plan number, however, because the CC plans relate to the installation of new storm sewers, and some plans relate to expansion of the site where sewers already are installed.

Tracking additions and redevelopments poses an interesting dimension to administering the utility. As the years go by, the same site might be redeveloped numerous times. Keeping track of each redevelopment and addition is challenging, as different engineers and architects might be employed for each different phase. To assist staff in comparing plans and reconciling discrepancies, the utility database tracks new plans and inserts the master billing account as the common denominator linking all the plans. A data element, such as a parcel identification number, was discarded as the consolidating identifier because a facility might straddle a number of different parcels and, by relying on this element, we might miss plans that are for the same site but that have the development taking place on a different portion of the site. (See Figure 1 for an illustration of how the application tracks additions for a facility that has been redeveloped over the years.)

Annexations represent an additional source of revenue growth. Annexations and detachments in Franklin County follow a specific administrative process that requires city council approval of two pieces of legislation and Franklin County commissioner approval of the specific annexation or detachment in question. Tracking annexations represents a different database challenge. Incorporating the stormwater utility into the process of disseminating annexation plats assures the utility of receiving notice of additional impervious areas that need to be measured and assigned. The plats, however, do not provide detailed building or parking-lot outlines. Thus, additional research is required to supplement the annexation plat. Typically this will require obtaining copies of site plans from the relevant county in which the annexation is located (the City of Columbus reaches into three separate counties: Delaware, Fairfield, and Franklin).

A separate database table has been created to track all annexations and a script written to the text to graphic files. (See Figure 2 for an example of an annexation record that has been displayed through an AutoCAD-based GIS.)

This information has proven to be very important in delivering quality customer service because invariably a new utility customer will inquire as to why he has received a bill although he has well water and a septic system. Quickly being able to offer annexation particulars, such as ordinance and annexation numbers and an annexation effective date, allows the utility to respond promptly to a customer's inquiry.

Data Structuring and Modeling

Structuring data has about as many options as ice cream has flavors. The experience of the Columbus stormwater utility favors ease of access and simplicity of design. Many database platforms exist, but the usefulness of the database has been tied to the availability of the raw data. Therefore Columbus has adopted platforms that provide flexibility through the generous use of built-in wizards to facilitate screen generation, query and analysis, and reporting.

Being able to relate one table to another by embedding common denominators has proven to be a crucial factor. We have learned that by segregating data into various tables, we can conduct queries more quickly, and sharing data with other interested parties has been made easier with the use of smaller files.

The question of centralizing data has consumed many a discussion. I offer the following observation: Wish lists grow over time as people discover the power of easy-to-access and easy-to-understand data. It has been my experience that it is easier to build and maintain a database that anticipates future uses and already has placeholders for various data elements than to add to a database. Establishing relations and connectivity can threaten the integrity of the data as overarching changes are made. Whether the stormwater utility is responsible for billing, flooding complaints, capital improvement program inquiries, or hydrological modeling, many communities have already-built databases that incorporate data tables related to digital flood insurance rate maps, best management practices, and customer relations. Borrowing these databases, building on them, and then adding data elements unique to the specific community is probably the best data structuring approach.

Once all the tables have been built, it becomes much easier to build an application that allows for quick and easy data entry. Overcoming procrastination is easier when you can point to the availability of a data-tracking methodology. Rules can be developed that preserve data integrity and maximize the efficiency of the operation. For instance, entering an address into a complaint table seems relatively simple, but if too much latitude is built into the data entry structure so that misspellings of street names become commonplace, then analysis becomes more troublesome because all relevant data might not have been captured in a snapshot. One way to avoid this problem is to establish a street table linked to a drop-down menu option so that the entry of street names is more rigorously controlled.

Stormwater programs throughout the country share similar concerns about water quantity and quality. Whether a community is modeling for total maximum daily loads or for floodplain analysis or both, the underlying data elements have been specified somewhere already. It is simply a matter of not reinventing the wheel and drawing upon what has been accomplished elsewhere. (See Figure 3 for a screen shot of selected tables that have been created for use by the Columbus stormwater program; the structure of many mirrors the format and labeling protocols of others.)

In the end, there are many possible methods for approaching a specific task. The legitimacy of a utility ultimately depends on reminding the community that the method of revenue collection is fair and equitable. The best way to demonstrate that is to have an open and accessible database that affords transparency and public accountability. Developers of the future are best served by thinking through the needs and desires of stakeholders and clearly delineating the specifications of a particular application. There is no reason for spending hard-earned user fees and taxes simply to duplicate efforts accomplished previously.

References

Brown, Dirk. "Using GIS Technology in the Development and Maintenance of a Stormwater Utility." GIS/LIS Conference Proceedings. October 1997. Reproduced with permission from the American Society of Photogrammetry and Remote Sensing. (Available on-line at stormwaterfinance.urbancenter.iupui.edu/PDFs/GISLISv3.pdf.)

Brown, Dirk S.G. "User Fee­Based Financing in the 2000s." Stormwater, January/February 2002.

Clary, Jane, Jonathan Kelly, John O'Brien, Jonathan Jones, and Marcus Quigley. "National Stormwater Best Management Practices Database: A Key Tool to Help Communities Meet Phase II Stormwater Requirements." Stormwater. March/April 2001.

Urban Water Resources Research Council of the American Society of Civil Engineers. "National Stormwater BMP Database Doubles." Stormwater. May/June 2002.

Guest author Dirk S.G. Brown is the stormwater utility manager for the City of Columbus, OH.

SW - September/October 2003

Home + About + Subscribe + News + Calendar + Glossary
Talk + Images + Advertise + Contact Us + Search + Register + Services

Erosion Control Magazine | MSW Management Magazine
Grading & Excavation Contractor | ForesterPress | Forester Communications

© 2000 - 2003 Forester Communications, Inc.