Can FK be nullable/optional by design?

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 Received on Sun Nov 30 2003 - 13:14:52 CST