---------- Original Message -----------
From: Ann Harrison <aharrison@...>

> And what part of relational theory allows partial matches on keys.
> Ann

------- End of Original Message -------

Hmmmm.

Codd might disagree with the incomplete normalization of his base tables, but
relational theory allows for set-joins across just about anything. If it's proper
to be able to "A inner join B on B.x > A.y", or "A left join B on B.a_id = A.id
and B.deleted = 0", neither of which are pure PK/FK joins, then I see no reason
that starts-with joins should be considered less relationally valid. If anything,
that proves the great strength of relational databases as opposed to strictly
navigational (network, hierarchical) ones, and should be highlighted, not
downplayed. This sort of difference always comes up when discussing "key vs.
pointer" and "natural vs. synthetic key" issues with less-practiced database
practitioners, who foresee no benefits to having a general-purpose system.

I once worked on medical software (with Firebird!) and we had to deal with ICD-9
and CPT-4 codes. The coding mechanism is somewhat hierarchical: code 201 might
mean something, and 201.1 might be more specific, and 201.12 might be even *more*
specific. (Sadly, they weren't entirely consistent.) I could see joins from a
list of "basic" conditions (diabetes, etc.) to "actual" conditions (per-patient),
where you want to find any patients who have certain conditions; you don't want
the person setting up the report to have to list each individual sub-condition,
when you know the key is structured such that a starts-with query would find the
rest. So you throw the list of user-selected base conditions (201.%) into a temp
table, and then do a simple join, but not an equi-join. The values can be
considered natural PK's (natural in that they're provided by some outside system,
and building your own would just mean headaches later on), and trying to
normalize that into a set of fields would be foolhardy (arbitrary number of
"parts" in the tree; it could be a key like 1.2.3 that also sometimes goes to
1.2.3.4.5.6 -- MIBs come to mind, in the realm of SNMP.)

So ... not completely crazy. At least not necessarily.

-Philip