Issue when not storing null db values.

Hello:
Because of the possible issues associated with storing null values in the
db, the development team of which I am a part opted to avoid using them
when possible. This has resulted in an interesting issue -
Because we are using surrogate GUID values as primary keys, if a foreign key
value does not point to a related record, an "empty" or "not selected" value
must be stored. To accomplish this requirement we are representing empty
records with the GUID value '{00000000-0000-0000-0000-000000000000}'. So
far so good.
But here's the catch. Because we allow the database to enforce ref
integerity, a record must be created in the referenced primary key table
that corresponds with the empty record. Any any foreign key references in
our "empty" record must also contain empty guid values, which means that
empty records must be created in those related tables, etc. etc. etc.
Now, all of this seems to be working but it can be rather tedious to
maintain all of this additional data - almost all of our tables must now
have contain an "empty" record. Has me wondering if we may have made a
wrong turn at Albaquerke.
Any thoughts? Have others encountered a similar issue? Just want to make
sure we are on the right track . . .
Thanks,
Chris> Has me wondering if we may have made a wrong turn at Albaquerke.
Yea - turn back before you get to the border.
Albeit NULLs can be a pain, NULLs are certainly appropriate for unknown or
unspecified values. Too many nulls can be an indicator of a db design
issue. You should never need to create a 'dummy' row because you are using
a non-null value as a FK. That's an easy but seriously flawed kludge.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:OaWrwqz$FHA.360@.TK2MSFTNGP09.phx.gbl...
> Hello:
> Because of the possible issues associated with storing null values in the
> db, the development team of which I am a part opted to avoid using them
> when possible. This has resulted in an interesting issue -
> Because we are using surrogate GUID values as primary keys, if a foreign
> key value does not point to a related record, an "empty" or "not selected"
> value must be stored. To accomplish this requirement we are representing
> empty records with the GUID value
> '{00000000-0000-0000-0000-000000000000}'. So far so good.
> But here's the catch. Because we allow the database to enforce ref
> integerity, a record must be created in the referenced primary key table
> that corresponds with the empty record. Any any foreign key references in
> our "empty" record must also contain empty guid values, which means that
> empty records must be created in those related tables, etc. etc. etc.
> Now, all of this seems to be working but it can be rather tedious to
> maintain all of this additional data - almost all of our tables must now
> have contain an "empty" record. Has me wondering if we may have made a
> wrong turn at Albaquerke.
> Any thoughts? Have others encountered a similar issue? Just want to make
> sure we are on the right track . . .
> Thanks,
> Chris
>
>
>|||Wow...GUID's for primary key values!!!
You better hope that you never have to match those up by hand when debugging
an application.|||Thanks for your input, Dan. Will have to consider other options.
This brings up a related issue. Generally, are "1 to 0-1" relationships
better handled with a nullable foreign key or should the "zero/one" data be
stored in a separate table so that data will only be stored if applicable?
Chris
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Ovz$G1z$FHA.3928@.tk2msftngp13.phx.gbl...
> Yea - turn back before you get to the border.
> Albeit NULLs can be a pain, NULLs are certainly appropriate for unknown or
> unspecified values. Too many nulls can be an indicator of a db design
> issue. You should never need to create a 'dummy' row because you are
> using a non-null value as a FK. That's an easy but seriously flawed
> kludge.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
> news:OaWrwqz$FHA.360@.TK2MSFTNGP09.phx.gbl...
>|||> This brings up a related issue. Generally, are "1 to 0-1" relationships
> better handled with a nullable foreign key or should the "zero/one" data
> be stored in a separate table so that data will only be stored if
> applicable?
Perhaps I don't understand your question correctly so you may want to post a
DDL example. It seems to me that one has a nullable FK then the
non-mandatory data are already in a separate table.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:ObNCj$2$FHA.356@.TK2MSFTNGP12.phx.gbl...
> Thanks for your input, Dan. Will have to consider other options.
> This brings up a related issue. Generally, are "1 to 0-1" relationships
> better handled with a nullable foreign key or should the "zero/one" data
> be stored in a separate table so that data will only be stored if
> applicable?
> Chris
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Ovz$G1z$FHA.3928@.tk2msftngp13.phx.gbl...
>|||diagram 1:
Parents
--
ParentID
ParentDescription
ChildID (nullable)
Children
--
ChildID
ChildDescription
diagram 2:
Parents
--
ParentID
ParentDescription
ParentChildren
--
ParentID
ChildID
Children
--
ChildID
ChildDescription
Dan, I guess diagram 2 would illustrate what I was trying to describe.
The first diagram contains the nullable field we were initially discussing,
but in the second diagram the parent/child relationship is stored in a
separate table - ParentChildren. An entry in this table would only be made
if necessary (therefore eliminating the need to use a null value).
Was curious as to whether this would be considered the correct way to model
a "1 to 0/1" relationship. All of the database books, I have discuss the
common relationships (1-1, 1 - many, many - many), but don't touch upon this
scenario.
Thanks!
Chris
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eSMsI73$FHA.1676@.TK2MSFTNGP09.phx.gbl...
> Perhaps I don't understand your question correctly so you may want to post
> a DDL example. It seems to me that one has a nullable FK then the
> non-mandatory data are already in a separate table.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
> news:ObNCj$2$FHA.356@.TK2MSFTNGP12.phx.gbl...
>|||"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:uQv0ys$$FHA.3864@.TK2MSFTNGP12.phx.gbl...
> diagram 1:
> Parents
> --
> ParentID
> ParentDescription
> ChildID (nullable)
>
> Children
> --
> ChildID
> ChildDescription
>
> diagram 2:
> Parents
> --
> ParentID
> ParentDescription
>
> ParentChildren
> --
> ParentID
> ChildID
>
> Children
> --
> ChildID
> ChildDescription
> Dan, I guess diagram 2 would illustrate what I was trying to describe.
> The first diagram contains the nullable field we were initially
> discussing, but in the second diagram the parent/child relationship is
> stored in a separate table - ParentChildren. An entry in this table would
> only be made if necessary (therefore eliminating the need to use a null
> value).
> Was curious as to whether this would be considered the correct way to
> model a "1 to 0/1" relationship. All of the database books, I have
> discuss the common relationships (1-1, 1 - many, many - many), but don't
> touch upon this scenario.
> Thanks!
> Chris
I've built a database for managing daycares.
I use the table structure in diagram2 to map parent/child although in my
case it's more of a contactChild table.
In the contactChild table, there is a column which identifies the
relationship between the child and the contact
(mother/father/grandfather/aunt).
So a contact can be the father of one child and the uncle of another.
In your case, if you are entering only parents, I guess it's better to keep
that information in the parent table (still using diagram2)..
I don't think that a person can be one child's father and the mother of
another.
Although in today's world, I wouldn't be surprised.|||I'll assume that these aren't real entity names and are named for your
parent/child relationship example.
Let's take a closer look at the first model:
--diagram 1:
CREATE TABLE Parents
(
ParentID int NOT NULL,
ParentDescription varchar(50) NOT NULL,
ChildID int NULL
)
CREATE TABLE Children
(
ChildID int NOT NULL,
ChildDescription varchar(50) NOT NULL
)
ALTER TABLE Parents
ADD CONSTRAINT PK_Parents
PRIMARY KEY (ParentID)
ALTER TABLE Children
ADD CONSTRAINT PK_Children
PRIMARY KEY (ChildID)
ALTER TABLE Parents
ADD CONSTRAINT FK_Parents_Children
FOREIGN KEY (ChildID)
REFERENCES Children(ChildID)
This will permit orphaned children (child with no parent). If you need
referential integrity, you could remove ChildID from the Parents entity and
add ParentID to the Children entity with as a FK. This would implement your
1-0/1 cardinality, provide referential integrity and provide effective space
physical space utilization.
--diagram 1a:
CREATE TABLE Parents
(
ParentID int NOT NULL,
ParentDescription varchar(50) NOT NULL
)
CREATE TABLE Children
(
ChildID int NOT NULL,
ChildDescription varchar(50) NOT NULL,
ParentID int NOT NULL
)
ALTER TABLE Parents
ADD CONSTRAINT PK_Parents
PRIMARY KEY (ParentID)
ALTER TABLE Children
ADD CONSTRAINT PK_Children
PRIMARY KEY (ChildID)
ALTER TABLE Children
ADD CONSTRAINT FK_Children_Parents
FOREIGN KEY (ParentID)
REFERENCES Parents(ParentID)
The second model as I understand it:
--Diagram 2:
CREATE TABLE Parents
(
ParentID int NOT NULL,
ParentDescription varchar(50) NOT NULL
)
CREATE TABLE Children
(
ChildID int NOT NULL,
ChildDescription varchar(50) NOT NULL
)
CREATE TABLE ParentChildren
(
ParentID int NOT NULL,
ChildID int NOT NULL,
)
ALTER TABLE Parents
ADD CONSTRAINT PK_Parents
PRIMARY KEY (ParentID)
ALTER TABLE Children
ADD CONSTRAINT PK_Children
PRIMARY KEY (ChildID)
ALTER TABLE ParentChildren
ADD CONSTRAINT PK_ParentChildren
PRIMARY KEY (ParentID)
ALTER TABLE ParentChildren
ADD CONSTRAINT FK_ParentChildren_Parents
FOREIGN KEY (ParentID)
REFERENCES Parents(ParentID)
ALTER TABLE ParentChildren
ADD CONSTRAINT FK_ParentChildren_Children
FOREIGN KEY (ChildID)
REFERENCES Children(ChildID)
This also permits orphaned children. The only value over diagram 1a that I
see is that you could more easily modify this schema to support a many
relationship. Normally, an associative table like ParentChildren is used to
normalize a many-to-many or when additional attributes describe the
relationship.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:uQv0ys$$FHA.3864@.TK2MSFTNGP12.phx.gbl...
> diagram 1:
> Parents
> --
> ParentID
> ParentDescription
> ChildID (nullable)
>
> Children
> --
> ChildID
> ChildDescription
>
> diagram 2:
> Parents
> --
> ParentID
> ParentDescription
>
> ParentChildren
> --
> ParentID
> ChildID
>
> Children
> --
> ChildID
> ChildDescription
> Dan, I guess diagram 2 would illustrate what I was trying to describe.
> The first diagram contains the nullable field we were initially
> discussing, but in the second diagram the parent/child relationship is
> stored in a separate table - ParentChildren. An entry in this table would
> only be made if necessary (therefore eliminating the need to use a null
> value).
> Was curious as to whether this would be considered the correct way to
> model a "1 to 0/1" relationship. All of the database books, I have
> discuss the common relationships (1-1, 1 - many, many - many), but don't
> touch upon this scenario.
> Thanks!
> Chris
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eSMsI73$FHA.1676@.TK2MSFTNGP09.phx.gbl...
>|||On Mon, 12 Dec 2005 11:39:53 -0500, ChrisB wrote:

