Re: [Gnumed-devel] family history table

[Karsten Hilbert]

BTW, here is the current, enhanced table:

create table clin_hx_family (
        pk serial primary key,
        fk_narrative_condition integer
                default null
                references clin_narrative(pk)
                on update cascade
                on delete restrict,
        fk_relative integer
                default null
                references xlnk_identity(xfk_identity)
                on update cascade
                on delete set null,
        relationship text
                not null
                check (trim(relationship) != ''),
        name_relative text
                default null
                check (coalesce(trim(name_relative), 'dummy') != ''),
        dob_relative timestamp with time zone
                default null,
        age_noted text,
        age_of_death interval
                default null,
        is_cause_of_death boolean
                not null
                default false
) inherits (clin_root_item);

alter table clin_hx_family add constraint narrative_null_or_not_empty
        check (coalesce(trim(narrative), 'dummy') != '');

alter table clin_hx_family add constraint link_or_know_relative
        check (
                -- from linked narrative
                (fk_narrative_condition is not null and fk_relative is null and 
name_relative is null and dob_relative is null)
                        or
                -- from linked relative
                (fk_narrative_condition is null and fk_relative is not null and 
name_relative is null and dob_relative is null)
                        or
                -- from name
                (fk_narrative_condition is null and fk_relative is null and 
name_relative is not null)
        );

alter table clin_hx_family add constraint link_or_know_condition
        check (
                (fk_narrative_condition is not null and narrative is null)
                        or
                (fk_narrative_condition is null and narrative is not null)
        );

-- FIXME: constraint trigger fk_narrative -> has_type(fHx)

select add_table_for_audit('clin_hx_family');

comment on table clin_hx_family is
        'stores family history items';
comment on column clin_hx_family.clin_when is
        'when the family history item became known';
comment on column clin_hx_family.fk_encounter is
        'encounter during which family history item became known';
comment on column clin_hx_family.fk_episode is
        'episode to which family history item is of importance';
comment on column clin_hx_family.narrative is
        'fHx-typed narrative holding the condition
         the relative suffered from, must be NULL
         if fk_narrative_condition is not';
comment on column clin_hx_family.soap_cat is
        'as usual, must be NULL if fk_narrative_condition is not but
         this is not enforced and only done in the view';
comment on column clin_hx_family.fk_narrative_condition is
        'can point to a narrative item of a relative if in database';
comment on column clin_hx_family.fk_relative is
        'foreign key to relative if also in database';
comment on column clin_hx_family.relationship is
        'how is the afflicted person related to the patient,
         maybe make nullable later when fk_relative or
         fk_narrative_condition is not null and the relationship
         is defined there';
comment on column clin_hx_family.name_relative is
        'name of the relative if not also in database';
comment on column clin_hx_family.dob_relative is
        'DOB of relative if not also in database';
comment on column clin_hx_family.age_noted is
        'at what age the relative acquired the condition';
comment on column clin_hx_family.age_of_death is
        'at what age the relative died';
comment on column clin_hx_family.is_cause_of_death is
        'whether relative died of this problem, Richard
         suggested to allow that several times per relative';



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