Re: Can FK be nullable/optional by design?

From: Tobin Harris <tobin_dont_you_spam_me_at_breathemail.net>
Date: Thu, 11 Dec 2003 23:22:42 -0000
Message-ID: <braub1$1cceh$1@ID-135366.news.uni-berlin.de>

"Andy" <net__space_at_hotmail.com> wrote in message news:edb90340.0311301114.19718061_at_posting.google.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 Received on Thu Dec 11 2003 - 17:22:42 CST