Re: problem with parent/child table and FKs

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: problem with parent/child table and FKs
Date: 2011-04-27 16:52:42
Message-ID: 20110427165241.GK2481@hermes.hilbert.loc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Schreyer 2011-04-27 17:00:47 Re: Best way to construct PostgreSQL ArrayType (_int4) from C int array
Previous Message gabrielle 2011-04-27 16:24:57 PgDay at OSCON