Barcode databases - the issues

["mark_harrison_uk2" <mph@xxxxxxx>]

Here we go again :-)

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

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

I've done a fair amount of work on these commercially, for several=20
employers.

The technology side of setting up a database that maps Barcodes ->=20
Product names is trivial.=20

The arguments about whether it's in MySQL, PostGRES, or SQL*Server=20
are fun, but ultimately pointless. The inherent data structure behind=20
these product databases is SO simple, that any SQL programmer could=20
write a line of code that generates, in turn, a full SQL String to=20
input all the data back into any of the other databasese.

The complexity is in the mapping from Product -> Barcodes, and the=20
integrity of the data set.

At the simplest, level, consider a 330ml can of Coke. Three different=20
people buy one, and scan in the barcode. One of these people has=20
bought a "standard for resale" with a UPC (Universal Product
Code),=20
one has bought a four-pack and scanned one of the cans (with a=20
different UPC), one has bought a co-branded can with a Tescos-
operated football promotion with a PPC (Private Product Code).

Two of these three enters "Coke", one enters "Coca
Cola" as the=20
description. Both enter "330ml" as the size...

Going FROM the barcode to the product is trivial for someone=20
interrogating the database.

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


One of the companies I worked for signed a joint deal with Spar to=20
share product information. Spar put out a big press release saying=20
that downloading the product information from their database had made=20
integration easy. This was rubbish. I had a team of 4 vacation=20
students in the local Spar for a fortnight with barcode scanners and=20
laptops re-doing everything.


Here are some fields for the "product table" in a database that
you=20
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]


Example:

- PepsiCo
- Pepsi
- Max
- Soft Drinks
- 330
- ml
- No

- Example:

- Tesco
- Milk
- Semi Skimmed Organic
- Dairy Products
- 4
- l
- Yes