Background

Product data often has intelligent keys, such as product codes and IDs. One of the most common is the UPC (Universal Product Code).

xDM can generate these intelligent keys to help the business set up unique IDs. This applies to domains, such as Product, Customers, Locations, and many more.

In this article, let's look at how xDM can help generate UPCs to provide products with unique IDs that follow patterns or rules. This example extends to other use cases where it is important to follow an algorithm to generate keys and IDs.

How UPCs work

You likely recognize UPCs when you see a barcode.

Example of a UPC-A barcode. Source.

This system is a standardized identifier that allows tracking items commonly sold in retail stores.

UPCs have 3 components:

  1. The company's prefix or manufacturer code (which is assigned by the local GS1 organization)
  2. A code that the company generates (product code)
  3. A check digit, which is calculated based on the previous digits.

Because there is a precise algorithm to calculate and validate UPC check digits, UPC is a great example of how xDM can generate intelligent keys. You can follow a similar pattern for your intelligent keys.

How xDM can generate UPCs

SemQL Enrichers can help you generate UPCs.

These are the 3 components you will define in the data model.

  1. Manufacturer code: Define the manufacturer code. If there are multiple manufacturer codes, you can create this as a reference basic entity.
  2. Product code: Create a product ID. You can use a sequence on the primary key generation in the entity. Add logic in the product code as necessary for covering cases like local uses, loyalty cards, or coupons.
  3. Check digit: Use a custom function to calculate the check digit.

For PostgreSQL users, here is a working example of a function that can generate the check digit.

 /* Source: https://stackoverflow.com/a/63104631
  * Calculates the check digit at the end of the UPC 
  */
 CREATE OR REPLACE FUNCTION intelligent_data_hub.usr_gs1_calculate_check_digit(code TEXT) 
 RETURNS TEXT
 AS $$
 #variable_conflict error
 DECLARE
  digit SMALLINT;
  digit_3x BOOL DEFAULT TRUE;
  digit_sum SMALLINT DEFAULT 0;
  digit_index SMALLINT DEFAULT 1;
  digit_count SMALLINT;
 BEGIN
 

  IF code IS NULL THEN
  RETURN '';
  END IF;
 

  digit_count := LENGTH(code);
  IF digit_count = 0 THEN
  RETURN '';
  END IF;
 

  code := REVERSE(code);
 

  LOOP
  digit := SUBSTRING(code, digit_index, 1)::SMALLINT;
  IF digit_3x THEN
  digit := digit*3;
  END IF;
  digit_sum := digit_sum + digit;
  digit_index := digit_index + 1;
  IF digit_index > digit_count THEN
  EXIT;
  END IF;
  digit_3x = (NOT digit_3x);
  END LOOP;
 

  RETURN CASE WHEN digit_sum%10=0 THEN digit_sum ELSE (digit_sum/10+1)*10 END - digit_sum;
 

 END;
 $$ LANGUAGE PLPGSQL;

Source: Stackoverflow.

Here is an example of how to configure a SemQL enricher to generate a UPC.

1. Create the reference entity (for example, UPCPrefixCertificate) to store the manufacturer code.

2. Use a sequence in the Item (or Product) entity.

Optional: Add extra necessary logic to the product code.

3. Compile the function usr_gs1_calculate_check_digit in the schema.

4. Register the function under Database Functions in the data model.

5. Create a new SemQL enricher (for example CalculateUPCCheckDigit) to call the usr_gs1_calculate_check_digit function. You can save the value in a new attribute or you can immediately concatenate the check digit to the manufacturer code and product code to create a UPC.

I prefer to save the check digit into its own attribute for troubleshooting purposes but this is unnecessary for Production.

6. Create another SemQL enricher (for example GenerateUPC) to concatenate the manufacturer code, product code, and the check digit to generate the UPC.

NOTE: If your use case is to validate the check digit is correct, rather than generate a UPC, you can follow a similar SemQL expression and place it in a validation rule.

7. Ensure the CalculateUPCCheckDigit SemQL enricher comes before the GenerateUPC SemQL enricher. You are chaining enrichers together so you need to have the GenerateUPC SemQL enricher come after so that the check digit is available.

Conclusion

Here is an example of what the different pieces look like to generate the UPC:

Your use case may vary from generating a UPC. But the same principles apply. Use SemQL Enrichers to generate the intelligent keys you need. You can chain enrichers together. And you can compile custom functions for performing advance calculations if necessary to extend beyond SemQL.