Richard,
find below the current state of affairs incorporating our
discussion. Note that this is neither in CVS nor on the public
server yet. Nor has the test data been updated. It does
bootstrap, though.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
=-- --------------------------------------------
=-- general FH storage
create table hx_family_item (
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,
name_relative text
default null
check (coalesce(trim(name_relative), 'dummy') != ''),
dob_relative timestamp with time zone
default null,
condition text
default null
check (coalesce(trim(condition), 'dummy') != ''),
age_noted text,
age_of_death interval
default null,
is_cause_of_death boolean
not null
default false,
unique (name_relative, dob_relative, condition),
unique (fk_relative, condition)
) inherits (audit_fields);
select add_table_for_audit('hx_family_item');
alter table hx_family_item add constraint link_or_know_condition
check (
(fk_narrative_condition is not null and condition is null)
or
(fk_narrative_condition is null and condition is not null)
);
alter table hx_family_item 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)
);
comment on table hx_family_item is
'stores family history items independant of the patient,
this is out-of-EMR so that several patients can link to it';
comment on column hx_family_item.fk_narrative_condition is
'can point to a narrative item of a relative if in database';
comment on column hx_family_item.fk_relative is
'foreign key to relative if in database';
comment on column hx_family_item.name_relative is
'name of the relative if not in database';
comment on column hx_family_item.dob_relative is
'DOB of relative if not in database';
comment on column hx_family_item.condition is
'narrative holding the condition the relative suffered from,
must be NULL if fk_narrative_condition is not';
comment on column hx_family_item.age_noted is
'at what age the relative acquired the condition';
comment on column hx_family_item.age_of_death is
'at what age the relative died';
comment on column hx_family_item.is_cause_of_death is
'whether relative died of this problem, Richard
suggested to allow that several times per relative';
=-- patient linked FH
create table clin_hx_family (
pk serial primary key,
fk_hx_family_item integer
not null
references hx_family_item(pk)
on update cascade
on delete restrict
) inherits (clin_root_item);
alter table clin_hx_family add constraint narrative_not_empty
check (coalesce(trim(narrative), '') != '');
=-- FIXME: constraint trigger has_type(fHx)
select add_table_for_audit('clin_hx_family');
comment on table clin_hx_family is
'stores family history for a given patient';
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
'how is the afflicted person related to the patient';
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';
create view v_hx_family as
=-- those not linked to another patient as relative
select
vpi.pk_patient as pk_patient,
vpi.pk_health_issue as pk_health_issue,
chxf.clin_when as clin_when,
chxf.fk_encounter as pk_encounter,
chxf.fk_episode as pk_episode,
chxf.narrative as relationship,
chxf.soap_cat as soap_cat,
chxf.pk as pk_clin_hx_family,
chxf.fk_hx_family_item as pk_hx_family_item,
null::integer as pk_narrative_condition,
null::integer as pk_relative_identity,
hxfi.name_relative as name_relative,
hxfi.dob_relative as dob_relative,
hxfi.condition as condition,
hxfi.age_noted as age_noted,
hxfi.age_of_death as age_of_death,
hxfi.is_cause_of_death as is_cause_of_death
from
v_pat_items vpi,
clin_hx_family chxf,
hx_family_item hxfi,
v_basic_person vbp
where
vpi.pk_item = chxf.pk_item
and
hxfi.pk = chxf.fk_hx_family_item
and
hxfi.fk_narrative_condition is null
and
hxfi.fk_relative is null
UNION
=-- those linked to another patient as relative
select
vpi.pk_patient as pk_patient,
vpi.pk_health_issue as pk_health_issue,
chxf.clin_when as clin_when,
chxf.fk_encounter as pk_encounter,
chxf.fk_episode as pk_episode,
chxf.narrative as relationship,
chxf.soap_cat as soap_cat,
chxf.pk as pk_clin_hx_family,
chxf.fk_hx_family_item as pk_hx_family_item,
null::integer as pk_narrative_condition,
hxfi.fk_relative as pk_relative_identity,
vbp.firstnames || ' ' || vbp.lastnames as name_relative,
vbp.dob as dob_relative,
hxfi.condition as condition,
hxfi.age_noted as age_noted,
hxfi.age_of_death as age_of_death,
hxfi.is_cause_of_death as is_cause_of_death
from
v_pat_items vpi,
clin_hx_family chxf,
hx_family_item hxfi,
v_basic_person vbp
where
vpi.pk_item = chxf.pk_item
and
hxfi.pk = chxf.fk_hx_family_item
and
hxfi.fk_narrative_condition is null
and
hxfi.fk_relative = v_basic_person.pk_identity
UNION
=-- those linked to a condition of another patient being a relative
select
vpn.pk_patient as pk_patient,
vpn.pk_health_issue as pk_health_issue,
chxf.clin_when as clin_when,
chxf.fk_encounter as pk_encounter,
chxf.fk_episode as pk_episode,
chxf.narrative as relationship,
chxf.soap_cat as soap_cat,
chxf.pk as pk_clin_hx_family,
chxf.fk_hx_family_item as pk_hx_family_item,
hxfi.fk_narrative_condition as pk_narrative_condition,
vpn.pk_patient as pk_relative_identity,
vbp.firstnames || ' ' || vbp.lastnames as name_relative,
vbp.dob as dob_relative,
vpn.narrative as condition,
hxfi.age_noted as age_noted,
hxfi.age_of_death as age_of_death,
hxfi.is_cause_of_death as is_cause_of_death
from
clin_hx_family chxf,
hx_family_item hxfi,
v_basic_person vbp,
v_pat_narrative vpn
where
hxfi.pk = chxf.fk_hx_family_item
and
hxfi.fk_narrative_condition = vpn.pk_narrative
and
hxfi.fk_relative is null
and
v_basic_person.pk_identity = vpn.pk_patient
;
_______________________________________________
Gnumed-devel mailing list
[email protected]
http://lists.gnu.org/mailman/listinfo/gnumed-devel
