database design

Gabriel Gunderson gabe at gundy.org
Thu Jun 9 12:30:19 MDT 2005


On Thu, 2005-06-09 at 11:45 -0600, Steve Dibb wrote:
> I'm working on creating a new table in our database, and I'm wondering 
> -- why bother with the possibility of NULL values in a field?  It seems 
> like it always adds more work then its worth.
> 
> What's the benefit of adding the option of IS NULL when you usually have 
> to check to see if its also blank (field = '' OR field IS NULL)?
> 
> Steve

Well, generally NULL-able is the default behavior.  To further constrain
the data you can make it "NOT NULL"  That helps you know more about your
data as it forces more structure.  Sometimes, this is good.

If this can be overcome by entering an empty string then you really
don't gain anything.  In a DB like PostgreSQL you could use the "rules
system" to rewrite that INSERT or UPDATE to make an "" -> "UNKNOWN" or
whatever suits your needs.

It's a delicate balance.  If you force data to be there that is not
always available then you are going to get junk data just to satisfy the
constraint.  Think about how many times you have filled in an EMAIL
field on a web page that *required* you to have it with something like
"mindyourbizjerk at yourcompanystinks.crap".  If they didn't require it
they wouldn't have got "junk in" and then in time got "junk out".

The moral is that the integrity of your data is always more important
then simplifying the SELECT statements.  Both NULL and NOT NULL are
useful and have their place.  Let the data determine which to go with.

One man's opinion...

Gabe




More information about the PLUG mailing list