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