Re: problem with parent/child table and FKs

On 18 Apr 2011, at 15:53, Karsten Hilbert 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.

> begin;
>
> create table code_root (
> pk_code_root serial primary key,
> code text not null,
> term text not null
> );
>
> create table icd10 (
> pk serial primary key,
> version text not null
> ) inherits (code_root);

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, you would need an extra table with just every unique combination of (code, term) that both code_root and icd10 would reference.

BTW, do you really need those artificial PK's? If not, you may well be better off dropping them. That way (code, term) could be your PK instead. I don't know enough about your data to make more than a guess though, I just get itchy when I see such designs ;)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4db0665111731275120228!