[Gnumed-devel] diagnosis tables

[Karsten Hilbert]

Please consider the enclosed tables for storing diagnoses.

Discussion, comments, criticism solicited.

-- ============================================
-- diagnosis tables
-- --------------------------------------------
-- patient attached diagnosis
create table clin_diag (
        pk serial primary key,
        fk_aux_note integer
                references clin_aux_note(pk),
        is_chronic boolean
                not null
                default false,
        is_active boolean
                not null
                default true
                check ((is_chronic = true) and (is_active = true)),
        is_definite boolean
                not null
                default false
                check ((is_active = true) and (is_definite = true)),
        is_significant boolean
                not null
                default true
                check ((is_active = true) and (is_significant = true))
        unique (description, id_episode),
        unique (description, id_encounter)
) inherits (clin_root_item);

-- diagnosis name stored in clin_root_item.narrative

select add_table_for_audit('clin_diag');


-- "working set" of diagnoses
create table lnk_diag2code (
        pk serial primary key,
        description text
                not null,
        code text
                not null,
        xfk_coding_system text
                not null,
        unique (description, code, xfk_coding_system)
) inherits (audit_fields);

select add_table_for_audit('lnk_diag2code');
select add_x_db_fk_def('lnk_diag2code', 'xfk_coding_system', 'reference', 
'ref_source', 'name_short');

comment on TABLE lnk_diag2code is
        'diagnoses as used clinically in patient charts linked to codes';
comment on column lnk_diag2code.description is
        'free text description of diagnosis';
comment on column lnk_diag2code.code is
        'the code in the coding system';
comment on column lnk_diag2code.xfk_coding_system is
        'the coding system used to code the diagnosis';

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