Barcode DB Technical WAS: Re: barcode scanner and automated shopping

[Stuart Grimshaw <stuart@xxxxxxx>]

On Thursday 09 Oct 2003 11:55 am, Mark McCall wrote:
> There must be standard field names for an EAN13 or whatever surely?

Looking at the UPC Database it's just 2 CSV files, with no field names
or=20
anything, so not much guidence there.

Looking at this box of tea in front of me, I would suggest a schema as
simp=
le=20
as:

create table tblItem (
intItemID int(11) unsigned AUTO_INCREMENT NOT NULL,
intManufacturerID int(11) unsigned NOT NULL,
vchBarcode varchar(25) NOT NULL,
vchProductName varchar(100) NOT NULL,
intWeight int(11) unsigned,
intContents int(11) unsigned,
txtNotes text
)

create table tblManufacturer
intManufacturerID int(11) unsigned AUTO_INCREMENT NOT,
vchManufacturerName varchar(50),
txtNotes text
)

create table tblBarcodeClass (
intBarcodeClassID int(11) unsigned NOT NULL,
vchDescription vch(50) NOT NULL,
)

INSERT INTO tblBarcodeClass (0, 'Standard'), (2, 'Variable Weight'), (3,=20
'Pharmaceuticals'), (4, 'In store use only'), (5, 'Coupons');

Excuse the MySQL style syntax, it's what I'm using at work, and so I have
m=
y=20
MySQL head on. I would implement this in PostgreSQL.

I would also recommend keeping the product database and any tables that an=
=20
application might need seperate, so there's no need to have supermarket
ite=
m=20
codes or a table that holds re-order levels in the product DB, they can be=
=20
application specific. Someone might want to use the product DB to store
inf=
o=20
on the CD's or DVD's they buy for example, which don't require re-order=20
levels, and most of the Big 4 supermarkets don't stock Cisco routers
either=
.