I was just thinking, it's about time I talked about SQL Server on my blog LOL

Here are 5 tips for creating well designed tables in Microsoft SQL Server.  They are in no particular order and it's far from an exhaustive list, but it makes for a good standard I think.

1.  Keep your tables narrow:  I don't just mean about the number of columns per-table, but also the width of those columns.  Normalization is your friend here.  I won't go into it here yet, but shoot for 3nf by default. 

By keeping an eye on table width and keeping your rows as narrow as you can you will get more rows on a page which will help you in case a query misses and index and requires a table scan.

2.  Add a deleted bit column to your tables.  Design your tables with an extra column called "deleted" or something like that.  Instead of removing the row from the table if there is a delete, set this flag = True. 

Some reasons you should do this are:

• It opens up more options for implementing an undelete function and it helps archiving/unarchiving old data.
  
• It's non-destructive!
  
• It helps index fragmentation.
  

3.  Maintain aggregate tables.  This one probabbly applies more to transactional system than others but by using maybe triggers or a scheduled job to maintain seperate tables that store common aggregates for your main tables you will for make your reports more efficient and can simplify queries and joins. 

Two things to remember: 

• These tables may not be indexed the same as the transactional tables, but this is a good thing.  Choose your indexes on these tables to suit exactly the queries you need.
• If you require the aggregate tables to be absolutely up-to-date with the main data tables then using triggers may be the best solution but don't over do it!  Triggers can be costly and have an adverse effect on transaction log throughput.
  

4.  Maintain created and modified timestamps on each row.  Design your tables with columns called created and deleted and store the timestamp of when the row was created and also the last time the row was modified.  Do this as well as an audit trail. 

This is the kind of gift that just keeps giving!  Some examples:

• Once your little back room app has 'grown up' and now needs to feed the data warehouse?  No problems! 
  
• Need to validate your aduit log or a point-in-time restore?  No problems.
  
• Users always love to see these details on the screen, or give your app a history feature like a web browser has so users can backtrack to what they were doing before the phone rang?  No problems.
  

5.  Don’t allow null unless it actually valid.  Don't be scared of Null values, but only allow them where they make sense.  Remember Null doesn’t equal "" or 0 - it means undefined. 

Consider the mod timestamp (from point 4 above) on a newly created row.  It has never been modified so Null actually makes sense.  The worst thing to do to these kinds of fields is to use "magic" values to avoid null columns,  for example giving "01/01/1970" the special meaning of "unknown" is just a waste of effort and makes your database usable only by applications that know these specific facts, seriously harming interoperability.  Also consider a table that joins to itself to represent a hierarchy - null can be valid in the parent field.

That said, don't allow nulls anywhere that they don't make sense.  If you find yourself thinking about optional fields consider normalizing, or using an attribute-collection pattern instead!   The thing to avoid is creating tables that has a column for each of the potential fields.  The typical example is having one field for each of business phone, business direct line/ext, home phone, mobile phone, fax number - each of them  with the same validation rules, and most of the rows  don't fill all columns!

So there you go!  5 steps closer to peace in the database world!