Re: [Gnumed-devel] Re: GNUmed substance schema was Re: GNUmed and FreeDiams

[Karsten Hilbert]

On Thu, Jan 06, 2011 at 01:22:34AM +0100, Karsten Hilbert wrote:

> > I did some experimenting on uniqueness in the Canadian brand names, and
> > even if I convert
> > 
> >     brand_name --> concat (brand_name, strength)
> > 
> > the Canadian brands are still not unique… only 12489 of 12756 become
> > distinct, because some "generic" companies do not care to make a unique 
> > brand
> > name, thus two or more manufacturers can achieve the same {brand_name,
> > strength}. I would really much prefer to *not* have to make fake Brand 
> > names as
> > concat (brand_name, FreeDiams unique ID) …
>
> How about only expanding the original name if there actually are
> identically named drugs ?  That would make for, what, 300 unhappy
> names out of 12500 ?

I still like this idea despite changing the UNIQUE.

> > this way, in the absence of {external_code, external_code_type} the check
> > falls equivalent to the original requirement
> > 
> >     UNIQUE (description, preparation)
> 
> No it does not because in SQL
> 
>  (NULL == NULL) = NULL
> 
> rather than TRUE or FALSE
> 
> > but in the presence of {external_code, external_code_type} we can enable
> > to exist a brand name / preparation distinguished by these other attributes.
> 
> I'll see if I can think of something useful.

I have found a way to express in SQL what you desired.

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