Databases start off small. Perhaps just a couple of tables with a few dozen columns. What could be simpler or more obvious? Then someone adds a feature or five, a few configuration options, ports the application to a new platform, a browser interface, half a dozen different languages. Soon enough the database is a huge mess. Yes, yes, someone should sit down and normalize the thing. By now the application is selling on an app store for $2, so who can afford the effort to normalize? Maybe then a summer student could at least document the database. Sure, and we can add the student’s efforts to a million other badly formatted, half completed database design documents in obsolete MSWORD files.
You know what would be nice? A way to self-document a database. The documentation should be contained in the database itself, so it can move with the database, grow with the database, and keep pace with database engine technology and popular documentation presentation.
I would like something like Doxygen for source code, but for a database. Doxygen uses comments with a specified format, so that when the source code is read by the Doxygen application, pretty and up to data documentation is generated automatically. The comments can be maintained in the normal course of editing the code, using any code editor without having to worry about special applications, but whenever required Doxygen can be used to generate the documentation for the latest code.
So, could we add a special table to our database which contains documentation of the other tables? A table that can by updated by any database management tool, even raw SQL, but when read by a special application, will generate the documentation.
I mostly use SQLite for my application databases. Paweł Salawa offers a nice SQLite database management tool SQLite Studio. The Studio is open source, written in TCL and has a sophisticated plugin system, so it was easy for me to add what I needed.
A simple example:
Suppose you have a database with the following table
CREATE TABLE config ( pro, KEY );
Now add a new table to document this, plus any other tables.
CREATE TABLE __sqlite_studio_doc ( desc, _table, _column );
Now add the documentation to the new table
desc, _table, _column True if PRO designer is running, config, pro The license key, config, KEY Stores configuration settings, config, __sqlite_studio_table
The special _column value, __sqlite_studio_table, is used for the documentation for the table itself.
Pretty simple? Well, it is if you are familiar with maintaining a SQL database. ( If you are not, why are you reading this? )
Now to generate the documentation.
Install SQLite Studio
Install the new export plugin. ( Download link to sqlitestudio-2.0.13\src\plugins\export )
Run the new export plugin HTML+DOC on your database.
The output will look like this, when viewed in a browser:
