Re: [Gnumed-devel] Might ref.consumable substances need to allow an amount of zero?

[Jim Busser]

On 2011-09-05, at 12:34 AM, Karsten Hilbert wrote:

> Or rather:
> 
>       alter table ref.consumable_substance
>       add constraint ref_consumable_uniq_subst_amount_unit
>               unique(upper(description), amount, unit);
> 
> since units can be intended to mean something different
> depending on case.

Yes, the above is more technically correct, except duplicates will end up 
resulting on account of (for example) the Canadian data is supplied entirely in 
upper case. Thousands of drugs denoted with strengths of

        MG

which literally should be Mega-Gigas !*^!!!

The argument against what I proposed rests on it doing some injustice to a 
substance which wants to be created but cannot because it is not allowed to 
share the same units (just in a different case) as an existing substance, and 
bear in mind this only applies when the description and the amounts are also 
shared. In order for this to be a real scenario, it seems it would depend on a 
substance being supplied at one billion times a lower strength e.g

        MU
vs      mU

whereas the risk of duplicates (which will be unable to be pruned, on account 
of them getting variously keyed into patient records) is a real one.

Can we accept to make the constraint

        unique(upper(description), amount, upper(unit))

and we could always liberalize it later upon getting a real complaint (?)

-- Jim