Re: [Gnumed-devel] family history table

[Karsten Hilbert]

Anyone care to comment on the current state of family history
tables/views ?

- --------------------------------------------
create table clin_hx_family (
        pk serial primary key,
        fk_narrative integer
                unique
                not null
                references clin_narrative(pk)
                on update cascade
                on delete restrict,
        relationship text
                not null,
        name_relative text
                default null,
        dob_relative timestamp with time zone
                default null,
        fk_relative integer
                default null
                references xlnk_identity(xfk_identity)
                on update cascade
                on delete set null,
        age_noted text,
        age_of_death interval
                default null,
        is_cause_of_death boolean
                not null
                default false
) inherits (audit_fields);

alter table clin_hx_family add constraint either_fk_or_name_and_dob
        check (
                (fk_relative is not null and name_relative is null and 
dob_relative is null)
                        or
                (fk_relative is null and coalesce(trim(name_relative), '') != 
'')
        );

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

select add_table_for_audit('clin_hx_family');

comment on table clin_hx_family is
        'used to store family history items';
comment on column clin_hx_family.fk_narrative is
        'link to FHx-typed clin_narrative holding the
         condition the relative suffered from';
comment on column clin_hx_family.relationship is
        'how is the afflicted person related to the patient';
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.fk_relative is
        'foreign key to relative if 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';

-- --------------------------------------------
-- family history
\unset ON_ERROR_STOP
drop view v_hx_family;
\set ON_ERROR_STOP 1

create view v_hx_family as
select
        vbp.pk_identity as pk_patient,
        vpn.soap_cat as soap_cat,
        vpn.narrative as condition,
        hxf.relationship as relationship,
        _(hxf.relationship) as l10n_relationship,
        case when hxf.fk_relative is null
                then hxf.name_relative
                else coalesce(vbp.lastnames, '') || ', ' || 
coalesce(vbp.firstnames, '')
        end as name_relative,
        case when hxf.fk_relative is null
                then hxf.dob_relative
                else vbp.dob
        end as dob_relative,
        hxf.age_noted as age_noted,
        hxf.is_cause_of_death as is_cause_of_death,
        hxf.age_of_death as age_of_death,
        hxf.pk as pk_hx_family,
        hxf.fk_narrative as pk_narrative,
        hxf.fk_relative as pk_relative
from
        clin_hx_family hxf,
        v_basic_person vbp,
        v_pat_narrative vpn
where
        hxf.fk_narrative = vpn.pk_narrative
                and
        vpn.pk_patient = vbp.pk_identity
;

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