unordained schrieb am 11.04.2012 um 15:18 (-0500): > 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.
Sounds like it might be a cool application for nested sets to represent tree as popularized by Joe Celko. https://www.google.com/search?q=celko+tree Funny, first hit for me looks like an InterBase site. :) http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html But maybe your scenario wouldn't have been a precise fit for that model. Michael > (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.)
