A new approach to documenting databases

Introduction

It is difficult to overestimate the importance of up to date database documentation for any serious project, yet maintaining it has been a tedious and boring job nobody wants to do.

LiveDoco tries to address this and make it easier and more fun.

This article compares the conventional database documentation generator way to the new web-browser based SQL Server metadata explorer interface approach - the LiveDoco. If you are already familiar with the former you might want to skip to the "new approach" section.

The conventional way

There are many database documentation tools on the market that try to make this job easier. These tools can generate nicely formatted database documentation or data dictionary from your database's metadata. Supported output formats are usually HTML, CHM and Microsoft Word. Some of them even allow editing descriptions, which in SQL Server's case are usually stored as "MS_Description" extended properties in the database.

Top 3 would probably be Red Gate's SQL Doc, ApexSQL Doc and ElsaSoft's SQLSpec.

Although documentation generators are a major improvement over the manual documentation process, they still have their own limitations.

The problem with documentation generators is that their output is essentially read only because any changes to the output would be lost once the documentation is regenerated. The solution is to keep these notes in the source database, and there are built-in facilities for that in most modern RDBMS, including SQL Server’s MS_Description Extended Properties.

The problem is that the standard SQL Server Management Studio does not provide a user friendly way to edit them. Installing the above-mentioned tools would address this issue to some extent, but it is still far from ideal.

Commercial tools are feature rich but quite expensive and installing such software on every developer’s and DBA’s PC is usually not feasible.

Both commercial and free tools would also create additional IT admin overhead - installation and maintenance.

Even from user's perspective this solution is suboptimal. Imagine the following scenario: A developer or browses the database documentation and finds an error or an omission. In order to fix it he needs to:

  1. Start the documentation tool (say 20 seconds including connection to the preselected database)
  2. Find the place that needs editing (5 seconds)
  3. Edit it (let's say 20 seconds for a relatively simple edit)
  4. Then trigger the documentation rebuild (another 30-40 seconds or more depending on the database complexity)

That's three extra steps and a full extra minute for something as simple as changing a couple of words in the documentation! And by the way - the rebuild might even fail due to the file locking errors if there are other people using the same document. There's got to be an easier way, right?

LiveDoco - a new approach to database documentation

What if we had a tool that allowed us to just edit the descriptions in place? Like we would change our Facebook status?

LiveDoco tries to address these issues by approaching the task from a different angle. Instead of generating a static document it simply provides a web-based interface to your SQL Server database's metadata. It renders database object pages directly from the underlying database.

This approach makes the following features possible:

  • LiveDoco completely eliminates the documentation generation step, thus greatly reducing "access latency" to any database

    For instance it could be a database you have never worked with before and just received from a client - simply open LiveDoco in your browser, select the database - and start exploring your database's structure, or adding notes to the objects of interest.

  • In-place editing of descriptions

    To edit a description just click on the pencil icon next to it:

    LiveDoco extended properties editor

    Changes will be immediately visible to all your team members who use the same database. The information displayed by LiveDoco is practically never out of sync with the actual state of your database. This makes fixing or updating database documentation an easy and natural process, thus improving the quality of the documentation.

  • Deep linking

    This means you could bookmark a particular page or send a link to a colleague via email/IM:

    LiveDoco - Deep linking support

    This is normally impossible with other database documentation tools (generators) even when they output HTML because they usually utilize HTML frames and this hides the current page's address - all the browser's address bar has is just the address of the IFRAME page. This is one of the main reasons LiveDoco does not use frames. If your browser is currently pointing to a sub-object on the object's page (EmployeeID column of the HumanResources.Employee table in the above example) and you send this link to a colleague - it will even scroll to that column (if needed) and blink when opened as in the example above (see it for yourself here).

  • Flexible and intelligent search facility

    Because LiveDoco has access to the underlying database's metadata it can be restricted to search only the particular object types:

    Search results are displayed as you type below the Search Options section:

    This approach allows quickly examining your search results without using your browsers "Back" button.

 

LiveDoco has other features and enhancements that are not directly related to its architecture, but still worth mentioning.
These include:

  • Foreign key column links

    Any column participating in a foreign key relationship on both FK (referencing) and PK(referenced) ends, has a list of links pointing to both referencing and referenced columns:

    We believe this is an important navigational aid that helps visualizing table dependencies in both directions. Merely listing foreign keys along with their columns as it is usually done is not good enough.

  • Rich hyper-linking and visual enhancements

    Although making object names a clickable link is quite common, LiveDoco adds a special "blink" effect - when you click on say a table column's name - not only it takes you to the parent table's page and scrolls the column into view, but also briefly blinks its background to attract users attention and save him or her few seconds.

  • Foldable sections

    LiveDoco has MSDN-style foldable sections:

    LiveDoco foldable sections

    This reduces visual clutter and allows hiding less important details on the page - such as trigger code in the example above, while still making them available if needed - the code is only a click away. These sections can be nested.

  • Export facility

    Export EPs

    LiveDoco has extensible export facility that allows exporting your descriptions (or any extended properties for that matter) into a SQL script that can be used to insert/update the notes into another database of the similar structure. This is just one application of the export facility. It can be used to generate any text from the database metadata - be it XML or various SQL scripts or even C# source code.

Applications

All these features make LiveDoco an excellent database structure exploration and documentation tool, especially for database developers. With LiveDoco you can:

  • Easily document your databases

    LiveDoco can be made available to all your employees just by installing on an intranet IIS Server. If you already own a database documentation generator or plan you purchase one - you could use LiveDoco to let your employees document your database(s) and then generate the documentation using only one copy of the generator software, since most of them also use MS_Description extended properties for descriptions/comments.

  • Explore and document unfamiliar databases

    Quite often database professionals need to work with unfamiliar databases for example to import data from them or to implement interfaces to such databases. LiveDoco's easy access to metadata and search facility can greatly simplify these tasks when used in conjunction with SQL Server Management Studio or other similar tools. For instance you could use LiveDoco search to identify potential foreign keys in the database based on column names, then try adding them in the SQL Management Studio and see them immediately in LiveDoco. Another use would be to add notes to relevant columns or tables for future reference while exploring this new database. This is especially helpful when dealing with "ugly" databases with obscure naming and relationships between tables.

  • Custom script templates
  • If you find yourself writing a particular type of script over and over again for different databases (for example delete from a set of tables without triggering FK errors) - you could prepare a LiveDoco template that would output such script given a selection of tables.