Document Your Database

, 2008-01-02 (first published: )

Introduction

Documentation for the tasks related to a particular project is required for easy maintenance and quick reference. Number of situations has been encountered where we all go through user-manuals or read-me files for getting help/solutions. Most of the applications are having documents that provide in-depth description of the design, code and maintenance related stuff. Most of these documents are for the development team but some of them are also provided to the customers.

Consider a scenario:

You are currently involved in designing a database i.e., created tables, defined constraints, written various scripts, jobs, etc. One fine day, you got an opportunity to work with Microsoft (Nice opportunity, isnt it!!!). The company for which you are working has already hired your replacement. Doing transition or knowledge transfer becomes tuff and boring. So, you try to get rid it as fast as possible and in turn making the life of your REPLACEMENT miserable.

There will be documents written when the database design was discussed and some of them include the database design in detail. But, some way or the other we miss out on small things that are very crucial, specially the updates or modifications to the database objects. Also, the documents that are created are not concentrated on the complete database design. For example:

  • Why a particular table is created?

  • What is the need of a particular column?

  • What is the use of XYZ stored procedures?

  • What is done when is a particular job executed?

These are some mystery questions that will be un-answered. Unless the REPLACEMENT takes the initiative and start digging in the database, the mystery would not be solved.

How to Document?

There are various tools available that can be used to document your database. The best is Microsoft Word. Instead of writing the overview of the database and the storing the sql-scripts in the documents, it is a good practice to write down the details about each tables, columns, stored procedures, views, jobs and other database objects. Still, this method demands regular update of the documents as and when the design or functionality changes are made.

Extended properties can also be used to document individual database objects. There are various stored procedures which allows to add and modify details.

Using SQL Server 2005 as your database will be useful, as it provides a good way to add description to all the database objects when they are created. You can specify description for each column [also in SQL Server 2000]. In SQL Server 2005, it is also possible to add description for tables, stored procedures and other database objects. It is helpful because you can get the information instantly without searching a document.

There are tools available that will run through your database and give output as complied-html file or normal html pages. The output is well categorized and contains all the database objects, with their descriptions. I have tried ApexSQL Doc, and it is really helpful. It provides a good user-interface for selectively documenting various database objects.

Conclusion

Documenting the database perfectly up to the column level is necessary and a good practice so that the design can be better understood. Also, there are some instances when the designer of the database is not sure about the existence of a particular table/column/script. Putting little extra effort by specifying descriptions for the database objects will make life easier for everyone.

Rate

1.61 (18)

Share

Share

Rate

1.61 (18)

Related content

Is XML the Answer?

New Author! Don Peterson writes his first article for us and explores why he considers XML to be...bad! There are some interesting points made here and if you've haven't thought about what XML means to you as a DBA, it's a subject worth spending some time on.

4.77 (79)

2008-05-02 (first published: )

64,236 reads

Normalizing Dates

Jon has been off in the land of C# and web apps for a while, but a recent project has him investigating OLAP and then coming up with a custom solution that met his needs. Jon helped us get this site off the ground, good to have him back for a guest appearance!

2003-09-05

8,189 reads