Best Practices Guide for Documenting Access Databases

This guide provides a set of best practices that will help you with the task of documenting a Microsoft Access database.  These best practices were established by the developers at Adivo who have extensive experience in technical documentation, database design and development.

Start With a Good Database Design

Good database design can make documenting your Access database a much easier task.   Although it might not always be an option, if you have any influence over the database design, here are some key design areas that you should look out for:

  • Naming conventions - The lack of naming conventions can result in very inconsistent and cryptic names for the Access tables, columns and queries making it more difficult to document them properly.  Applying good naming conventions to the database design will promote better readability of the documentation.
  • Primary keys - While every table should have a primary key that defines which columns uniquely identify a record, Access does not require them.  Not only does this affect database performance and data integrity, knowing what the primary key is for a table is key to understanding the data model and an essential part of the database documentation.
  • Table relationships - Another key to understanding the data model is documenting the relationships between tables.  And just like primary keys, they are not required to be defined.

And even if you do not have any control over the database design, a quick review will help you better estimate how much time it will take and set expectations for the completion of the documentation.

Embed Comments inside Your Access Database

Access provides description properties that can be used to add comments to tables, columns and queries.  These comments are stored as metadata within the database definition, making them easy to maintain as your Access database structure changes over time.

By embedding comments inside your Access database, you create a "self-documenting" database that will be easier for DBAs and database programmers to understand.

Comment What You Can and Annotate Everything Else

For Access databases, you should add comments to the following database objects:

  • Tables - Most tables are typically used to store logical representations of real world objects.  The comments for these types of tables should describe what that real world object is.  Your database may also include tables, sometimes referred to as "link tables" that are used to create many-to-many relationships between tables.  For these types of tables, your comment should describe the purpose of the relationship.
  • Queries - Queries are typically designed to answer a question.  The comments for views should describe what those questions are.
  • Columns - As tables typically represent real world objects, the columns within those tables are used to store properties related to those objects.  Column comments should describe what those properties are and how the column represents them.

The descriptions that you add to these database objects should be concise and consist of one or two sentences.  If there is additional information that is important to document, you should consider structuring your comments into two parts: a summary description followed by a remarks section.  The remarks section can be used to include the additional information.

Embedding comments in Access database using description properties does have some limitations. These include:

  • Description properties can be no longer than 255 characters.
  • Description properties are not available for query columns.

To get around these limitations, you can use a tool like TechWriter to annotate the database with external comments that can be merged together with the embedded comments to produce the complete documentation.

Use a Documentation Generator

Having a self-documented Access database is helpful for database design and maintenance, but there are many instances where you are going to need actual database documentation. Here are a few examples:

  • System Documentation - If you are creating a database for a client, a complete set of system documentation is typically a required deliverable.
  • End-User Documentation - Not all the users of your database are going to be experts at Access database design.  These users might not have the skills necessary to locate and understand database design information within Access.
  • Design Reviews - Not all participants in a design review are going to have the same level of Access database knowledge and expertise.  Your subject matter experts may not be technical at all.  To facilitate such a design review, you will need to provide documentation that anybody can read and understand.

Fortunately there are tools like TechWriter that can be used to generate the database documentation automatically, leveraging the database definition and the comments you've embedded.

Make Documentation Part of Your Development Process

If your project is using automated build tools like Ant, NAnt and MSBuild, you can make generating the documentation part of the automated build process for the system.   By having the documentation as up-to-date as the system itself, your project will benefit in the following areas:

  • Knowledge Capture - While some development methodologies such as Agile ascribe to a "document late" philosophy, projects can suffer greatly when they lose a team member.  The knowledge of a system literally walks out the door.  Documenting as you go minimizes the impact of losing a team member and makes it easier for new team members to ramp up.
  • Communication - Documentation can help communication among project team members.  The project manager can better assess the status of the project.  Subject matter experts can verify that developers are on the right track.  And mistakes can be identified earlier in the project life-cycle.
  • Documentation Quality - Scrambling at the end of the project to throw the documentation together for the final deliverable can result in your Access database not being documented properly, if documented at all. 

By making documentation part of your development process you will ensure that the documentation is always in sync with your Access database, even in extreme programming environments where the Access database is constantly changing. 

For more information please visit: