This article stems from a interesting event on a project I was working on

recently. Overall it's about defaults, but as you'll see it's just as much about

design principles and communication.

I did the database design consisting of about 50 tables, driven by what I'd

consider to be fairly standard design goals:

• Normalize the design (reasonably!)
• Avoid nulls as much as possible
• Date stamp rows where appropriate
• Provide default values where they make sense
• etc, etc

I also had a light data dictionary where I documented the purpose of each

table along with the column names and their purposes as well. For those that are

wondering, I built my dictionary by hand in Word. I have no real objection to

using the built in extended properties that can be used for the same purpose,

but I didn't have a tool handy to generate a doc from the properties and

maintaining the doc manually wasn't a lot of work for a project this size. 

I also documented some of the standards to be used and how some of the

business processes would work.

Now going back to the design principles, I hope most of you would agree with

me at least on normalizing and avoiding nulls. Date stamping (requiring you to

track at least the date the record was added to the table) isn't uncommon, but

in my experience tends to be under used in table design. I'm not trying to build

a full auditing solution as I will typically implement that with triggers once

the design has stabilized, I just like to know when rows were added. For the

tables where I felt a date stamp was needed I used a datetime data type

(arguably in some of these cases smalldatetime would have been sufficient, but

we'll save that for another day) and a default of GETUTCDate().

Note: After using getdate() for much of my career, in the past couple

years I've started to use getutcdate() instead. For those of you who may not

have used them, getdate() returns the local server time and getutcdate() returns

universal (GMT) time. If you're running a single server you can use either,

though getdate() is probably more comfortable since the time you see in your

queries will match the time on your watch. Once you start crossing time zones on

multiple servers you'll start to find getutcdate() more interesting.

I also listed 'provide default values where they make sense' as a design

goal. For example, in this project there is a business process where users start

out as unverified, may get moved to a status of unverified (follow up sent), and

hopefully to a status of verified. I prefer numbers over text for this kind of

situation, so I had a look up table where:

• 0 = Unverified
• 1 = Verified
• 2 = Unverified (Follow up sent)
• 3 = Expired (Can be deleted)

In the users table I set the default to zero. It's not that I couldn't or

wouldn't ever add a user directly with a status of verified, but in the standard

business flow I knew that users would start at zero and by setting it as a

default, I could save passing in one value to a stored procedure and also

indirectly enforce the workflow I had in mind by making sure users started at

the correct status.

I was fortunate enough to be able to outsource some of the early data access

work to a contractor I had worked with many times before and I provided him with

a copy of my docs. Some time later after that initial work was done I was doing

some testing and was not getting the expected results. Digging in to the problem

a bit I found that the test row I had added and expected to see was being

excluded due to a where clause built around the dateadded column. Looking a

little further I finally found the problem in the stored procedure used to add

the row, he had explicitly specified getdate()!

Talk about an "aha" moment. My first thought was that using GetDate() made

sense in his context. Most of us use it routinely and he had worked with me

before when I used it, and of course I had not documented that I was using UTC

for dates throughout. So my fault, and easy enough to fix.

But wait, there's more. Even if he had used GetUTCDate, to me it would have

been bad coding. The column had a default and it would never make sense outside

of some type of administrative action to enter a different value for the date

the record was added, so I would have just omitted the column from the insert

altogether. A middle of the road approach would have been to use the DEFAULT

keyword as the value for the date. The worst part is that if I changed the

default on the table, perhaps to reflect a change in the business approach, the

stored procedure would still be implementing the original behavior.

I think that we wind up with four possible philosophies:

• Defaults are set for a reason, overriding is treason! Columns with

  default values should be excluded from all insert statements

• Defaults are ok, you can include columns with defaults in your insert,

  but if you truly want the default value the correct way is to use the

  DEFAULT keyword

• Defaults are evil. Most likely you would set the column as not null and

  force whoever is writing the insert to do the homework required to figure

  out what a good initial value is or where it should come from.

• Chaos, where everyone does it however they like on the project!

Aside from avoiding chaos, I'm not sure which to recommend. I've used

defaults for a long time and am used to working that way, but is it helpful or

confusing behavior? Or is it just a matter of setting the standards up front so

there is no collision of philosophies? One downside to not using defaults is you

would no longer be able to do "insert into table default values" which I find

useful when generating a few rows for testing, but have never used in production

code. You could enforce defaults via an instead of trigger, but that seems like

a lot of work that you may regret at some point anyway.

Is it a Zen question, or is there really a right answer? I'm looking forward

to your comments so that we can figure it out together!