nulled out - defaults instead?

OK, I'm nulled out.
I am converting a dbase based enterprise wide system that has almost 100
tables to convert into a vb .net system using ms sql server 2000 (I am
posting this both to ado .net newsgroups and sql server newsgroups). I am
working with a prototype where many of the columns in many of the tables
allow nulls. But often I can't call ... is null (in tsql) or isdbnull(...)
in vb .net on the same line as, say, 'or len(trim((dddd)) < 1' because this
throws an error if the col is null, since you can't measure anything when a
col is null.
Now null might have a place in the universe - like black holes - but not
being Stephen Hawkings I just don't know what that place is. But in vb .net
especially and to some extent in tsql also, it's just a pain in the ...
My question - is there any reason I shouldn't convert into tables where,
when the data is converted if it's empty or 0 (int) or # / / # (date), I
use defaults instead (eg, "", 0, 01/01/1900 respectively)? Do I lose
anything by doing this?
Tx for any help.
Bernie YaegerBernie,
Although I belong to the 'avoid nulls at all costs' camp,
you do lose something with defaults.
For example, 1/1/1900 is a valid date in many systems, so
using that date as the default will cause logical
problem. Likewise, a zero may (or may not) cause
problems when used instead of a NULL.
Think of the difference with summing a zero into an
aggregate vs taking an average. In the sum, you can
ignore the zero defaults since they are harmless, but
with the average you have to decide whether to include
them or not.
The key is how your application is coded. If you code
for defaults from the start, there are few difficulties
in making it all work smoothly since you will choose your
defaults and define how they are to be interpreted.
However, from the examples above, any code that includes
the default code in the domain of legal values can cause
you grief.
Russell Fields
>--Original Message--
>OK, I'm nulled out.
>I am converting a dbase based enterprise wide system
that has almost 100
>tables to convert into a vb .net system using ms sql
server 2000 (I am
>posting this both to ado .net newsgroups and sql server
newsgroups). I am
>working with a prototype where many of the columns in
many of the tables
>allow nulls. But often I can't call ... is null (in
tsql) or isdbnull(...)
>in vb .net on the same line as, say, 'or len(trim
((dddd)) < 1' because this
>throws an error if the col is null, since you can't
measure anything when a
>col is null.
>Now null might have a place in the universe - like black
holes - but not
>being Stephen Hawkings I just don't know what that place
is. But in vb .net
>especially and to some extent in tsql also, it's just a
pain in the ...
>My question - is there any reason I shouldn't convert
into tables where,
>when the data is converted if it's empty or 0 (int) or
# / / # (date), I
>use defaults instead (eg, "", 0, 01/01/1900
respectively)? Do I lose
>anything by doing this?
>Tx for any help.
>Bernie Yaeger
>
>.
>|||Allowing NULLS is a design decision that only you can make. IMHO, tri-state
logic is a pain because you often have to exclude data that make no logical
sense. I strongly prefer NOT NULL and default values in columns since it
simplifies the logical conditions. Just make sure your default values are
meaningful in your system and your application knows what to do with them.
NOT NULL and defaults help later when you maintain your application since
you can add columns without having to change existing code. Whatever choice
you make, apply it consistantly and document the few times when you
absolutely must deviate from the standard.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:JIPZa.40432$_R5.12322590@.news4.srv.hcvlny.cv.net...
> OK, I'm nulled out.
> I am converting a dbase based enterprise wide system that has almost 100
> tables to convert into a vb .net system using ms sql server 2000 (I am
> posting this both to ado .net newsgroups and sql server newsgroups). I am
> working with a prototype where many of the columns in many of the tables
> allow nulls. But often I can't call ... is null (in tsql) or
isdbnull(...)
> in vb .net on the same line as, say, 'or len(trim((dddd)) < 1' because
this
> throws an error if the col is null, since you can't measure anything when
a
> col is null.
> Now null might have a place in the universe - like black holes - but not
> being Stephen Hawkings I just don't know what that place is. But in vb
.net
> especially and to some extent in tsql also, it's just a pain in the ...
> My question - is there any reason I shouldn't convert into tables where,
> when the data is converted if it's empty or 0 (int) or # / / # (date),
I
> use defaults instead (eg, "", 0, 01/01/1900 respectively)? Do I lose
> anything by doing this?
> Tx for any help.
> Bernie Yaeger
>|||Hi Russell,
Tx for your advice; I have considered some of the issues you raise and know
the consequences and how to deal with them, I believe.
Bernie
"Russell Fields" <rlfields@.sprynet.com> wrote in message
news:008101c36030$3efd7300$a501280a@.phx.gbl...
> Bernie,
> Although I belong to the 'avoid nulls at all costs' camp,
> you do lose something with defaults.
> For example, 1/1/1900 is a valid date in many systems, so
> using that date as the default will cause logical
> problem. Likewise, a zero may (or may not) cause
> problems when used instead of a NULL.
> Think of the difference with summing a zero into an
> aggregate vs taking an average. In the sum, you can
> ignore the zero defaults since they are harmless, but
> with the average you have to decide whether to include
> them or not.
> The key is how your application is coded. If you code
> for defaults from the start, there are few difficulties
> in making it all work smoothly since you will choose your
> defaults and define how they are to be interpreted.
> However, from the examples above, any code that includes
> the default code in the domain of legal values can cause
> you grief.
>
> Russell Fields
>
> >--Original Message--
> >OK, I'm nulled out.
> >
> >I am converting a dbase based enterprise wide system
> that has almost 100
> >tables to convert into a vb .net system using ms sql
> server 2000 (I am
> >posting this both to ado .net newsgroups and sql server
> newsgroups). I am
> >working with a prototype where many of the columns in
> many of the tables
> >allow nulls. But often I can't call ... is null (in
> tsql) or isdbnull(...)
> >in vb .net on the same line as, say, 'or len(trim
> ((dddd)) < 1' because this
> >throws an error if the col is null, since you can't
> measure anything when a
> >col is null.
> >
> >Now null might have a place in the universe - like black
> holes - but not
> >being Stephen Hawkings I just don't know what that place
> is. But in vb .net
> >especially and to some extent in tsql also, it's just a
> pain in the ...
> >
> >My question - is there any reason I shouldn't convert
> into tables where,
> >when the data is converted if it's empty or 0 (int) or
> # / / # (date), I
> >use defaults instead (eg, "", 0, 01/01/1900
> respectively)? Do I lose
> >anything by doing this?
> >
> >Tx for any help.
> >
> >Bernie Yaeger
> >
> >
> >.
> >|||With due respect to my colleagues, I fall into the 'Use Nulls when
necessary' camp... if you do not know the value, you do not know... Tri
state logic is a little more difficult, but I prefer the data to represent
reality...
And this is something where 'reasonable people differ' in their opinions...
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:JIPZa.40432$_R5.12322590@.news4.srv.hcvlny.cv.net...
> OK, I'm nulled out.
> I am converting a dbase based enterprise wide system that has almost 100
> tables to convert into a vb .net system using ms sql server 2000 (I am
> posting this both to ado .net newsgroups and sql server newsgroups). I am
> working with a prototype where many of the columns in many of the tables
> allow nulls. But often I can't call ... is null (in tsql) or
isdbnull(...)
> in vb .net on the same line as, say, 'or len(trim((dddd)) < 1' because
this
> throws an error if the col is null, since you can't measure anything when
a
> col is null.
> Now null might have a place in the universe - like black holes - but not
> being Stephen Hawkings I just don't know what that place is. But in vb
.net
> especially and to some extent in tsql also, it's just a pain in the ...
> My question - is there any reason I shouldn't convert into tables where,
> when the data is converted if it's empty or 0 (int) or # / / # (date),
I
> use defaults instead (eg, "", 0, 01/01/1900 respectively)? Do I lose
> anything by doing this?
> Tx for any help.
> Bernie Yaeger
>|||Just to clarify, I am not religiously opposed to using Nulls. I just think
the places where they apply are very few. If you have properly represented
Entity Relationships in your database, either you know about an entity or
that entity doesn't exist. Thus, you have a complete row in an appropriate
table or no row in that table.
Since most of us work in the real world where we have to live with inherited
databases, Nulls are a fact of life. I prefer to choose where I use nulls
and treat them as the exception rather than the rule. Again, this is just a
personal design preference and should not be taken as holy writ.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:#63VdaLYDHA.384@.TK2MSFTNGP12.phx.gbl...
> With due respect to my colleagues, I fall into the 'Use Nulls when
> necessary' camp... if you do not know the value, you do not know... Tri
> state logic is a little more difficult, but I prefer the data to represent
> reality...
> And this is something where 'reasonable people differ' in their
opinions...
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:JIPZa.40432$_R5.12322590@.news4.srv.hcvlny.cv.net...
> > OK, I'm nulled out.
> >
> > I am converting a dbase based enterprise wide system that has almost 100
> > tables to convert into a vb .net system using ms sql server 2000 (I am
> > posting this both to ado .net newsgroups and sql server newsgroups). I
am
> > working with a prototype where many of the columns in many of the tables
> > allow nulls. But often I can't call ... is null (in tsql) or
> isdbnull(...)
> > in vb .net on the same line as, say, 'or len(trim((dddd)) < 1' because
> this
> > throws an error if the col is null, since you can't measure anything
when
> a
> > col is null.
> >
> > Now null might have a place in the universe - like black holes - but not
> > being Stephen Hawkings I just don't know what that place is. But in vb
> .net
> > especially and to some extent in tsql also, it's just a pain in the ...
> >
> > My question - is there any reason I shouldn't convert into tables where,
> > when the data is converted if it's empty or 0 (int) or # / / #
(date),
> I
> > use defaults instead (eg, "", 0, 01/01/1900 respectively)? Do I lose
> > anything by doing this?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>