Re: [Gnumed-devel] family history table

[Richard Terry]

comments and questions.

It seems to be a single table. I do not some sort of ?external key 
(fk_relative??)

One of the major bugbears of my system is the inability to import family 
history data from  within the database from related persons, although when I 
set the tables up which I've shown you before (and enclose below), they were 
normalised to do so, I never got it finished. 

This has caused endless unnecessary repeative copying from one instance of my 
program to another - instead of being able to have an import wizard come up 
with all related family members and let you click on a checkbox to include a 
particular history problem  in the persons notes you are working on.

Have you included this ability in your table design?

Regards

Richard





On Sun, 13 Mar 2005 11:26 pm, Karsten Hilbert wrote:
> 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

Table: Data_FH_Conditions       Page: 1
        
        Condition_ID    
        Member_iD       
        Condition       
        Ozcode  
        Age_of_onset
        Cause_of_death
        Comment
        Deleted

 
Table: Data_Fh_FMembers
        

        Member_ID
        Name
        Date_of_birth   
        Relationship_ID 
        Age_of_Death
        Deleted
 
Table: Data_Fh_Links

        Link_ID 
        Member_iD       
        Patient_ID      
        Deleted

fh_tables.rtf
Description: Text Data