Re: [Gnumed-devel] use of PG inheritance in GNUmed

[Karsten Hilbert]

Tim,

we have three main uses currently. A table

create table audit_fields (
        pk_audit serial primary key,
        row_version integer not null default 0,
        modified_when timestamp with time zone not null default 
CURRENT_TIMESTAMP,
        modified_by name not null default CURRENT_USER
);

provides all descendants with fields used in auditing table
changes. The primary keyness of pk_audit does not inherit,
though. Child tables fill in the fields. Audited data
accumulates in descendants of 

create table audit_trail (
        pk_audit serial primary key,
        orig_version integer not null default 0,
        orig_when timestamp with time zone not null,
        orig_by name not null,
        orig_tableoid oid not null,
        audit_action varchar(6) not null check (audit_action in ('UPDATE', 
'DELETE')),
        audit_when timestamp with time zone not null default CURRENT_TIMESTAMP,
        audit_by name not null default CURRENT_USER
);

by the same principle.

The third use has a somewhat different background. I looked
for a way to have access to the entire narrative of a medical
record (for easier fulltext searching). Hence we said that

create table clin_root_item (
        pk_item serial primary key,
        clin_when timestamp with time zone not null default CURRENT_TIMESTAMP,
        id_encounter integer not null references clin_encounter(id),
        id_episode integer not null references clin_episode(id),
        narrative text
) inherits (audit_fields);

will provide basic fields for all items of medical
granularity. The field narrative provides a list of all the
medical narrative in the EMR of a patient. The corresponding
child table can be found by inspecting clin_root_item.tableoid
(a system level column).

This is how we currently use inheritance.

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