Having to create documentation is like having to go to the dentist. You know you have to go and you dread it every six months. Database documentation is similar because everyone dreads it and whenever it is mentioned you can find hundreds of reasons to do other things until your teeth start to hurt.

Well, I have a great dentist for you and your Sql database. The product is called APEX SQL. First, before I give you a review and how to use it, there are few points that I have to make. The product only works if you have a normalized database with proper indices, primary keys/foreign keys, and good object naming conventions. If your database does not conform to a good database standard, then documentation is going to hurt because the nice dentist won’t work on your teeth until you start brushing and flossing on a regular basis. Don’t worry, I will tell you how to make your database conform to a standard good enough to get you in the chair.

You might be thinking there aren’t any real standards out there especially for Sql. You are right, so here are a few rules that I use when creating a database.

1. 3^rd form of Normalization – I try to adhere to this rule to manage the size of my database as well as ensure I only store my data once.
2. Avoid protected naming – Sql uses prefixes to identify its objects verses objects created by a user. For example you should never prefix your stored procedure with a sp_ because all of the system stored procedures will have the same prefix. The impact is that whenever you call your stored procedure, Sql will look through its own objects first and then to the user objects to execute the object. That might not seem like a big deal, but if your database becomes sizable it will become a big deal. There are enough letters and characters available…use your own.
3. Primary and Foreign Keys – In a relational database there should be primary and foreign keys assigned. This tells the database how tables are related to one another. It is the core component of creating an ERD (entity relationship diagram).
4. Indexes – In a relational database, there should be an index on any table that stores large amounts of data. There are different conversations out there about the blessing and curse of indexes. If they are done correctly, they are a blessing because it will speed up the result delivery but may increase the size of your database. The curse is if you index on columns that don’t reduce the need for a full table scan, you have simple made the database larger without speeding up result delivery.

Now that we have some basic standards in place, we can talk about how Apex Sql will help with documentation. It is a server side installation, which means it has to be installed on the same server where the Sql databases exist. Once you have it installed, you can use the wizard and it will go and read every object in the database. If you followed my basic rules, it will create fantastic documentation for you in less time it would take you to rinse out your mouth at the dentist! After it is done, you can use a window scheduler to create your documentation on a routine. How nice it that?! Now, whenever you change your database your documentation is updated automatically.

The best part of Apex Sql is that you can try it before you buy it. Take it for a test run and see how easy it will make your life. Remember that you must have the basics covered in your designed or it won’t work as well.

Everyone will be impressed that your database documentation is always up to date. Get ready for everyone to compliment on your nice smile!