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

[Bob Badour]

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

"Bob Badour" <bb*****@golden .net> wrote in message
news:ao******** ************@go lden.net...

Your list of "fundamenta ls" does not answer any of the questions "What is data?", "What does it mean to manage data?" or "What principles facilitate or guide effective data management?"
In that case I'd be interested in learning some of these fundamentals. I

may have to take myself to the library...
Try to find a library with a copy of the ISO/IEC Standard Vocabularies for
Information Technology. A friend drew my attention to an article in IEEE
Compute called _The Great Term Robbery_ a few years ago; I found both that
article and the standard vocabularies very informative with respect to "What
is data?".

I have never found a succinct list of principles, and if anyone knows of
one, I would love to see it. Codd's 12 Rules embody a lot of principles he
did not name explicitly; although, logical identity, guaranteed access,
physical and logical independence are all principles. Certainly, the
principle of separating concerns applies to data management in several ways.
As a general principle, one prefers to minimize, centralize and automate any
need for highly specialized or arcane knowledge. One prefers to maximize the
portability of one's data. One prefers to make easy things easy and to make
likely errors difficult. One prefers to minimize the learning curve for
casual users. etc.

Of the items in your list above, integrity and data types are fundamental, but your elaborations above are anything but fundamental.

One can come up with any number of taxonomies for integrity
constraints--Chris Date has published enough of them in his career. The
taxonomy I find most enlightening is: All integrity constraints constrain variables. Integrity is fundamental because it is fundamental to the
manipulation function when managing data.

A data type does not enforce business rules--the integrity function of the dbms does this. Data type is fundamental to computing and not only to data management. A data type comprises both a set of values and a set of
operations on those values. With respect to the relational model, Date and Darwen have observed that data types define what we can make statements
about, and relations make statements about them.

Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
constraining the set of possible values that can be stored in a column
constrained to that type.

Data types form part of the definition of some constraints, but the
integrity function of the dbms enforces constraints. What you suggest above
is similar to suggesting that legislation and street signs enforce traffic
laws. Police officers and the judiciary enforce traffic laws.

If a business rule dictates that data of a certain
type must fall within a spefic range, for example, then by defining a type
that imposes this constraint, the business rule could be enforced by the
Data Type?

The type does not impose the constraint; the integrity function of the dbms
imposes the constraint. The type merely describes the constraint. For a very
long time, almost all constraints in commerical SQL dbmses were nothing more
than comments. One was allowed to express them, but the integrity function
of the dbms ignored them (if one can really claim an integrity function even
exists in that situation).