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.)