>Hello:
>Because of the possible issues associated with storing null values in the
>db, the development team of which I am a part opted to avoid using them
>when possible. This has resulted in an interesting issue -
(snip)
Hi Chris,
In fact, there are not so many issues with null values.
There are issues with handling missing data, though. But since missing
data is a fact of life, we'll have to make do as good as we can.
There are three ways to handle missing data.
1. Use nulls. This is the special value that is defined in SQL for
exactly this reason: to represent missing data. Most of the behaviour of
null is geared towards giving you the "best" behaviour wrt missing data.
2. Use a self-chosen symbol to represent missing data. This doesn't
solve any of the issues with missing data. Instead, it makes life worse
becuase the SQL engine is NOT taught to handle whatever you use to
represent missing data as missing. You'll have to write lots of special
code to make sure that the special values is excluded whenever they need
to be excluded.
3. Use seperate tables for all values that might be missing. If a
persons name, age and sex are always known but shoe size and hair colour
might be missing, use one table to store name, ageand sex, a second
table to store shoe size and a third for haid colour. This rids you of
the nulls AND of the need to represent missing data by another special
value. It also rids you of some of the idiosyncrasies of missing data.
But you'll also find that you often need to have all the values
together, so you use outer joins to combine the tables - and then the
NULLS will be back too.
Option 2 is not a serious option. Since the problem is not the null
symbol but the missing data that this symbol represents, choosing a
different symbol to represent missing data solves nothing.
Option 1 or 3 is more a matter of taste - and of course, if performance
matters you'll want to test them both.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Dan:
Thanks very much for your detailed examples. After giving your posts some
thought, I've decided to go with the 1a design you suggested.
Chris
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23vwv40AAGHA.3928@.tk2msftngp13.phx.gbl...
> I'll assume that these aren't real entity names and are named for your
> parent/child relationship example.
> Let's take a closer look at the first model:
> --diagram 1:
> CREATE TABLE Parents
> (
> ParentID int NOT NULL,
> ParentDescription varchar(50) NOT NULL,
> ChildID int NULL
> )
> CREATE TABLE Children
> (
> ChildID int NOT NULL,
> ChildDescription varchar(50) NOT NULL
> )
> ALTER TABLE Parents
> ADD CONSTRAINT PK_Parents
> PRIMARY KEY (ParentID)
> ALTER TABLE Children
> ADD CONSTRAINT PK_Children
> PRIMARY KEY (ChildID)
> ALTER TABLE Parents
> ADD CONSTRAINT FK_Parents_Children
> FOREIGN KEY (ChildID)
> REFERENCES Children(ChildID)
> This will permit orphaned children (child with no parent). If you need
> referential integrity, you could remove ChildID from the Parents entity
> and add ParentID to the Children entity with as a FK. This would
> implement your 1-0/1 cardinality, provide referential integrity and
> provide effective space physical space utilization.
> --diagram 1a:
> CREATE TABLE Parents
> (
> ParentID int NOT NULL,
> ParentDescription varchar(50) NOT NULL
> )
> CREATE TABLE Children
> (
> ChildID int NOT NULL,
> ChildDescription varchar(50) NOT NULL,
> ParentID int NOT NULL
> )
> ALTER TABLE Parents
> ADD CONSTRAINT PK_Parents
> PRIMARY KEY (ParentID)
> ALTER TABLE Children
> ADD CONSTRAINT PK_Children
> PRIMARY KEY (ChildID)
> ALTER TABLE Children
> ADD CONSTRAINT FK_Children_Parents
> FOREIGN KEY (ParentID)
> REFERENCES Parents(ParentID)
> The second model as I understand it:
> --Diagram 2:
> CREATE TABLE Parents
> (
> ParentID int NOT NULL,
> ParentDescription varchar(50) NOT NULL
> )
> CREATE TABLE Children
> (
> ChildID int NOT NULL,
> ChildDescription varchar(50) NOT NULL
> )
> CREATE TABLE ParentChildren
> (
> ParentID int NOT NULL,
> ChildID int NOT NULL,
> )
> ALTER TABLE Parents
> ADD CONSTRAINT PK_Parents
> PRIMARY KEY (ParentID)
> ALTER TABLE Children
> ADD CONSTRAINT PK_Children
> PRIMARY KEY (ChildID)
> ALTER TABLE ParentChildren
> ADD CONSTRAINT PK_ParentChildren
> PRIMARY KEY (ParentID)
> ALTER TABLE ParentChildren
> ADD CONSTRAINT FK_ParentChildren_Parents
> FOREIGN KEY (ParentID)
> REFERENCES Parents(ParentID)
> ALTER TABLE ParentChildren
> ADD CONSTRAINT FK_ParentChildren_Children
> FOREIGN KEY (ChildID)
> REFERENCES Children(ChildID)
> This also permits orphaned children. The only value over diagram 1a that
> I see is that you could more easily modify this schema to support a many
> relationship. Normally, an associative table like ParentChildren is used
> to normalize a many-to-many or when additional attributes describe the
> relationship.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
> news:uQv0ys$$FHA.3864@.TK2MSFTNGP12.phx.gbl...
>