Post reply

should I use "barcodes" as a primary key?

  • 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

     

     

  • December 28, 2005 at 1:11 pm

    #611767

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 28, 2005 at 1:31 pm

    #611776

    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

  • December 28, 2005 at 4:27 pm

    #611817

    Anytime and definitely a varchar. Perf should be fine.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 28, 2005 at 8:16 pm

    #611837

    "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

  • December 29, 2005 at 4:13 am

    #611880

    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

     

  • December 29, 2005 at 7:25 am

    #611905

    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.

  • December 29, 2005 at 8:28 am

    #611919

    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.

  • December 29, 2005 at 9:44 am

    #611953

    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.

  • December 29, 2005 at 11:22 am

    #612005

    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

  • December 30, 2005 at 10:29 am

    #612189

    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

  • January 2, 2006 at 2:20 am

    #612264

    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

  • January 6, 2006 at 4:07 pm

    #613408

    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.

  • January 11, 2006 at 5:46 am

    #614023

    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

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

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