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