When working with legacy systems documentation is one of the most important things to keep up to date. One thing I have noticed is that the database documentation quite often fall behind when it comes to updates. Changes are often made to the database but the documentation is not always updated in the same way as new functions are. My trick here is to keep the database documentation inside the database. This way I do not have to search for a separate document every time you make changes to the databasen. I can update the documentation in the same view as the one I made the changes in.
To accomplish this I create a new table in the same schema as the program tables. I usually call it something like ‘db_doc’ or ‘db_info’
In this table I have the following columns:
- ID – a unique id for the row (I often use ‘autoincrement’ in MySQL)
- Table – textfield for the name of the table to describe
- Column – textfield for the name of column to describe
- Description – large textfield for the description
- Author – textfield for the name of the person who updated the field
- Timestamp – timestamp field to be updated on inserts and updates to the row
Here is a SQL example of the create statement for MySQL:
CREATE TABLE `db_info` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `table` VARCHAR( 255 ) NOT NULL , `column` VARCHAR( 255 ) NOT NULL , `description` TEXT NOT NULL , `author` VARCHAR( 255 ) NOT NULL , `timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL )
Here is an example view of a table working as database documentation:
Now if you wanted it would be pretty easy to make this into a web page to give it a little more “documentation” feel.
0 Comments.