Piotr Kononow
This article presents 8 different types of tools you can use to create a documentation of your existing databases.
In short, they are:
- Advanced documentation tools
- Word processors
- Spreadsheets
- Database tools
- Documentation generators
- Documentation tools
- Data Modeling tools
- Diagramming tools
Let's have a closer look:
1. Advanced documentation tools (read/write + metadata + authoring)
There are tools that go beyond extended property/comment editors and documentation generators - let's call them metadata repositories. Those tools keep data in a separate repository which enables them to gather and store much more metadata and makes them independent on the database platform. They provide more functionalities: advanced authoring, more metadata, database diagrams.
Benefits
- Global repository
- Rich metadata
- Better authoring capabilities
- Support for multiple database engines
Disadvantages
- Slightly more difficult to set up and learn
Tool examples
Documentation generated with Dataedo (product tour)
2. Word processors
When you want to create a printable document, word processors (like MS Word or LibreOffice Writer) sound like an obvious choice.
Benefits
- You are most likely already using it
- Good formatting, branding and printing capabilities
Disadvantages
- It's a nightmare to prepare
- It's an even worse nightmare to maintain
3. Spreadsheets
A slightly better choice for a tool would be a spreadsheet (like MS Excel or LibreOffice Calc). It makes navigating, searching and filtering data more convenient. It also feels more natural since documentation usually has tabular structure.
Benefits
- You are most likely already using it
- Easy to search and filter metadata
Disadvantages
- Poor printing capabilities
- It's difficult to prepare
- It's a nightmare to maintain
4. Database development and management tools
Standard database consoles like SQL Server Management Studio (SQL Server), Oracle SQL Developer (Oracle) or MySQL Workbench (MySQL) support some basic database schema and model documentation and generation.
Those features might include:
- Commenting data elements (tables, columns, views etc.)
- Generating HTML or PDF documentation
- Reverse engineering database schema to ER Diagrams
Benefits
- Most of them are free or shipped with the database engine
- DBAs, developers and architects already use database management tools
Disadvantages
- No global documentation for both - objects descriptions and diagrams
- Little or no editing and authoring capabilities (only basic annotation features)
- No repository for diagrams
- No ability to generate integrated documentation consisting of diagrams and detailed data dictionary
Tool examples
- SQL Server: SQL Server Management Studio
- Oracle: Oracle SQL Developer
- MySQL: MySQL Workbench
Database documentation generated with Oracle SQL Developer
Diagram generated with SQL Server Management Studio
Diagram generated with MySQL Workbench
5. Documentation generators (read only)
There is a number of dedicated tools whose sole purpose is to generate documentation from your database schema. This category of tools doesn't have any editing capabilities and can only generate a documentation from metadata extracted from the database.
Benefits
- Easy to use
- Better formatting than standard database tools
Disadvantages
- No editing capabilities, separate tool is required for this (e.g. database management tool)
- Annotating capabilities are limited by database platform (you can't provide rich text descriptions or describe elements if DBMS doesn't support that)
Tool examples
- dbForge Documenter for SQL Server
- Elasoft SqlSpec
- Spectral Core Documenter
Documentation generated with dbForge Documenter for SQL Server
6. Documentation tools (read/write)
There is a number of tools that enable you to do both functions of documenting:
- describe tables and columns (data dictionary), or other database objects and
- generate convenient documents for sharing.
This category is different from the previous in editing capabilities which is a huge difference as providing the descriptions is a key documentation activity. This is where value added is being created. Document generation just makes it easier to access and share, while providing descriptions of data structures gets the knowledge out of the peoples heads and saves it for later reference.
Those tools mostly store metadata (descriptions) in the database itself - in Extended Properties in the case of SQL Server or comments in the case of MySQL and Oracle.
Benefits
- Ability to describe/annotate data elements (tables, columns etc)
Disadvantages
- Metadata scope limited by database capabilities (you can't add more information than the engine enables)
Tool examples
- Redgate SQL Doc
- ApexSQL Doc
- Elasoft SqlSpec
Redgate SQL Doc description editor and documentation generator
7. Data Modeling tools
There is a large category of tools which are designed specifically for logical and physical engine-independent data modeling. They provide forward and reverse engineering functionalities. They are good for creating ER Diagrams, but much less for describing data elements (creating Data Dictionaries). Even worse at the maintenance of the documentation.
Benefits
- Global repository
- Rich metadata
- Better authoring capabilities
- Support more than one
- Support for multiple database engines
Disadvantages
- Very complicated and overloaded with features
- Designed for forward engineering purposes, not really ideal for reverse engineering
- Mostly non-convenient column descriptions editing (Data Dictionary)
- Clunky default export documents
- Exporting sometimes requires programming/customizations
Tool examples
- Erwin
- SAP PowerDesigner
- Idera ER/Studio Data Architect
8. Diagramming tools
If you just want to create database diagrams (it's not a full documentation of the database) you can use generic diagramming tool.
Benefits
- Good diagramming and visual capabilities
Disadvantages
- No support for Data Dictionary (a description of each data element)
- Most of these tools don't support connections to database
- Even if they support connection to a database, it is not easy to maintain such models when database schema changes
- No support for other database elements - stored procedures, functions, triggers etc.
Tool examples
- MS Visio
- Gliffy (online)
- LucidChart (online)
Conclusion
I hope this is a convenient overview of the tools available if you want to document your existing databases.
