Re: Can FK be nullable/optional by design?

"Andy" <[EMAIL PROTECTED]> wrote in message <news:[EMAIL PROTECTED]>...

> 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:jlfoster%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!