Re: Barcode databases - the issues

[Stuart Grimshaw <stuart@xxxxxxx>]

On Saturday 11 Oct 2003 1:32 pm, mark_harrison_uk2 wrote:
> Here we go again :-)

and with two of the same people, you would have thought we'd learned :-)

> It's easy to get into a "it's trivial to build a barcode
database",
> but unless we work FIRST to determine what the data will be used for,
> there is a real danger that the data will be fundamentally stuffed
> since there's been no definition of integrity requirements.

The idea is to store just barcodes and info on the products they relate
to,=
=20
nothing more nothing less. Give it a barcode, it tells you what it is.

Any other functionality will be down to whoever is using it, re-order
level=
s,=20
recipe suggestions etc will be down to individual authors/applications.

> For example, an obvious thing to do would be to query "what's the
> barcode for a can of coke", or "what's the barcode for
organic milk".

It would normally be the other way around in this instance, since we
alread=
y=20
have the barcode, we want to query the DB for a description etc, if
there's=
=20
no match, then well there's no match, type it in yourself :-)

> I've done a fair amount of work on these commercially, for several
> employers.
>
> The technology side of setting up a database that maps Barcodes ->
> Product names is trivial.
>
> The arguments about whether it's in MySQL, PostGRES, or SQL*Server
> are fun, but ultimately pointless. The inherent data structure behind
> these product databases is SO simple, that any SQL programmer could
> write a line of code that generates, in turn, a full SQL String to
> input all the data back into any of the other databasese.
>
> The complexity is in the mapping from Product -> Barcodes, and the
> integrity of the data set.
>
> At the simplest, level, consider a 330ml can of Coke. Three different
> people buy one, and scan in the barcode. One of these people has
> bought a "standard for resale" with a UPC (Universal Product
Code),
> one has bought a four-pack and scanned one of the cans (with a
> different UPC), one has bought a co-branded can with a Tescos-
> operated football promotion with a PPC (Private Product Code).

Do they tend use the category digit at the start of the barcode (5 is it
fo=
r=20
private barcodes) or do they just stick to the standard 0 ?

> Two of these three enters "Coke", one enters "Coca
Cola" as the
> description. Both enter "330ml" as the size...
>
> Going FROM the barcode to the product is trivial for someone
> interrogating the database.

And that's what the database is designed for, if people want to use it to
g=
o=20
the other way around, then fine, but they will run into the problems you=20
describe.

> Going FROM a requirement for a "330ml can of coke" will turn
up 2 of
> the barcodes, but not the third. Even worse, one of the barcodes may
> have been for a limited-time PPC, so isn't available any more.

I can't think of a situation in the home where you might want to do this,
b=
ut=20
I'm open to suggestions ... ?

>
> One of the companies I worked for signed a joint deal with Spar to
> share product information. Spar put out a big press release saying
> that downloading the product information from their database had made
> integration easy. This was rubbish. I had a team of 4 vacation
> students in the local Spar for a fortnight with barcode scanners and
> laptops re-doing everything.
>
>
> Here are some fields for the "product table" in a database
that you
> might like to consider. Items marked with * are keys on another table.
>
> - Manufacturer * varchar
> - ItemName varchar
> - ItemSubname varchar
> - ItemClassification varchar *
> - ItemSizeNumber int
> - Item size char
> - Organic boolean [default=3D"No" for ease of inputting]

That's roughly what we had, apart from the size char (though I did have=20
contents for things like 80 Tea Bags, that also have a weight)

I can send you the schema I'd come up with if you'd like to cast your
beady=
=20
over it...