Re: Can FK be nullable/optional by design?

"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 Fri Dec 12 2003 - 00:22:42 CET