Suggestions for Developing Your Own Database Application ​

But before you get started, you need to answer two key questions. First, why didn’t the COTS system meet your needs? And second, which data management or process- related issues did the COTS system fail to address?

There are many potential reasons why a COTS system isn’t the answer:

• inability to simulate your existing processes
• you need a database that is relational to existing data sources, but this isn’t feasible with the COTS system
• unresolved issues related to the user interface, such as an inability to define organization-specific input fields
• issues related to functionality, such as the inability to define or customize data outputs
• inability to define custom access privileges (which may be necessary if non-IHs in your organization need access to IH data)

When users have problems with data input, the COTS system will be under-utilized and the quality of the data will suffer. If you have unresolved issues with data retrieval, integrity, or validation, or if your data is unprotected or inaccessible, you’d be better off storing records in a file cabinet or logging details on a spreadsheet.

The amount of effort required to develop your own database application can be substantial. It takes considerable forethought and an investment in time and resources. These need to be weighed against the effort and cost associated with working with a commercial provider to customize a COTS system, especially if the COTS system actually simulates your existing work processes. If you decide to develop your own database, here are a few things that worked well for us. STAFFING Don’t develop the database in a vacuum. Involve and engage the end users, programmers, and support staff throughout the entire process, from planning to production. Identify everyone who will need to interact with the database application, and keep in mind that this might include non-IHs.

Identify programming skills available for application development. Is a skilled programmer available who can be devoted to your project? Is there someone on your staff (possibly an IH) who isn’t a programmer but can be trained and mentored for user interface design? Such a person can help the lead programmer during development, and, after launch, make updates and modifications to satisfy end users and keep up with changing processes. Since development involves a lot of back-and-forth between the programmer and the organization, you can save an immense amount of time by having an IH learn a basic set of programming skills. Combining an IH with a programmer allowed us to make real-time decisions about process flow and data input variables from conception to production.

Identify your support staff. They are the ones who will maintain the servers where your database application resides and ensure that your data is backed up regularly. They can also help identify where the program will reside and any infrastructure support that will be needed. If they also manage your other institutional databases, they can help make your database application relational. Establish business rules with your support staff to ensure that the operation of the system is maintained. For example, who will be running the backups, and who will install updates to the software or patch the operating system on your server? Ensure that user agreements are in place before you go into production. SOFTWARE A key decision will be to select the software for developing your database. Several factors play into this decision. Method What method will you use to determine the functional requirements of the database application? Will you do rapid prototyping, or will a requirements document be written first? If you plan to do rapid prototyping for quick deployment and proof of concept, the requirements document, typically written before development begins, can be written as development progresses. Some software applications are better for rapid prototyping than others, so it is important to select software that lends itself to ease of use and has the desired feature sets. Scope What does the database need to do, and what type of data does it need to contain? Even if the database is being developed via rapid prototyping software, you need to define the scope first. Users One of your most important considerations is determining the number, job classification, and methods by which users will need to connect to the database. For example, will it be used by one IH, or by a thousand people with varying job classifications—IHs, technicians, management, and so on? Will some users need to connect from mobile devices or via Web browsers as well as from computers connected directly to the company network? Will the database need to support both Microsoft Windows and Apple operating systems? Some commercial products allow only a few individuals to simultaneously access a database or support only one type of operating system. Other products allow many more simultaneous users under different operating systems, who may also connect via the Web or from smartphones and tablets. Programmers’ Skill Level If you select complex programming software, you’ll need a senior programmer who is devoted to your project. If your plan involves rapid prototyping or if the only programmer available is less experienced, then something like FileMaker Pro or Access may be a good choice.

In our organization, selecting FileMaker Pro gave us the option of using an IH who had some programming skills to develop content and prototype the processes. FileMaker Pro also met our requirements for a database that would support 50 to 100 simultaneous users of both Windows and Apple operating systems, as well as those using mobile devices and Web browsers, to collect and enter data and view live reports. By developing and proving our processes in FileMaker Pro, we avoided trial-and-error in more complex and time-intensive software before considering converting our database to Oracle, which is the company standard for production databases.

