Can FK be nullable/optional by design?

[Andy]

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.

It works, and it is simpler.
The design that satisfy business rule and FK not null can be
implemented but it will be more complicated.

Example: There are clients. A client might belong to only one group.

Case A.
Group(GroupID PK, Name,Code…)
Client(ClientID PK, Name, GroupID FK NULL)
Case B(more cleaner)
Group(GroupID PK, Name, GroupCode…)

Client (ClientID PK, Name, ….)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group
Summary Q: Is it worth to go with CaseB?

Thank you in advance

[Joe \Nuke Me Xemu\ Foster]

"Andy" <ne********@hot mail.com> wrote in message <news:ed******* *************** ****@posting.go ogle.com>...

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.
Nulls suck. Dealing with Null is ugly any way you look at it.
It works, and it is simpler.
The design that satisfy business rule and FK not null can be
implemented but it will be more complicated.

Example: There are clients. A client might belong to only one group.

Case A.
Group(GroupID PK, Name,Code.)
Client(ClientID PK, Name, GroupID FK NULL)
In this scheme, a client may belong to no group or one group but
cannot belong to more than one group. Is this the business rule?
Case B(more cleaner)
Group(GroupID PK, Name, GroupCode.)

Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group
With one tweak, GroupedClient can be a many<->many link between
Client and Group. Otherwise, you can always use a view to turn
Case B into Case A for the convenience of a particular program.
Summary Q: Is it worth to go with CaseB?

Case C. Use one or more "special" groups to "contain" otherwise
"groupless" clients. However, you now have the "special" groups
to deal with.

--
Joe Foster <mailto:jlfoste r%40znet.com> Sign the Check! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!

[Bruce Lewis]

ne********@hotm ail.com (Andy) writes:

General statement: FK should not be nullabe to avoid orphans in DB.

I don't see the reasoning behind this statement. Any column that
references keys to another table should be explicitly specified as such
to avoid orphans.

If that column may sometimes be unknown/unspecified for perfectly valid
records, I see no reason not to make it nullable.

