[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] family history table
From: |
Karsten Hilbert |
Subject: |
Re: [Gnumed-devel] family history table |
Date: |
Sun, 13 Mar 2005 13:26:11 +0100 |
User-agent: |
Mutt/1.3.22.1i |
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
- [Gnumed-devel] family history table, Karsten Hilbert, 2005/03/12
- Message not available
- Re: [Gnumed-devel] family history table,
Karsten Hilbert <=
- Re: [Gnumed-devel] family history table, Richard Terry, 2005/03/15
- Re: [Gnumed-devel] family history table, Karsten Hilbert, 2005/03/17
- Re: [Gnumed-devel] family history table, Karsten Hilbert, 2005/03/17
- Re: [Gnumed-devel] family history table, Karsten Hilbert, 2005/03/17
- Re: [Gnumed-devel] family history table, Richard Terry, 2005/03/20
- Re: [Gnumed-devel] family history table, Karsten Hilbert, 2005/03/21
- Re: [Gnumed-devel] family history table, Richard Terry, 2005/03/21
- Re: [Gnumed-devel] family history table, Karsten Hilbert, 2005/03/21
- Re: [Gnumed-devel] family history table, Karsten Hilbert, 2005/03/21