Database Best Practices

Teaming For Technology DC is part of a nationwide program of AmeriCORPS/ VISTA, IBM, United Way, and Public Allies designed to increase the capacity of nonprofit organizations through the use of technology gifts and information management, primarily in the form of database creation assistance.

Our experience in database development has led us to develop a series of “best practices.” Our goal is to create a set of guidelines that help organizations to create usable, sustainable, and sharable databases. The following list of guidelines was created with a focus on Microsoft Access, but it can be applied to other database systems as well. We create databases based on Access 97 because the majority of the nonprofit organizations we work with already own Microsoft Office, which includes either Access 97 or 2000. We also use Access because the interface is familiar to most office users.

The guidelines include: creating documentation, providing training, using common standards and conventions, using forms for data input, using a simple and consistent design, and creating templates or applications under an open source license.

Documentation

The goal of documentation is to create a solution that allows anyone who looks at the tables and schema to get a fair idea of the purpose and methodology of the database. This can be achieved in a number of ways:

• Commenting any macros or code: will help explain the code and the automation features in the database. All fields should have at least a brief explanation of the type of data requested, how it should look, or what not to enter in a field. This information is entered in the design view of the table and will also be displayed by Access at the bottom of the screen when data is being entered. Training should be provided to teach new users to check the bottom of the screen to find help on what’s expected in a data entry field.
• Using validation rules: helps prevent data entry errors by restricting what information can be entered in a field. Validation text can then be used to provide the user with a more descriptive response than the standard Windows error message.
• Providing written documentation: including the schema and how to use the database, will help the users understand the database.

Training

Providing training will greatly increase the sustainability of the database project. In general, having a short user manual is a big help for clients, allowing them to refresh their own memory and providing a basis for training new users. Helping the users to create their own queries, teaching them enough of the keyboard shortcuts, and showing them how to create a mail merge document or how to export information from the database will give them the confidence to use the database, increasing the sustainability and utility of the project

Standards and Conventions

Accepted standards and conventions should be used consistently where possible. Consistency helps users navigate the application and helps other developers follow your train of thought if they expand the purpose of the database. This is one reason we support using the Leszynski naming convention. The Leszynski naming convention is a fairly standard naming convention for Microsoft Access, and it is used and recognized by many designers. It can also make your Access database solution more compatible with other database systems.

Forms

Forms should be used for data input for a variety of reasons, particularly because forms can control what can be altered or deleted in the database. In a table, records can be easily deleted or altered, but in form view, records can be made “read only” or set so that they can be altered but not deleted. Forms also allow for automation of the database fields, reducing the chance of data entry errors and speeding up the data entry process. Methods to help prevent duplicate records may also be built into the form. Access allows forms to use what it calls “datasheet view,”which looks like an Excel spreadsheet, but still has automation and “read only” features available. This isn’t always the best way to present data, but it can be of help for users migrating from Excel.

Simple and Consistent Design

Simple and consistent design in the layout of forms and the database in general allows for easier maintenance and sustainability. If forms are created with simplicity in mind, it will be easier for users to add new forms to the database or extend elements of the database. This makes maintenance smoother and easier for users. Consistency also increases the user’s ability to predict how a database will work. If a right click always opens a menu or a double click always opens another form to the record indicated, the user will have an easier time navigating through the database.

Open Source

We included open source development in our list of best practices even though we use Microsoft Access because we feel that it increases the capacity for technology use in all nonprofit organizations.

The open source option gives organizations the right to view and alter the database structure and code as needed. It also allows other consultants to look at the inner workings of the database so they are able to give better technical assistance. Another benefit of open source is the ability of the client to share a database with other nonprofit organizations. These other organization may, in turn, improve upon the database design and share these improvements with the community. The original developer can reuse the code and structure it for new clients. This decreases development time and increases the number of clients the developer can work with. If you are creating a solution for your own organization, it is often easy to solicit help from open source programmers. There are many mailing lists and forums where programmers frequently check in to share ideas, and many programmers are willing to answer posted questions. Another benefit of open source solution is that open source software is usually cheaper to obtain than commercial software, and it can be customized by anyone.

The nonprofit sector has traditionally lagged behind other communities when it comes to using technology to manage information. However, by defining best practices for database design and management, we are hoping to alleviate this problem, helping to ease the transition into greater technology use for non-technology oriented organizations.