'foreign key with default null' problem in allergies view

Warning: long post

Sam,

> I have been reading along with the development notes for a while.  In my
> practice, allergy information is critical and collected on the first
> visit in all patients.
Now, there's some encouragement for carrying on :-)   Not sure
how we scale up to HIPAA, though.

> Would it be practical to have a default value of
> 'none' in the allergy table so that id_comment is not null?
That's sort of already the case and is really the root of the
evil. I'll cite some SQL here to illustrate the fact:

-- ===================================================================
-- clinical narrative aggregation
-- -------------------------------------------------------------------
create table clin_narrative (
    id serial primary key,
    id_patient integer not null,
    src_table name,                    -- references pg_class(relname) ??
    value text
) inherits (audit_clinical);

-- --------------------------------------------
create table allergy (
    id serial primary key,
    id_clin_transaction integer not null references clin_transaction(id),
    substance varchar(128) not null,
    substance_code varchar(256) default null,
    generics varchar(256) default null,
    allergene varchar(256) default null,
    atc_code varchar(32) default null,
    id_type integer not null references _enum_allergy_type(id),
    reaction text default '',
    generic_specific boolean default false,
    definate boolean default false,
    had_hypo boolean default false,
    id_comment integer references clin_narrative(id) default null
) inherits (audit_clinical);
-- ===================================================================

Note the line:
 id_comment integer references clin_narrative(id) default null

This effectively means that either there's a comment to this
allergy recorded in the clin_narrative table OR id_comment is
null.

All is fine if we do SELECTs on    the allergy table. If there's
a comment we'll get its ID, if there's no comment we'll get
NULL.

However, note that a) the patient isn't directly recorded in
the allergy table (for reasons of normalization) and b) when
we retrieve data about an allergy we couldn't care less about
the *ID* of it's comment (or type, for that matter) but we
*do* care about the associated patient (and type and
comment)...

Hence, one would create the following view for easier access
to the relevant fields:

-- ===================================================================
create view v_i18n_patient_allergies as
select
    a.id as id,
    vpt.id_patient as id_patient,
    a.id_clin_transaction as id_clin_transaction,
    a.substance as substance,
    a.substance_code as substance_code,
    a.generics as generics,
    a.allergene as allergene,
    a.atc_code as atc_code,
    a.reaction as reaction,
    a.generic_specific as generic_specific,
    a.definate as definate,
    a.had_hypo as had_hypo,
    _(at.value) as type,
    cn.value as "comment"
from
    allergy a, _enum_allergy_type at, clin_narrative cn, v_patient_transactions vpt
where
--    cn.id=a.id_comment
--        and
    vpt.id_transaction=a.id_clin_transaction
        and
    at.id=a.id_type
;
-- ===================================================================

(Don't get confused about the _i18n_ and _(at.value) stuff,
 that's just so you will see "sensitivity" while I will see
 "Unverträglichkeit" ...)

In the view definition we need to tell the select what data
to, well, select :-)   We want all the rows from
clin_narrative that have the ID that's recorded in those rows
in allergy that match the rest of our criteria (such as
being a transaction that belongs to our patient):

 allergy.id_comment = clin_narrative.id

BUT: allergy.id_comment can also be NULL (namely to denote
that, indeed, there's no comment recorded on that allergy) and
will thus not reference any row in clin_narrative. This makes
the above comparison fail.

I have unsuccessfully tried:

 allergy.id_comment in (clin_narrative.id, NULL)
 (allergy.id_comment=clin_narrative.id) OR (allergy.id IS NULL)

Basically, it's probably rather easy and just a matter of
saying what I mean in SQL but I can't get my head around it
currently.

Yes, this can be solved by putting a default comment into
clin_narrative but that's not a clean way of going about
things, IMHO.

Karsten

PS: Sorry for the cross-posting to pgsql-general but this is a)
PostgreSQL stuff and b) technical enough. I do admit it's just a
silly newbie's (me, that is :^) question.
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346