0
DB: 'proper' way of handling null fields and dates?
LEGEND
,
/t5/dreamweaver-discussions/db-proper-way-of-handling-null-fields-and-dates/td-p/740553
Dec 12, 2006
Dec 12, 2006
Copy link to clipboard
Copied
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
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
TOPICS
Server side applications
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
/t5/dreamweaver-discussions/db-proper-way-of-handling-null-fields-and-dates/m-p/740554#M106583
Dec 12, 2006
Dec 12, 2006
Copy link to clipboard
Copied
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
>
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
>
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
/t5/dreamweaver-discussions/db-proper-way-of-handling-null-fields-and-dates/m-p/740555#M106584
Dec 12, 2006
Dec 12, 2006
Copy link to clipboard
Copied
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
> 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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
/t5/dreamweaver-discussions/db-proper-way-of-handling-null-fields-and-dates/m-p/740556#M106585
Dec 12, 2006
Dec 12, 2006
Copy link to clipboard
Copied
> 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
Not sure what you mean. Are you referring to useing an isnull() upon getting
the data to put in a dummy value?
-Darrel
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Newsgroup_User
AUTHOR
LEGEND
,
LATEST
/t5/dreamweaver-discussions/db-proper-way-of-handling-null-fields-and-dates/m-p/740557#M106586
Dec 12, 2006
Dec 12, 2006
Copy link to clipboard
Copied
> 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
> you're wrong. :)
That's the problem with programming... ;o)
-Darrel
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

