T-SQL Database Design

The importance of template tables and standards...

Something that I always like to do now when I have the pleasure of creating a database design from scratch rather than work on a design that either from years of compounded lack of foresight or care is to insist on a system wide template for a table.

Usually on OLTP I add five fields at the end of every table. These include a timestamp and user identity of both the last update and initial insert. The final field is a status field which in the majority of systems I have worked is usually required. At the very least it usually holds a status value of deleted or active. If for some reason it is not required then it can be removed. However in my experience this is rarely the case.

The reason I do this is partly to speed up the time to develop an application but probably more importantly for maintenance. Having a standard table design is a godsend when you have developed five hundred tables in your database and a fellow developer is asking you how a piece of code interacts works.

Learn how to hand code...

As with HTML and practically any other language developing has been made simple with the introduction of GUI’s and wizards which hide any complexity involved.

However I think when developing a database you should at least know how to create your DDL without the need for the design tools. Funnily enough once you know you will probably never feel the need to use these ingenious helper tools again.

Lessons in design and being too clever...

I've been writing T-SQL now probably for the best part of 10 years. And before that I worked on Informix 4GL and and before that Access. During this time I have have had the experience of developing and bug fixing lots of differently designed systems. At every assignment undertaken you will always come across something neat or clever that you can take to your next project. However for every trick you would like to keep there is often two or three things that make you curse.

Probably the worst code I have ever had the pleasure to work on and without naming names was for a software house that developed a standard ASP.NET application that connected to a SQL Server back end. The database it interacted with had it's standard set up of stored procedures, views, functions and tables. Some of the stored procedures however because of years of adding functionality ended up being greater that 6000 lines each. Obviously this was not the initial intention but just happened when you try to fix rather than refactor. Trying to maintain it was impossible. By the time you worked out what the bottom part of the procedure was doing you had forgootton what the top part was trying to achieve. The answer here is to refactor when you can as you develop. Also remember small is often best.

The other system I worked on which I found equally as difficult was a system that was originally designed by a C++ programmer who thought T-SQL was just a just an easy scripting language. The end result was a relational database system built to an object orientated design. This after a while became inflexible and performed badly when scaleability became an issue.

So in summary when desiging a system remember keep your procedures short and manageable but most importantly design a system in a relational way and forget trying to produce an OO inspired monster.

Last updated by admin1 on 7/27/2009