When using the rapid prototyping model for database development, keep in mind the form and function of your desired software endpoint. Special attention is needed when defining fields and naming layouts in FileMaker or Access so that, once converted, the database will behave the same way in Oracle. You also need to evaluate budget constraints for both software procurement costs and development time. Databases developed directly in enterprise-level products such as Oracle can be robust, but they will often take more time to program and require a higher skill level. Talk to either your IT staff or a consultant before making a decision. DESIGN CONSIDERATIONS Once your key people are assembled, start by identifying the end products and the overall architecture of the database. What do you need the database to do, and what are the outputs? The breadth of the data needed for specific processes becomes much clearer if you start with the end product and work backward to identify the input variables. You can then determine whether your database needs to interface with other systems. If so, have your IT support staff or a consultant identify the connections needed to provide live data feeds. They can help determine how the data will be fed and how it will be refreshed. If the programming languages between the databases are different, IT staff will need to figure out which tools are needed for setting up the data extraction. 

When designing the database application, insist on compliance with internal and external requirements. Controls on data access for specific users or user roles, security of transmitted and stored data, and data retention periods are among the topics frequently addressed by contracts, regulations, and statutes, in addition to your company’s policies and practices. If your company is OHSAS 18001- or ISO 9001-certified, design your database system with data quality and continual improvement in mind. For example, if you have a reoccurring survey (such as periodic surveys of a workplace), design the database to automatically capture and append issues identified from the previous survey to the current survey record so you can determine the effectiveness of past corrective actions. Data history and revision logging are necessary for maintaining data integrity and essential if the database will be used to house official records. However, programming this layer into the database will take additional time.

When designing the user interface screens for data entry, consider building in as many tools and including as much logic as possible to avoid common errors. Limit navigation to ensure that data required for a complete record must be entered. Developing tools that require specific data input fields for running an algorithm helps minimize data quality issues. If specific fields must be completed, add logic stipulating that the screen can’t be saved or the next data field can’t be entered before completing a required entry. We’ve found that color coding fields helps users recognize which fields are required. PLANNING Once these decisions have been made, it’s time to develop the project plan—who does what and how data flows. Identify the user interfaces from the various data entry points all the way to output reporting and how each of them will function. These plans can range from simple flowcharts to elaborate documents. Often, existing written procedures will outline your database application requirements. More elaborate plans may include timelines with completion dates for key deliverables.

Consider your organizational requirements for accessing the database. How will end users gain access? What computer security or data protections are needed? Work closely with your programmer when developing these documents; they will be the roadmap—and, in some cases, the contract—for the database application development. TESTING AND SUPPORT After the plan is finalized, identify resources needed to support database development and eventual launch, including maintenance and day-to-day operation. Estimate the cost and determine whether the necessary resources are available.

As code development begins, continuously engage the end users and hold regular meetings to solicit their feedback. Identify when beta testing can begin. Often, the best time to ask beta testers for feedback is immediately following completion of a module or feature. Address their feedback in a timely manner so they remain engaged in the process. When development is completed, the beta testers will be your biggest advocates and will help other users accept the new database and related processes. Keep all end users well informed about development—let them know what to expect and when.

During beta testing, it is essential to write procedures for the end users to help keep data input consistent. When the database output interfaces with different groups within your organization, you may need to develop business rules. For example, if a report recommending specific actions is sent to a different work group outside of your organization, that group needs to know ahead of time when these reports may be generated and what types of actions may need to be addressed. Business rules help clarify everyone’s roles and responsibilities for managing database outputs.

Your working group will need to determine when the database application system is ready for production. Hold meetings with the end users during the roll-out phase and review any procedures associated with use of the database. It’s helpful to have some of your beta testers in the audience to help answer questions. Change always has a learning curve and may be met with opposition from workers who have longevity with your organization or those without strong computer skills. Keep in mind that your database system is a work in progress and can be significantly affected by changes in technology. Anticipate the need for version updates—not only to the main database software, but also to other software that links to other data sources, including third-party>applications, both internal and external to your organization.

Once the database is launched, ensure that programming support is available to help resolve bugs, update database interfaces, and keep the database functioning the way it was designed. This support is essential in the first few weeks after production; plan accordingly to make sure bugs can be fixed in near real-time. REFLECTION Allow time for the new processes to begin and users to adapt before reflecting on what was learned. Then ask yourself the hard questions: did we make the right choice, and was it worth it? How did we benefit from developing our own system? For us, there was no doubt the final product was worth every bit of effort, including all the challenges. DIANA L. LARSON, CIH, CSP, is the Industrial Hygiene Section leader at Lawrence Livermore National Laboratory (LLNL) in Livermore, Calif. She can be reached at (925) 423-5468 or larson29@llnl.gov. RYAN G. KAMERZELL, CIH, is an industrial hygienist and the Respirator Program administrator at LLNL. He can be reached at kamerzell2@llnl.gov. JAMES D. BRANUM is a software engineer with more than 35 years’ experience developing database applications at LLNL. He can be reached at branum2@llnl.gov.