[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] next GNUmed release
From: |
Jim Busser |
Subject: |
Re: [Gnumed-devel] next GNUmed release |
Date: |
Sun, 18 Sep 2011 13:30:04 -0700 |
On 2011-09-18, at 9:02 AM, Jim Busser wrote:
> I would prefer us to resolve the clin.vaccine schema question in which I
> proposed that
>
> id_route
>
> be removed from the table.
The reason being that I would like the schema (v16) to be reasonably
well-settled so that drug data packs (which can include vaccines) can also be
settled.
Would this resolution require more time than any deadline for, say, next debian
or ubuntu?
1) If time is short
then the minimum resolution can be:
make no changes to the table column existence, BUT
allow clin.vaccine.id_route to be null, so that generic vaccine does not assign
a route, where the route was unknown or wrong (relative to vaccine's attribute
in the backend)
AND inform the user via GUI which (if any) route description property belongs
to the selected ref.branded_drug vaccine
2) an in-between solution would be
drop the column id_route from clin.vaccine or
allow id_route to be NULL
and to hold an array of integer route values per single
ref.branded_drug
redefine fk_brand as needing to be unique in clin.vaccine
display (in the GUI)the route(s), where clin.vaccine.id_route IS NOT
NULL
3) a better long-term approach would be to rethink whether the table
clin.vaccine is redundant:
i) clin.vaccine needs referential integrity to a brand, but
ii) a branded drug product can be compatible with more than one route of
administration, and
iii) is_live is not specifically a property of the vaccine, it is rather a
property of each component of a vaccine… a single vaccine which immunizes
against more than one indication in some cases contains a mixture of non-live
pathogen antigens, and live…
but even if we continue the current model, which allows fake brands ignorant of
what was *in* a particular vaccine, then the link table
clin.lnk_vaccine2inds
could be remapped to link to ref.branded_drug, instead of to clin.vaccine. The
column clin.vaccine.is_live would be as defined in
ref.consumable_substances.is_live
which, in the case of fake vaccines, would not exist causing the attribute
is_live for fake vaccines to be null AS IT SHOULD, because where we lack
original information on what was given, why are we insisting to have unknown
knowledge of whether that historically given vaccination was (necessarily)
live? What does it *matter* ??
We can determine which among ref.branded drugs to present in the vaccine
phrasewheel, based on the optional addition of a column is_vaccine to
ref.consumable_substances as
SELECT DISTINCT r_bd.description
FROM ref.branded_drug r_bd
WHERE r_bd.is_fake IS TRUE
OR
position('J07' in r_bd.atc_code) = 1
OR
r_bd.pk IN (SELECT 1
FROM ref.branded_drug r_bd
INNER JOIN ref.lnk_substance2brand l_s2b
ON r_bd.pk = l_s2b.fk_brand
INNER JOIN ref.consumable_substance r_cs
ON r_cs.pk = l_s2b.fk_substance
WHERE position('J07' in r_cs.atc_code) = 1 OR
r_cs.is_vaccine IS TRUE
LIMIT 1)
ORDER BY r_bd.description
;
-- Jim