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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
