Re: Separate foreign keys with shared ID space

"Hans Forbrich" <forbrich_at_yahoo.net> wrote in message news:jZROc.2236$T_6.1453_at_edtnps89...
> Marshall Spight wrote:
>
> > "Christian Antognini" <christian.antognini_at_trivadis.com> wrote in
message
> > news:410affcf$1_at_post.usenet.com...
> >>
> >> A PK should have no business meaning.
> >
> > Says who? Can you justify this statement?
>
> A PK should be selected to uniquely identify an entity. Ideally, and by
> formal definition, the PK is invariant.
>
Hmmm. Any key _identifies_ exactly one single key value that we hope corresponds to identifying a single entity, but there are no guarantees, especially over time because the business or real world domain might change over time. The use of the term 'primary key' is frivolous in my view and leads to confusion, as can be seen in the cases presented here.

Starting with the argument that the primary key is invariant and that it has no business meaning can lead to cases where the invariant property is held, but also where the same entity instance with sometimes contradicting propery states will appear. There is no guarantee of entity uniqueness because business semantics have been decoupled from the notion of tuple uniqueness. Saying one has guaranteed the uniqueness of a key, but not the uniqueness of the actual entity does nothing but raise the risk of integrity problems. I've seen this happen time and again with surrogates.

This might be unavoidable in some cases, but I think that if one can create at least one and possibly more tuple level identification mechansisms based on a subset of the attributes reflecting logical properties in the real world (i.e. properties with business meaning), and if we avoid the traps of thinking in terms of a primary key and think in terms of candidate keys, one can react to evolving semantics by changing keys. What's the big deal? If the logical model of the business changes, why shouldn't the candidate keys change to reflect them while still ensuring identification and uniqueness properties?

Example: Select the five colors from the color domain...

RED
BLUE
ORANGE
YELLOW
GREEN The key should be self evident. If we throw the color orange out and add the color black, the logical identification of all colors correctly reflects our changing conceptualization of a set of valid colors. Now add a key with no business meaning.

1 RED
2 BLUE
3 ORANGE
4 YELLOW
5 GREEN It seems obvious that the system will not complain if I add a tuple such as: 6 RED, and this is not a desirable event!

> All to often a unique attribute of the entity, such as empno (or SSN/SIN
or
> name or email address,) is used as the PK. Attributes generally have a
> business meaning. Such attributes can change, although some change very
> infrequently.

Well, each social security number should be assigned to one and only one person, right? I would think that a candidate key reflecting this particular rule would still be desirable, yes?

>
> Selecting an attribute as the PK can cause DBAs (and/or developers and/or
> businesses) headaches when attempting to change the PK. This is
especially
> true when taking the offline archives into account.

This is a good point from a practical standpoint, but changing table structures and other evolutions in the schema cause as many problems? Why throw the baby out with the bath water?

>
> All of the examples are not invariant, although they tend not to change
> often. In some cases using the suggested PK is against the law (eg: SIN
in
> Canada, has privacy implications).
>
> Hans

• Dan