Connecting Companies

Connecting companies to products is not as simple as it use to be. As you may have read in the help section, the first 6 digits of the UPC-A code (or first 7 digits of EAN13) identify the manufacturer of the product.

Well this isn't completely true any longer. Yes that portion of the code is still issued to a company by GS1 but some compaies that own the first 7 digits of the EAN13 code are selling off smaller subsets of the numbers to companies that don't need that many product numbers. In some cases, codes are being sold off individually.

These subsets make connecting companies to products much more difficult. By default, we connect the product to the GS1 issued owner of the set of product codes. But when we find that the products are part of a subset we add the subset owner to the database and connect to them instead.

You may be wondering how we do that without a 1-to-1 connection in the product list. Well it isn't all that hard when you think about things starting at the company side. It is a little tougher when you are starting from the product side working back to the company.

First of all, you need to work with the full EAN13 code to keep things simple. EAN13 is a superset that includes all of the UPC-A codes. If you have a UCP-A code with 12 digits, you simply add a zero to the front to make it an EAN13 code and you are set.

Selecting all products for a company

This part is pretty easy. Once you have the company code (7 or more digits) you can quickly select all products that start with that code. Here is an example SQL command to do the job:

select * from products where code like '1234567%'

This will return a list of all products that have codes starting with 1234567 in no particular sequence. I am going to assume you know the basics of SQL because this is not a database training session. I am also not going to cover any programming specifics. This is just about concepts.

Selecting a company for a specific product

This is where it gets to be a little harder. We don't know in advance if the product belongs to a company entry with 7, 8, 9 or more digits. So how do we get the correct (or closest) company record back?

The answer is, you can't. Well you can't be 100% sure that you will only get one company record back. You can't really even be sure you will get any company record back. It is possible that there is no company data stored for the specified product then you are out of luck. But your program will be able to handle what ever comes back if you simply search for the company that best matches the product and sort them in order from the longest company code to shortest.

select * from companies where '1234567890123' like CONCAT(code,'%') order by length(code) desc

Please be aware that this SQL is a concept and many not execute in your database server as it sits. The idea is, we know the full 13 digit product code, we have the company codes stored as 7, 8, 9 or more digits and since they are subsets, the longest company code is going to be the one that owns the product.

Using this method, you may get back no records but assuming you get some, you only want to keep the first record and discard the rest. This is because the second (third or so on) records simply belong to companies that sold off subsets of numbers. They don't actually make the product and may have nothing at all to do with the product.

Programming

How you actually implement these concepts is totally up to you and is tied to how your program will work. There are so many variations on language, style and need that it is not possible for us to cover the specifics you will use in your program to get the data you want.

We store our data in several formats within the database. For example we have the code fields stored as text with all 13 digits but for speed we also store them as large integers with no leading zeros in place. This makes searching a little faster which adds up in the long run. But how you store things is totally up to you.

Hopefully, this page has given you some basics that will help you get your program running and make it easy for you to connect companies to products in an effecient way.

Our Data

Our database makes this process easy by performing the match any time a product is updated or a company is added. We perform the tedious query once and store the results so the next time we need to connect a product to a company the process is very fast. If you download our data you will get this connection infomrmation to help you on your way. Be aware that as you add companies and products, you will need to maintain this connection yourself.

Close

Search

Close

Share

Close

Dialog