should I use "barcodes" as a primary key?

Question

should I use "barcodes" as a primary key?

yogiberr

Hall of Fame

Points: 3679
More actions
December 28, 2005 at 12:35 pm

#364497

hiya,
I want to create a schema for tblProduct that includes a "barCode" field.
I want to use the barcode as a primary key (because I don't want the dataEntry people keying in duplicate barcodes..I understand that the dataEntry people could "scan" the barcode from a label.However, this still doesnt' stop them from inadvertently scanning the SAME label for 2 different products)
However, something is telling me that I should be using the standard sqlSever generated "auto identity" to uniquely identify the products.
I'm using yukon.
Any comments appreciated.
many thanks,
yogi

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

If you mean the representation of the code as the numbers, then I'd use that. If you mean an image of the code, I wouldn't use that.

You can setup the primary key as any fields and it will enforce unqueness and prevent someone from inserting new values with the same bar code. Be sure your application handles those.

I would recommend choosing a real key such as a bar code when possible. If you are not sure if you have a real key, or natural key, then the identity (autonumber) fields work well as a surrogate.

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

righto,

cheers bud.I'll use the barCode, that HAS to be unique.It was the size of the thing that was putting me off (8 digits).

I now realise of course that I can just store it as VARCHAR...I think it was the fact that I thought I should be storing it as a whole number that was ringing the alarm bells.

many thanks,

yogi

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736162

December 28, 2005 at 4:27 pm

#611817

Anytime and definitely a varchar. Perf should be fine.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

"I'll use the barCode, that HAS to be unique."

Actually, no it does not need to be unique. I ran into this problem a few years ago in the health care industry where medical equipment, telcom equipment and blood donations could have the same bar code !

The most common identifier is EAN-13, which has replaced the US Universal Product Code (UPC). EAN-13 is 13 numbers.

Check with your subject matter expert because there are many other specialty identifier, such as those on shipments, telecommunications equipment and perhaps passports.

Here are some of the identifiers that are translated into barcodes:

EAN-13: European Article Numbering international retail product code

EAN-8: Compressed version of EAN code for use on small products

UPC-A: Universal product code seen on almost all retail products in the USA and Canada

UPC-E: Compressed version of UPC code for use on small products

Code 11: Used primarily for labeling telecommunications equipment

Interleaved 2 of 5: Compact numeric code, widely used in industry, air cargo, other applications

Industrial 2 of 5: Older code not in common use

Standard 2 of 5: Older code not in common use

Codabar: Older code often used in library systems, sometimes in blood banks

Plessey: Older code commonly used for retail shelf marking

MSI: Variation of the Plessey code commonly used in USA

PostNet: Used by U.S. Postal Service for automated mail sorting

SQL = Scarcely Qualifies as a Language

yogiberr Hall of Fame Points: 3679 More actions · Answer 5

cheers, I'll look into it.I'm just grateful that it's only sandwiches, rather than medical equipment I'm dealing with.

many thanks,

yogi

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 6

Also, bar codes can change especially when a wholesaler or repackager changes manufacturer or there is a merger or acquisition in the supply chain. This may not apply in your case, but it is something to consider.

My wife works in retail and the system she uses doesn't treat bar code as a primary key, but they use an internal SKU (stock keeping unit). The UPC is just an attribute. That fact is helping the store through the transition to the new longer numbers that are more universal than UPCs.

BTW, retail is an "interesting" application domain for many reasons. You should have lots of challenges.

Ryan Hathaway SSC Eights! Points: 827 More actions · Answer 7

I agree with the others, using the barcode as your primary key is a great idea, as long as it can meet all your needs. If you do use it, make sure you evaluate whether it should be a Unique/clustered (default for Primary keys), or if it should be Unique/non-clustered. This could have a big impact on your performance if this identity will be used heavily in your application.

Arthur Smoot Newbie Points: 5 More actions · Answer 8

For Retail in general, that's not a good idea. Manufacturers sometimes re-use barcodes if they have stopped making a product. It is also common for barcodes to change when the product does not change, for instance when one manufacturer is bought by another. Both of these can foul up your sales records and reorder systems. It is better to make your own 'SKU' to keep track of ordering and sales.

SQLBill SSC Guru Points: 51440 More actions · Answer 9

You can also use multiple columns as your primary key. If you think the barcodes might be reused some day, you could do something like:

Barcode Vendor

000001 Acme

000002 Acme

000001 Jones

Make the combination of Barcode and Vendor (or whatever combination meets your requirement) your primary key.

-SQLBill

iurisilveira SSC Rookie Points: 41 More actions · Answer 10

I Have a few POS systems and I use internal SKU as primary key.

Barcode is stored in a separate table related to the Product table by SKU.

You might have the same sandwich supplied by different vendors with different barcodes. All pointing to the same product.

Regards,

IGS

Vladan SSC-Insane Points: 22072 More actions · Answer 11

I have to agree with those who warn you against using barcode as primary key. Apart from re-using and changes of barcode, there is also a possibility that a product will receive the same barcode as another one by mistake, or that a product will not have any barcode at all. I've seen it several times even with EAN (13 places) of books... the same publisher assigned one EAN to 2 books. It happens rarely, but if you have it as a primary key, even one occurrence of duplicity is too much .

I think that auto IDENTITY number is best candidate for a primary key of products... unless you are able to find some 100% reliable composite key.

HTH, Vladan

Chris Castle Old Hand Points: 381 More actions · Answer 12

Since no one else is doing it, here goes the identity argument.

Use an identity. Also create a unique index on the vendor/bar code columns. Everyone here has said that barcodes can change. That means if you sold a bunch on an item, you would have to cascade the updated barcode to all of the other tables in the database. Why bother? Also, I would rather carry around 1 column to designate a product than the vendor/barcode pair. You will always be joining using an integer instead of two strings.

I have to go now before all of the identity columns are bad people come chasing me.

yogiberr Hall of Fame Points: 3679 More actions · Answer 13

hiya,

Thanks for all the replies folks.The company actually print their own barcodes, but it's good to get input about all the potential barcode pitfalls.

cheers,

yogi