--
"Notwithstandin g fervent argument that patent protection is essential
for the growth of the software industry, commentators have noted
that `this industry is growing by leaps and bounds without it.'"
-- US Supreme Court Justice John Paul Stevens, March 3, 1981.

[Trey Walpole]

depends on what a Group is and how it is used...

e.g.,
is a Group a Super-Client? -- individual Clients may be subsidiaries of a
Super-Client?
is a Group in internal designation, like a Sales territory?

How many Clients are there likely to be w/o a group?
When you need to act on the clients that are grouped, do you also need to
act on the clients that are not grouped?

[ps. in Case B, where did PersonID come from? Is that the Client?]

Example: There are clients. A client might belong to only one group.
Case A.
Group(GroupID PK, Name,Code.)
Client(ClientID PK, Name, GroupID FK NULL)
Case B(more cleaner)
Group(GroupID PK, Name, GroupCode.)

Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group
Summary Q: Is it worth to go with CaseB?

Thank you in advance

[Andy]

"Trey Walpole" <tr********@SPc omcastAM.net> wrote in message news:<u3******* *******@tk2msft ngp13.phx.gbl>. ..

depends on what a Group is and how it is used...

e.g.,
is a Group a Super-Client? -- individual Clients may be subsidiaries of a
Super-Client?
is a Group in internal designation, like a Sales territory?

How many Clients are there likely to be w/o a group?
When you need to act on the clients that are grouped, do you also need to
act on the clients that are not grouped?

[ps. in Case B, where did PersonID come from? Is that the Client?]

Yes, it does.
It should be this way

[ps. in Case B, where did PersonID come from? Is that the Client?]

Case B
Group(GroupID PK, Name, GroupCode.)
Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (ClientID PK/FK, GroupID FK NOT NULL)

[Eric Junkermann]

ne********@hotm ail.com (Andy) wrote in message news:<ed******* *************** ****@posting.go ogle.com>...

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Where did this statement come from? The idea of an orphan belongs to
network and hierarchical databases (old fashioned) or to
object-oriented databases (allegedly new), where the only way to get
to a record might be through its parent record. In a relational
database there is no such thing as an orphan.

You can find your "orphans" by some equivalent of (client where
groupcode not present) (worded that way to keep away from arguments
about NULLS).

In your example, what you have is

A client may be a member of at most one group.

If you meant to have

A client must be a member of exactly one group.

then (in your example) you would have to use NOT NULL.

Regards,

Eric

[Tobin Harris]

"Andy" <ne********@hot mail.com> wrote in message
news:ed******** *************** ***@posting.goo gle.com...

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.

I'm not too hot on all this, but here is what I was lead to believe: If
Client *must* belong to at least one group, then the client is dependent on
the group - it cannot exist without it. Therefore, it's primary key would
(at least logically) be a composite, where the group pk forms part of the
clients composite primary key. This would ensure that a client cannot exist
without a group!?

This might look like:
Client(GroupID PK, ClientID PK, Name )
Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

Just my 2 pennies worth 8-)

Tobes

[Joe \Nuke Me Xemu\ Foster]

"Tobin Harris" <to************ ********@breath email.net> wrote in message <news:br******* *****@ID-135366.news.uni-berlin.de>...

"Andy" <ne********@hot mail.com> wrote in message
news:ed******** *************** ***@posting.goo gle.com...

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.

I'm not too hot on all this, but here is what I was lead to believe: If
Client *must* belong to at least one group, then the client is dependent on
the group - it cannot exist without it. Therefore, it's primary key would
(at least logically) be a composite, where the group pk forms part of the
clients composite primary key. This would ensure that a client cannot exist
without a group!?

This might look like:
Client(GroupID PK, ClientID PK, Name )
Did you really mean to claim that ALL non-nullable attributes MUST
'logically' be included as part of the primary key?!
Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

This would avoid the null nonsense until someone does an outer join.

--
Joe Foster <mailto:jlfoste r%40znet.com> L. Ron Dullard <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!

[Tobes \(Breath\)]

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUC P> wrote in message
news:10******** *******@news-1.nethere.net.. .

Did you really mean to claim that ALL non-nullable attributes MUST
'logically' be included as part of the primary key?!
Well, not really! I was just throwing in another option - where if the
existance of one entity is dependent on another, then you can make the PK of
that entity part of a composite key in the dependent entity. It's an
alternative to just non nullable foreign keys, where the related column(s)
become part of a primary key, rather than just a foreign key. Sorry, I think
I need to take my anti-waffle pill, can't seem to put a good explanation
together 8-)

Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

This would avoid the null nonsense until someone does an outer join.

That's true. So which option would you go for?

Tobes
--
Joe Foster <mailto:jlfoste r%40znet.com> L. Ron Dullard <http://www.xenu.net/> WARNING: I cannot be held responsible for the above They're coming to because my cats have apparently learned to type. take me away,

ha ha!

[Joe \Nuke Me Xemu\ Foster]

"Tobes (Breath)" <to************ ****@breathemai l.net> wrote in message <news:br******* *****@ID-131901.news.uni-berlin.de>...

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUC P> wrote in message
news:10******** *******@news-1.nethere.net.. .

Did you really mean to claim that ALL non-nullable attributes MUST
'logically' be included as part of the primary key?!

Well, not really! I was just throwing in another option - where if the
existance of one entity is dependent on another, then you can make the PK of
that entity part of a composite key in the dependent entity. It's an
alternative to just non nullable foreign keys, where the related column(s)
become part of a primary key, rather than just a foreign key. Sorry, I think
I need to take my anti-waffle pill, can't seem to put a good explanation
together 8-)

The ClientID by itself should probably be the primary key, though
the GroupID could be made part of an alternate candidate key.

Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

This would avoid the null nonsense until someone does an outer join.

That's true. So which option would you go for?

Maybe have a special "Loners" group? =) It's hard to say given
the information at hand. Yeah, I know, the usual cop-out...

--
Joe Foster <mailto:jlfoste r%40znet.com> Sacrament R2-45 <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!