In Part 1, I wrote about the importance of database documentation and how it often gets overlooked or done at the last minute.
One of the primary reasons software developers put off database documentation is because the process delays or interrupts the flow of getting the design in their minds into whatever tool they are using – often because they are using the wrong software. This can happen during the early design process or when a software developer responds to design changes.
Typical office spreadsheet and visual flow diagram programs, such as Excel or Visio, are poorly suited for database design, making the whole process very tedious and time consuming. Neither program is able to provide the ideal database documentation of both a visual graph table relation and a spreadsheet style listing of the database table and column details.
Still, the developer must go through the process of creating all the actual table, column, and relation objects in the actual database, as well as maintaining these documents to record any adjustments made during development.
The time and cost benefits of using the right tool for the job far outweighs the cost of the tool.
Many of the previously mentioned time-consuming hassles can be avoided by using a proper database design tool. Such tools combine the process of database design, documentation, and applying the design to the actual database. Documentation can be easily done as the design is made, allowing for much of it to be completed before development even starts. Changes to the design are kept in sync with the database as documentation output is automatically generated from the design.
Several of these tools are available and relatively inexpensive. For this example, I’ll be using DbSchema to demonstrate how much faster and smoother the database design and documentation process can be.
When starting a new project in DbSchema, you’ll need to provide it with the necessary connection information to an existing database to do the development work so changes to the design in DbSchema are immediately applied the database.
If you have an existing database, the schema can be pulled into the design to automatically generate the visual layout. If the database server is not yet available, or you need to do design work offline, then you can use a local database server running on your machine using SQL Server Express, or any of the open source database programs at no cost. You are free to change database connections at any time and selectively migrate schema changes to it.
Once this is done, you’ll be presented with the layout view, which shows all the tables and relationships. Tables can be dragged and arranged as desired. There is no preset size restriction, so your diagram can be as wide or as tall as you want. Multiple layout views can be created, allowing large designs to be broken down into more managed blocks.
Simple database example
For this example, we’ll start with an empty database and create the first table, Customer, by right clicking anywhere in the layout view selecting Create Table. A dialog will appear like this:
Below the table name is a description box, allowing the database documentation to be done as part of the design process. At the bottom are tabs for adding columns, keys, constraints, etc. The dialog for adding columns also provides a description box for documentation of the column.
To start, let’s create the Id, FirstName, and LastName columns. Now, let’s suppose there needs to be another column which contains a value indicating the subscription status of the customer. This is one of those cases where the importance of database documentation comes into play.
Here, I’ve not only documented the possible status values, but also noted which is indicated when this column is NULL. After making this column, I’ll click OK on the table creation dialog, and the table will appear in the layout and will also be created in the attached database.
DBSchema will show the SQL changes made to the database based on what you create, delete, or modify in the design on left panel like this:
Next, I’ll add a simple address table and create a relation to demonstrate how that appears:
In less than five minutes, I have two tables designed, documented, and created in the development database, all while using a single program.
Generating the database documentation
By now, you might be wondering how to get this from the tool into a document you can give the client. The types of document formats and methods on which you can export the design vary from tool to tool.
In the case of DbSchema, documentation is output as an HTML file, which is generated by selecting the HTML Image Documentation option in the Layout menu. The HTML document generated is standalone and can be loaded in any web browser. The contents of which can then be printed or copied out.
When the HTML documentation is opened in a web browser, it will show the tables in the exact same arrangement as that appeared in the layout:
Below the diagram will be a detailed listing of all the tables and columns along with the documented description of each. Users can also click a table and column in the layout and the browser will scroll down to the selection.
Based on this demonstration, it should be clear how much easier this process can be over other manually-created design approaches with spreadsheet, word processor, and flow chart programs. By making database documentation quick, easy, and done early during design, software developers can finally break away from the habits of overlooking or procrastinating database documentation.
