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.
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
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.
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]. 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?