---------- Original Message ----------- From: Ann Harrison <[email protected]> > 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
