Skip to main content
Inspiring
Dec 12, 2006 4:52 PM UTC
Question

DB: 'proper' way of handling null fields and dates?

I need to store a date in a table. The date might be anything, including
non-existent.

Is it better to store the date in one field, and check to see if it's null,
or is it better to use two fields...one for the date, and the other to
'flag' records that shouldn't have a date.

Obviously the former will work, but you always have to deal with the
sometimes messy issue of properly checking for nulls and properly inserting
nulls. Having a separate bit field seems like a cleaner way to handle it,
but I'm not sure if that's a faux paux to have that extra field when,
technically, I don't REALLY need it.

-Darrel

--
=================================================================
Win prizes searching google:
http://www.blingo.com/friends?ref=hM72-GU3FWzMFnTqhv-2GE1FNtA


This topic is closed to new replies. Start a new post to keep the conversation going.

4 replies

Inspiring
Dec 12, 2006 5:24 PM UTC
> So ... in the end it doesn't matter because someone will always tell you
> you're wrong. :)

That's the problem with programming... ;o)

-Darrel


Translate
Inspiring
Dec 12, 2006 5:24 PM UTC
> Use a default, "NA", 00000000, for examples.

Not sure what you mean. Are you referring to useing an isnull() upon getting
the data to put in a dummy value?

-Darrel


Translate
Inspiring
Dec 12, 2006 5:10 PM UTC
darrel wrote:

> I need to store a date in a table. The date might be anything, including
> non-existent.
>
> Is it better to store the date in one field, and check to see if it's null,
> or is it better to use two fields...one for the date, and the other to
> 'flag' records that shouldn't have a date.
>
> Obviously the former will work, but you always have to deal with the
> sometimes messy issue of properly checking for nulls and properly inserting
> nulls. Having a separate bit field seems like a cleaner way to handle it,
> but I'm not sure if that's a faux paux to have that extra field when,
> technically, I don't REALLY need it.
>
Use a default, "NA", 00000000, for examples.
Mick
Translate
Inspiring
Dec 12, 2006 5:05 PM UTC
You'll hear it both ways. A lot of people (including me) use a NULL to
indicate that a value is not applicable in addition to its canonical use as
a placeholder for an unknown value. Then there are purists for which NULL
always means 'unknown', which also implies that there is a value, it's just
not (yet) known. Then there are those who would tell you that if your
column doesn't apply for all rows, that your design is wrong.

So ... in the end it doesn't matter because someone will always tell you
you're wrong. :)

But ultimately, your business rules are paramount. Without knowing what the
date in question is, I would assume that there is a column or combination of
columns that indicates a record type or state, and that you could infer from
the record type or state whether or not the date applies. In that case,
leaving the date NULL when it's unknown or not applicable would be the norm
and there's no need for a special column just to indicate that the date
applies.


"darrel" <notreal@nowhere.com> wrote in message
news:elmmor$4bs$1@forums.macromedia.com...
>I need to store a date in a table. The date might be anything, including
>non-existent.
>
> Is it better to store the date in one field, and check to see if it's
> null, or is it better to use two fields...one for the date, and the other
> to 'flag' records that shouldn't have a date.
>
> Obviously the former will work, but you always have to deal with the
> sometimes messy issue of properly checking for nulls and properly
> inserting nulls. Having a separate bit field seems like a cleaner way to
> handle it, but I'm not sure if that's a faux paux to have that extra field
> when, technically, I don't REALLY need it.
>
> -Darrel
>
> --
> =================================================================
> Win prizes searching google:
> http://www.blingo.com/friends?ref=hM72-GU3FWzMFnTqhv-2GE1FNtA
>


Translate