Re: Private Re: [Gnumed-devel] gnumed ideas 0.1 and post-0.1

[Ian Haywood]

Karsten wrote:
> > that dosn't fit AU, including some of the database table design.
>
> Should there be anything in the schema that can not accomodate
> AU needs I should like to know about it now. 
Unified path/document tracking (which we have discussed at length I know),
is the only area where I think we've "lost out" to system closely matching 
German requirements
at the expense of ours.
We can use lab_report.fk_med_doc to "emulate" tracking but this is a big ugly 
hack
IMHO. Additionally I think lab_report could be better normalised (which would
solve both problems at a stroke, I attach an example)
What puzzles and (slightly) annoys me is I don't understand what's "Australian" 
about
this requirement, I can't imagine any medical scenario where you wouldn't 
absolutely need it.

drugs and billing are the two big areas we haven't tackled, I suspect
(particularly for billing) we may end up having au_ and de_ tables,
but for everything else I'm confident we can come up with a generic solution.

Ian

CREATE TABLE doc_med (
        -- all medical communications go here.
        pk serial primary key,
        fk_patient integer
                not null
                references xlnk_identity(xfk_identity)
                on update cascade
                on delete cascade,
        fk_sender_identity integer references xlnk_identity(xfk_identity)
                on update cascade
                on delete cascade,
        fk_sender_org integer, 
        type integer
                not null
                references doc_type(pk)
                on update cascade
                on delete restrict,
        comment text,
        "date" timestamp with time zone
                not null
                default CURRENT_TIMESTAMP,
        ext_ref text.
        technically_abnormal text
                default null
                check (
                        (reviewed_by_clinician is false)
                                or
                        (reviewed_by_clinician is true) and 
(technically_abnormal is not null)
                ),
        norm_ref_group text,
        note_provider text,
        material text,
        material_detail text,
        reviewed_by_clinician boolean 
                not null
                default false,
        fk_reviewer integer
                default null
                references xlnk_identity(xfk_identity)
                check(((reviewed_by_clinician is false) and (fk_reviewer is 
null)) or (fk_reviewer is not null)),
        clinically_relevant boolean
                default null
                check (((reviewed_by_clinician=false) and (clinically_relevant 
is null)) or (clinically_relevant is not null))
)
);


create table test_result (
        pk serial primary key,
        fk_doc_med integer references doc_med (pk),
        fk_type integer
                not null
                references test_type(pk),
        val_num numeric
--      val_num float
                default null
                check (
                        ((val_num is not null) or (val_alpha is not null))
                                or
                        ((val_num is null) and (val_alpha != '') and (val_alpha 
is not null))
                ),
        val_alpha text
                default null,
        val_unit text,
        val_normal_min numeric,
        val_normal_max numeric,
        val_normal_range text,
        val_target_min numeric,
        val_target_max numeric,
        val_target_range text
);


CREATE TABLE doc_obj (
        pk serial primary key,
        fk_doc_med integer
                not null
                references doc_med(pk)
                on update cascade
                on delete restrict,
        seq_idx integer
                not null,
        comment text,
        data bytea
);

signature.asc
Description: OpenPGP digital signature