Re: problem with parent/child table and FKs

On Thu, Apr 21, 2011 at 07:53:04AM -0700, Adrian Klaver wrote:

> On Thursday, April 21, 2011 4:36:51 am Karsten Hilbert wrote:
> > Does anyone have any suggestions regarding the below ?
>
> The only thing I can come up with is to eliminate the FK :
>  fk_code integer not null
>                references code_root(pk_code_root)
>                on update restrict
>                on delete restrict
> on lnk_codes2epi and replace it with a trigger that essentially does the same
> thing ; check for presence of pk_code_root.

I feared as much. I hoped to get around that somehow but
what you suggest doesn't sound half bad, actually.

I had been thinking to do the typical master-detail tables
for the coding systems instead of the inheritance:


table generic_code
    pk serial primary key
    code
    term
    system

table icd10
    pk serial primary key
    fk_generic_code
        references generic_code(pk)
    icd10_extra_field

table icd9
    pk serial primary key
    fk_generic_code
        references generic_code(pk)
    icd9_extra_field

table disease
    pk serial primary key
    description

table lnk_code2disease
    pk serial primary key
    fk_generic_code
        references generic_code(pk)
    fk_disease
        references disease(pk)


But then I'd have to write even more triggers making sure
that rows in, say, the icd10 table don't link to rows in the
generic_code table whose .system <> 'icd10'.

I can't put the .system column into the icd10/icd9/...
tables either because I need that column in generic_code to
ensure:

    unique(code, system)

Sheesh :-)

I suppose my underlying problem is that PostgreSQL's
inheritance is not intended to support polymorphism which is
what I seem to be trying to do - link diseases to
polymorphic code tables.

I can't easily think of a better relational solution,
though. The real world requirement for polymorphism is
surely there.

I guess I'll go with your solution unless someone comes up
with a better idea yet.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346