|
|
Posted by David Portas on 10/02/07 11:54
Erland Sommarskog wrote:
>
> 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
Don't forget the third option: decompose the optional attribute(s) into
another table. To extend Erland's example, the WhoToBlameUsrID can go
in a table along with any other columns that relate only to Blame.
CREATE TABLE Who (WhoID INT NOT NULL PRIMARY KEY /* ... The required
attributes for the Who table ... */);
CREATE TABLE Blame (WhoID INT NOT NULL PRIMARY KEY REFERENCES Who
(WhoID), WhoToBlameUsrID INT NOT NULL /* ... The optional "Blame"
attributes ... */);
The principle at work here is that an entity is determined by its
unique set of attributes. If you analyse the functional dependencies
you find you have more entities than you currently have tables for -
that's what tells you to decompose.
In SQL Server this approach has one special advantage. SQL Server's
UNIQUE constraint treats nulls like values. The constraint doesn't
permit nulls to be duplicated, which means that unique constraints are
of limited use for optional attributes. So if an optional column may
need to be part of a unique constraint you should certainly consider
the decomposition approach.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|