Friday, September 24, 2010

Real Time Database Documentation

All the information about a project should be maintained from within the project whenever is possible. I have never seen a perfect documentation nor an updated documentation to live long enogh in any company I have been as employee or contractor. That is why I believe that the most valuable documentation is the one that can be auto generated when you need it.

The database or Model information in particular is very important as that is the bottom layer, the foundation of any modern software architecture. Having the ERD/EER available is then a must have for the agility of a true Business Driven development (BDD). Enough to say it allows to plan for new features providing just the gap to be implemented.

1. Provide business access to your current database metadata. You are maintaining it in a SCM repository, aren't you? This is the best way for business to see your naming conventions are aligned with their business language. So let them see your current tables and fields.
2. Provide business with all current default values. I hope you understand you must script them and keep them in a repository as well.
3. Provide business with a tool to get the EER/ERD by themselves. For MySQL install MySQL Workbench.  Here is all they need to get a whole diagram whenever they want:

1. Open a MySQL Workbench already saved project. In a project some of the settings will be already saved but youy are free to create one project from scratch every time you want a new EER/ERD.
2. Choose "Create EER Model From SQL Script" from the Home page and point to the metadata file.
3. Select "Model|Create Diagram From Catalog Objects" to generate the diagram.
4. If the tables show up too tight select "Model|Diagram Properties and size" and expand columns and rows as needed.
5. Hit Arrange/Autolayout. If you need more space go back to the previous step.
6. In big diagrams sometimes we do not need all information about foreihn keys for example. From preferences/diagram select “Hide Captions” and deselect “Draw Line crossings” and “Center captions over the line”
7. Confirm all tables are viewable and save the project.

Here is a nice command to cleanup the MySQL database metadata (also referred as dump file) to show just tables and fields. You can encapsulate it in a batch/bash script so business guys have a cleaner file to look into when wondering if a given keyword has been already used in the system.
cat application.sql |grep -v "^/.*"|grep -v "\-.*"|grep -v "DROP.*"|grep -v "\`id.*"|grep -v "\`version.*"|grep -v "UNIQUE.*"|grep -v "KEY.*"|sed s/ENGINE.*//g|sed s/CREATE.//g > application_tables_and_fields.txt

No comments: