1

I am building a product database and I need to store GTIN/EAN (Global Trade Item Number/European Article Number) values. Probably except GTIN-14 as it does not seem to be used at retail sales? I read the first digit is used to show some packing information but it is not globally agreed which numeric value represents what exactly?

Anyway, I tried to find out how they work to understand which column type would be appropriate. I used bigint when I started my little project. But now I wanted to investigate the issue deeper.

https://www.gs1.org/sites/default/files/docs/barcodes/GS1_General_Specifications.pdf Page 31 says:

Note: GTINs may be stored with or without leading zeroes in the same database field, depending on the requirements of the particular application.

Note: A GTIN-12 may start with one, two or three leading zeros. These zeroes are meaningful since they are part of the U.P.C. Company prefix, and therefore these must be preserved when storing the GTIN-12 in a database field. For the list of U.P.C. Prefix ranges see section 1.4

https://www.gtin.info/ says:

GTIN is term referring to how the data is stored, i.e., padding the item number with zeroes to a uniform length.

https://www.gs1.dk/media/1246/udi-guideline.pdf Page 14 says:

GTINs should be represented in software applications as 14 digits by right justifying and zero-filling to the left as appropriate. In order to preserve any leading zeros that may be present, the GTIN field should be represented ina database as a textfield, not as a numeric field.

https://www.barcodefaq.com/1d/upc-ean/ says:

To be fully GTIN compliant, all UPC and EAN numbers should be stored in databases as 14-digit numbers and filled in with zeros for the spaces to the left as shown in Fig: 1. For example, a UPC-A barcode of 123456789012 would be represented as 00123456789012 in the database of a GTIN compliant system.

Then I found that PostgreSQL has a module for this called [isn][1]. There it says:

Internally, all these types use the same representation (a 64-bit integer), and all are interchangeable.

First of all, I did not quite understand why one would need to store zeros in database if they are for padding only. It made no sense to me. (except for GTIN-12)

So, I was also thinking 64-bit integer sounds a reasonable type to store EAN values as I used bigint. Now I am thinking should I have used isn extension? But I am using Doctrine on PHP/Symfony and it may be difficult to get it to understand ean13 column type and need to do custom mapping etc.

Perhaps somebody can tell if simply using bigint could cause troubles in the long run? I thought GTIN-14 can contain all other types and I do not necessarily need conversion between types in the database end. So I don't think there should be any problems but perhaps somebody else may have hands on experience? Any suggestions?

Thanks!

  • 5
    Since you're not going to perform arithmetic calculation upon product codes (at least I hope you won't), there is no reason to store the values as numeric. Store them as strings. – mustaccio May 10 at 19:07
  • 3
    Store it as a string. All of the big ERP systems do – Philᵀᴹ May 11 at 11:52
  • mustaccio so you think for example ID values should be stored as string because you never do arithmetic calculations on them? That is a very weak argument. Philᵀᴹ any links? at least the PostgreSQL database developers think bigint is the way to go. The problem is that it is much easier and faster to access bigint values compared to variable or fixed length string. They occupy only 8bytes an entry while 14character string will take 14bytes and database can index bigint much more efficiently. – yurtesen May 12 at 13:19
  • 2
    You're not asking about any old IDs. You're asking about GTIN/EAN codes specifically, which the GS1 specification defines as combinations of strings. – mustaccio May 13 at 14:12
  • 1
    @yurtesen: you don't need to store the number of leading zeros if you store the GTIN as a number. You just have to (left) pad the number with zeros to 12 digits when you display it. – a_horse_with_no_name May 16 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.