|
|
Posted by Erland Sommarskog on 10/02/52 11:54
binder (rgondzur@gmail.com) writes:
> binder wrote:
>> I am designing a new table with a few columns that may or may not have
>> a value on each row that is inserted.
>>
>> What issues determine whether to allow a NULL value to be inserted for
>> that column or define a default value to be used?
>>
>> I want to think through the repercussions of this decision before I get
>> into production.
>
> From a programmatic standpoint, if I have a column that may or may not
> have a value, is it better to insert a default value that indicates no
> value was entered, such as 0 for a userid, or insert a NULL value?
Programmatic? That's the wrong standpoint to look at it. You should look
at what it means.
Say that you have a column called whotoblameusrid, and no explicit value
is inserted. If you let it be NULL, means that in this case there is
no one to blame. (After all, anyone who is acquainted with Elvis Costello's
early material knows that Accidents can Happen.) If you use a default
value of 0 and 0 is Cain's user id, this mean that we Blame it on
Cain when no one else is at fault. (Costello fans know what I'm talking
about.)
But must 0 be a certain user? Yes, because good database design says
that a userid should be a foreign key to a table that defines users,
so there must be a user with id 0.
This also applies to non-key columns. Say a column that represents
an amount, for instance the cost for something. NULL would indicate
that the price is unknown (and we probably should not sell it). 0
means that the goods is for free.
That is not to say that default values should not be used. For instance
if you open a new account, it makes perfect sense to have default of
0 for the holdingsamt column, because you start with 0 and you may
not make a deposit immediately.
Simply, having NULL or a default value depends on what not entering a
value means. And by the way, a column could permit NULLs, but still have
a default value, because it's only exceptional that the value is not
known. For instance, a column "citizenof" could very well have the
default value of SE for a Swedish system, but the column must permit
NULL to account for stateless persons.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|