On Thu, Apr 21, 2011 at 07:15:38PM +0200, Alban Hertroys wrote:
> > What is the suggested approach for this situation ? (there
> > will be more tables like "icd10" holding other coding
> > systems of fairly diverse nature but all of them sharing
> > .code and .term: LOINC, ATC, ICPC-2, ICD-9, ...).
>
> I think your best bet is to not rely on inheritance here.
> If you instead reference code_root from icd10 using (code,
> term), you end up with the same results, while you then only
> need to reference code_root from your lnk_codes2epi table.
...
> So this would become:
>
> create table code_root (
> pk_code_root serial primary key,
> code text not null,
> term text not null,
> UNIQUE (code, term)
> );
>
> create table icd10 (
> pk serial primary key,
> code text not null,
> term text not null,
> version text not null,
> FOREIGN KEY (code, term) REFERENCES code_root (code, term)
> );
>
> This does rely on the combination of (code, term) being
> unique in code_root. If it's not,
It is not, because there can, for example, be several
versions (depending on, say, the country) and generations
(evolving over time) of "one and the same" coding system.
> you would need an extra
> table with just every unique combination of (code, term)
> that both code_root and icd10 would reference.
Or else I could, additionally, put a .system_version column
in both tables and add that into the foreign key.
The downside would be that I now have a slow(er) foreign key
consisting of three text fields which, in addition, each
duplicate information (however, the database helps me to
keep those duplicates in sync by way of ON UPDATE/DELETE
CASCADE). I would then need to add an ON INSERT trigger to
each of the icd10, ... tables such that INSERTS into them
get propagated to code_root. Then it'd be wise to disallow
user level INSERTs/UPDATEs/DELETEs on code_root.
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346