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
{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fnil\fcharset0 Arial;}{\f1\fnil\fcharset0 MS Sans Serif;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\sb120\tx90\tqr\tx8640\cf1\lang1033\fs16 H:\\data\\database\\medrec.mdb\cf0\f1\fs24\tab\cf1\f0\fs16 Tuesday, March 15, 2005\fs21\par
\pard\tx90\tqr\tx8640\fs16 Table: Data_FH_Conditions\cf0\f1\fs24\tab\cf1\f0\fs16 Page: 1\par
\cf0\f1\fs24\tab\cf1\ul\b\f0\fs16 Columns\fs21\par
\pard\sb120\tx735\tx5055\tx7287\cf0\ulnone\b0\f1\fs24\tab\cf1\f0\fs16 Name\cf0\f1\fs24\tab\cf1\f0\fs16 Type\cf0\f1\fs24\tab\cf1\f0\fs16 Size\fs21\par
\pard\sb58\tx735\tx5055\tqr\tx8439\cf0\f1\fs24\tab\cf1\f0\fs16 Condition_ID\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Long)\cf0\f1\fs24\tab\cf1\f0\fs16 4\fs21\par
\pard\tx735\tx5055\tqr\tx8439\cf0\f1\fs24\tab\cf1\f0\fs16 Member_iD\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Long)\cf0\f1\fs24\tab\cf1\f0\fs16 4\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Condition\cf0\f1\fs24\tab\cf1\f0\fs16 Text\cf0\f1\fs24\tab\cf1\f0\fs16 50\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Ozcode\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Long)\cf0\f1\fs24\tab\cf1\f0\fs16 4\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Age_of_onset\cf0\f1\fs24\tab\cf1\f0\fs16 Text\cf0\f1\fs24\tab\cf1\f0\fs16 3\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Cause_of_death\cf0\f1\fs24\tab\cf1\f0\fs16 Yes/No\cf0\f1\fs24\tab\cf1\f0\fs16 1\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Comment\cf0\f1\fs24\tab\cf1\f0\fs16 Text\cf0\f1\fs24\tab\cf1\f0\fs16 50\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Deleted\cf0\f1\fs24\tab\cf1\f0\fs16 Yes/No\cf0\f1\fs24\tab\cf1\f0\fs16 1\fs21\par
\cf0\f1\fs24\page\pard\sb120\tx90\tqr\tx8640\cf1\f0\fs16 H:\\data\\database\\medrec.mdb\cf0\f1\fs24\tab\cf1\f0\fs16 Tuesday, March 15, 2005\fs21\par
\pard\tx90\tqr\tx8640\fs16 Table: Data_Fh_FMembers\cf0\f1\fs24\tab\cf1\f0\fs16 Page: 2\par
\cf0\f1\fs24\tab\cf1\ul\b\f0\fs16 Columns\fs21\par
\pard\sb120\tx735\tx5055\tx7287\cf0\ulnone\b0\f1\fs24\tab\cf1\f0\fs16 Name\cf0\f1\fs24\tab\cf1\f0\fs16 Type\cf0\f1\fs24\tab\cf1\f0\fs16 Size\fs21\par
\pard\sb58\tx735\tx5055\tqr\tx8439\cf0\f1\fs24\tab\cf1\f0\fs16 Member_ID\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Long)\cf0\f1\fs24\tab\cf1\f0\fs16 4\fs21\par
\pard\tx735\tx5055\tqr\tx8439\cf0\f1\fs24\tab\cf1\f0\fs16 Name\cf0\f1\fs24\tab\cf1\f0\fs16 Text\cf0\f1\fs24\tab\cf1\f0\fs16 50\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Date_of_birth\cf0\f1\fs24\tab\cf1\f0\fs16 Text\cf0\f1\fs24\tab\cf1\f0\fs16 10\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Relationship_ID\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Byte)\cf0\f1\fs24\tab\cf1\f0\fs16 1\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Age_of_Death\cf0\f1\fs24\tab\cf1\f0\fs16 Text\cf0\f1\fs24\tab\cf1\f0\fs16 3\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Deleted\cf0\f1\fs24\tab\cf1\f0\fs16 Yes/No\cf0\f1\fs24\tab\cf1\f0\fs16 1\fs21\par
\cf0\f1\fs24\page\pard\sb120\tx90\tqr\tx8640\cf1\f0\fs16 H:\\data\\database\\medrec.mdb\cf0\f1\fs24\tab\cf1\f0\fs16 Tuesday, March 15, 2005\fs21\par
\pard\tx90\tqr\tx8640\fs16 Table: Data_Fh_Links\cf0\f1\fs24\tab\cf1\f0\fs16 Page: 3\par
\cf0\f1\fs24\tab c\cf1\ul\b\f0\fs16 olumns\fs21\par
\pard\sb120\tx735\tx5055\tx7287\cf0\ulnone\b0\f1\fs24\tab\cf1\f0\fs16 Name\cf0\f1\fs24\tab\cf1\f0\fs16 Type\cf0\f1\fs24\tab\cf1\f0\fs16 Size\fs21\par
\pard\sb58\tx735\tx5055\tqr\tx8439\cf0\f1\fs24\tab\cf1\f0\fs16 Link_ID\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Long)\cf0\f1\fs24\tab\cf1\f0\fs16 4\fs21\par
\pard\tx735\tx5055\tqr\tx8439\cf0\f1\fs24\tab\cf1\f0\fs16 Member_iD\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Long)\cf0\f1\fs24\tab\cf1\f0\fs16 4\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Patient_ID\cf0\f1\fs24\tab\cf1\f0\fs16 Number (Long)\cf0\f1\fs24\tab\cf1\f0\fs16 4\fs21\par
\cf0\f1\fs24\tab\cf1\f0\fs16 Deleted\cf0\f1\fs24\tab\cf1\f0\fs16 Yes/No\cf0\f1\fs24\tab\cf1\f0\fs16 1\par
\par
Query these using:\par
\par
SELECT data_FH_Links.Patient_ID, data_FH_FMembers.Member_ID, data_FH_FMembers.Consult_ID AS Member_Consult_iD, data_FH_FMembers.Consult_First_Noted AS Member_Consult_First_Noted, data_FH_FMembers.Name, data_FH_FMembers.Date_of_birth, data_FH_FMembers.Relationship_ID, data_FH_FMembers.Age_of_Death, data_FH_Conditions.Consult_ID AS Condition_Consult_ID, data_FH_Conditions.Consult_First_Noted AS Condition_Consult_First_Noted, data_FH_Conditions.Condition_ID, data_FH_Conditions.Condition, data_FH_Conditions.Ozcode, data_FH_Conditions.Age_of_onset, data_FH_Conditions.Cause_of_death, lu_Family_relationships.Description, data_FH_Conditions.Deleted, data_FH_Conditions.Comment, data_FH_FMembers.Deleted, data_FH_Links.Deleted\par
FROM ((data_FH_Links INNER JOIN data_FH_FMembers ON data_FH_Links.Member_iD = data_FH_FMembers.Member_ID) INNER JOIN data_FH_Conditions ON data_FH_FMembers.Member_ID = data_FH_Conditions.Member_iD) INNER JOIN lu_Family_relationships ON data_FH_FMembers.Relationship_ID = lu_Family_relationships.Relationship_ID\par
WHERE (((data_FH_Links.Patient_ID)=[Enter Patient ID]) AND ((data_FH_Conditions.Deleted)=False) AND ((data_FH_FMembers.Deleted)=False) AND ((data_FH_Links.Deleted)=False))\par
ORDER BY data_FH_FMembers.Member_ID;\par
\par
Produces recordset output which is extremely easy to display:\par
\par
name \tab relation disease ageonset caused death,age death etc etc\par
-------------------------------------------------------------------------------------------------\par
Joan smith Mother Hypertension 55 n\par
" " " angina 60 n\par
" " " AMI 61 y 61\par
Fred Uncle-maternal Lung cancer 77 y 78\par
Peter grandfather - paternal Stroke 99 y 99\par
Peter grandfather - paternal Hypertension 65 n\par
\par
\fs21\par
}
_______________________________________________
Gnumed-devel mailing list
[email protected]
http://lists.gnu.org/mailman/listinfo/gnumed-devel
