Re: [Gnumed-devel] Re: Gnumed-devel Digest, Vol 6, Issue 34

[Karsten Hilbert]

> In some cases I'd even go for the following approach for performance (and 
> sort 
> of simplicity) reasons:
> where normalized data is small (example: typical enumerations) we should 
> store 
> BOTH data and foreign key to data in the table. A trigger will intercept 
> inserts and updates and make sure that referential integrity is maintained by 
> using the referenced table as "lookup" only.
This is only necessary in cases where the "actually"
referenced column is NOT unique (as in the example in the
previous mail). For other cases where the referenced column IS
(supposed to be) unique the need for insert/update triggered
referential integrity checks goes away simply by referencing
the real column instead of the (artifical) primary key of the
referenced table:

create table _enum_hx_type (
    id serial primary key,
    "name" unique varchar(50)
);

create table hx_entry (
    id serial primary key,
    fk_type varchar(50) references _enum_hx_type(name),
    data varchar
);

Here, _enum_hx_type."name" is supposed to be unique by design
anyways, so we can just reference that and let PostgreSQL do
the hard work.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346