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