Default VS NULL

  • Thread starter Thread starter Janaka
  • Start date Start date
J

Janaka

I'm designing a table which has a foreign key field which may be nullable.
This is the only field in the table that may be NULL and I prefer to use a
default value instead if that's possible.
The field is an int so I was thinking of making a default value of 0. In my
sql statements I would use a LEFT JOIN on the associated table and then use
ISNULL() on any joined columns.

Is this a good design approach or should I stick with making it NULL?
 
Janaka,

Null means not used,
0 means somebody has placed a 0 in it, so the value is used, when you set a
0 in it gives a more actual idea than it is.

I assume that you see what I prefer

Cor
 
I was thinking more in terms of performance. I've been told having Null
impedes performance in a table overall. I know no identifiers will have the
value 0 so I could use that in place of Null.
 
If the field goes to a lookup table, I would say your approach works if you
place a 0 value (unknown?) in the lookup table. If not, default only makes
sense if there is a real default. I would leave it null if there is no
reasonable motivation other than not wanting a null field.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
You can safely assume that more than 50% of project failures are caused by
the search of the *ultimate* performance before the search of making
something that will simply work.

When you have a project to do, first you should try to make something that
will work; second that will work without to many bugs and third, that will
work without to many modifications asked by the client.

After that and if the project is still alive and you are still working on it
and you have nothing else to do, then you could take the time to try
squeezing a little more performance from it but personally, I never had the
occasion to do this in the past 20 years.

S. L.
 
In this case it would break referential integrity ?

Another option would be to add a record in your codification table with a
particular pk such as 0 and who as a label such as "Unknown". This way you
can disallow null, user "see" the value is not known and referential
intregrity is preserved...

Patrice
 
Back
Top