Documenting a database during its development is a best practice to ensure that the organizational schema, data objects, and other related information are captured for future reference.

This documentation often takes many forms including data dictionary, database administrator guides, database architecture documentation, and database functional specifications. In this article, I’ll use the term “data dictionary” to refer to database documentation. While your current database documentation may not fly under the same title, the principles outlined in here are designed as a launching pad for you to better your own database documentation.

Developing your database documentation
Database documentation serves multiple audiences, including:

  • Database architects
  • Database developers
  • Database administrators
  • Production support staff
  • Quality assurance staff

The actual process of developing your data dictionary will draw on the talents of a cross-functional team including database administrators and/or database architects, business analysts, and technical writers. While your organization’s mileage may vary with these job roles in the development of database documentation, the heart of the database documentation must come from the team building the database.

Database administrators can extract the necessary data dictionary information from the database itself. A data dictionary is included as an electronic file in many relational database management systems (RDBMS). DBAs and database developers can extract useful information from the file, including:

  • Lists of all files included in the database.
  • The number of records in each file included in the database.
  • The names and types of each database field.

The information included in the data dictionary is hidden from normal users to prevent any corruption of its contents. While the data dictionary in a database serves an administrative and management function, it doesn’t include any actual database data, though an RDBMS requires a data dictionary to access data from the database

Business analysts and technical writers, with their expertise in print technical documentation, also play an integral part of documenting a database. While the information extracted by the DBA is valuable, it still must be presented and communicated to an internal or external client audience. Additionally, the skills of this staff are integral in eliciting technical information not captured by automated documentation tools. Such an approach requires that the business analysts and technical writers are immersed in the project vs. being brought on at the tail end of the project to “make things pretty.”

Typical elements to include in your database documentation
You should consider capturing the following information in your database documentation:

  • Data element number
  • Data element name, which is the commonly agreed upon unique data element name (The data element name is generally decided upon during the design phase with some influence from the requirements gathering phase.)
  • Short description of the data element
  • Security classification for the data element (The security classification is generally an organizationally specific concern. The database development team and the organization’s security team—if any—both have a vested interest here and should be involved in the security classification. Other influences on security classification include the requirements document, functional specifications, and design document for the database under development.)
  • Listing of related data elements with important relations to the specific data element
  • Field names based upon the technical names provided within the database schema and/or RDBMS
  • Code format including any needed special presentation and the datatype format and size
  • Default value specifying whether the data value may have a default value (Any variables for the default value should be listed here.)
  • Element coding providing an explanation of coding and validation rules
  • References to other documents listing any validation rules between this element and other elements documented in your database documentation and data dictionary
  • Database table references
  • Data source for the element
  • Valid dates for the data element
  • History references
  • External references
  • Data element version

This outline is meant to provide guidelines for typical sections. You can customize this outline, and you should feel free to make adjustments to meet your own internal organizational or external client requirements.

You’ll also need database table documentation. Such database table information can be drawn for a SQL database with the SQL command help table, including:

  • Table name
  • Database or table owner name
  • Data element column names and details
  • Key order for all elements
  • Database index information
  • Technical table organization
  • Duplicate row information (Are duplicate rows allowed? Or not allowed?)
  • Data element list
  • Table security classification

You should also consider your database schema when planning your database documentation. Tools like Visio can help you develop a graphical representation of the database schema for inclusion in print documentation.

Automated tools for documenting your database
There are a number of automated documentation tools available to assist in the development of databases. A couple of available tools include:

Microsoft Visio Professional 2002 is another popular database documentation tool. It includes the following templates:

  • Database model diagram
  • Express-G
  • ORM diagram

Best approach
The best approach to database documentation relies on a combination of automated tools and the skills of experienced business analysts and/or senior technical writers. Having these people work in conjunction with the expertise of the database architects, developers, and administrators will ensure that your documentation meets the needs and expectations of all who are involved.