2

Background

I'm working through creating a structure for Items in a new ERP (Cloudsuite Industrial) that I'm implementing. For Items (which can be finished goods/products or raw materials), we currently have a three level hierarchical system of organization. I'm trying to determine the best/standard practice for mapping that information into Product Codes.

In CSI, Product Codes can be Alphanumeric along with a few special characters such as hyphen and underscore and have a 10 char max limit.

Simplified Example Structure:

Structure

  • ItemType 1
    • Division A
      • Category 1
      • Category 2
      • Category 3
    • Division B
      • Category 1
      • Category 2
  • ItemType 2
  • ItemType 3

Example

  • Finished Goods
    • Cars
      • Sedans
      • Trucks
      • SUVs
    • Boats
      • Yachts
      • Rowboats
  • Sub Assembles
  • Raw Materials

Initial Thoughts on Product Code Structure:

Structure

[ItemType] + HYPHEN + [Division] + HYPHEN + [Category]

Examples

FG-CR-SED (Finished Goods - Cars - Sedans) FG-BT-Y (Finished Goods - Boats - Yachts

Concerns

When presenting this idea to higher-ups, the response was, "why not just do sequential numeric?" CSI provides a separate text field for description which is shown beside the product code. That will mean that if I have a code of "FG-CR-SED", then on the page beside it, it'll show another text box with "Finished Goods, Cars, Sedans", so there is no real NEED for the code itself to be smart/human readable.

However, what I don't see is an actual downside to using smart/human readable codes. Seems like it might be useful if the data is exported, because the code it part of the Item record itself, while the code's description is a separate table.

I'd like to get some feedback on what other ERP users typically do for setup of this.

Thanks!

Edit

Based on the answers here and on discussions on a CSI Linkedin group and on the ERP subreddit, we've decided to go with a hybrid:

  • FG-01
  • FG-02
  • ...
  • RM-01
  • etc, etc

This is the best of both worlds. It fits with CSI's recommendations of the simple "FG", "RM", etc. It gives up to 99 possible categories within each top level group. And because the categories are numeric, if we change how we refer to one (change "Cars" to "Vehicles", the product code doesn't change.

7

Your current requirements do not take into consideration the effect of time: sooner or later, your company will modernize its catalogue or reorganize its organizational structures. Items will then have to be organized differently:

  • If you opted for a dumb sequential numbering, you don't care: you just change some attributes of the item. And there you go!

  • If you opted for a meaningful numbering, that embeds structural information, it'll be terrible:

    • You'll have to break the former numbering and people will be confused.
    • Moreover, the meaningful numbering could have hidden some requirements for filtering the catalogue according to additional criteria. So the reshuffling might even disrupt some processes.
    • Item labels on the stock shelves or printed on the boxes cannot be changed just overnight like in a database?
    • Finally, customers out there might have ongoing orders, or long term contracts for these items. So that you cannot change the external reference like that to adopt a new numbering: this would be a painful transitioning however you'll be doing it.

So yes: Why not just sequential numeric? Is there any tangible argument against it, being understood that you can always have a popup or a link showing additional item attributes, or even print what's needed on labels if it's relevant.

| improve this answer | |
  • "sooner or later, your company will modernize its catalogue or reorganize its organizational structures. Items will then have to be organized differently" - if this logic were followed to conclusion, the firm would never organise its catalogue. It would just list one damn item after another and be unable to speak in shorthand about any common features. Better to think of product organisation as something that, once settled, provides a valuable conceptual framework and mnemonics for the business. Change destroys that value, but you don't avoid the costs of change by never producing the value. – Steve Jul 21 at 13:39
  • 4
    @Steve companies do this all the time. I even have examples where people came with a structure at the beginning of project to learn from thier boss the new structure before the end of the project. Changes are necessary. 30 years ago, IT ressellers had catalogues with 16 and 32 bit computers as main categories. Nowadays this categorization is useless. keeping it would have destroyed value. So let business people decide on business value. At our level we promote agility in catalogue management: sequential numbering is invariant, and item attributes can change as often as needed :-) – Christophe Jul 21 at 13:48
  • My argument is not against necessary changes. My point is that the costs of it have to be factored against the gain and then swallowed - they can't be pre-empted by avoiding commitments. Making everything abstract itself imposes costs or leads to deprivation of value. Either "Sedan" (or "16-bit") is a meaningful category for the business for the time being, and the cost of changing those categories and that vocabulary has to be swallowed when the time comes, or else it becomes impossible for anyone in the business to communicate about sets of items with a shared vocabulary. (1/2) – Steve Jul 21 at 14:20
  • And in reality, what happens in such cases where a business does not provide categories and vocabulary for them, is that people devise their own categories local to themselves or their team or their division, and the catalogue-keepers devise their own unspoken categories (and maybe even keep secret cheat-sheets), so in practice the business ends up with what you were trying to avoid (a rigidly organised catalogue), and all the costs of change when change happens, but the organisation of the catalogue is not shared in common or under anyone's control. It really is a fool's gold. (2/2) – Steve Jul 21 at 14:26
  • 1
    The argument pro-sequential-numeric also applies pro-meaningless-unordered-alphanumeric types, such as a GUID. The issue isn't whether it's sequential or not, or numeric or not, it's whether the value has any inferred meaning to it (e.g. "all blue cars' IDs must start with BL"), which is the thing you're best to avoid so as not to have to rework your identification when the content of the article changes (e.g. we no longer track car color) – Flater Jul 24 at 12:14
1

In the ERP I'm developing for, all tables have a surrogate and almost all tables have a natural key. As you can see from Christophe's and Ewan's answers, both types of keys have their pros and cons. So it makes some sense to have both, although this adds complexity. Relationships in this ERP are often done with the surrogate keys, but these keys are almost never shown to the user. Instead, when the data is shown to the user, the surrogate keys are replaced with their natural keys. This is a basic behavior/functionality for all data structures in this ERP and might be overkill to implement for just your specific scenario.

Not sure which approach I would pick in your scenario if I had to choose. Take a good look at your requirements and the pros and cons of each approach.

| improve this answer | |
  • To clarify, wouldn't keys be unique per item? I am not asking about SKUs or UniqueIDs. I'm talking about what Cloudsuite calls Product Codes, which are generic categorization codes applied to all items in certain categories. One product code might have 300 items that it's assigned to. – Andy Mercer Jul 22 at 12:20
  • 1
    I'm assuming that the product codes are stored in a data structure separate from the item data structure. Then one field in the item data structure would reference (either by natural or surrogate key) the product codes data structure. So there are unique keys for items and unique keys for product codes. Alternatively, if one item can be assigned to more than one product code, you have a n:m relation, which is usually done with another table that only holds a reference to item and product code. Not sure if this clears it up for you, open a chat room if you want to discuss this further. – FH-Inway Jul 22 at 15:34
0

What you are comparing is natural keys vs surrogate keys.

Your natural key is productType = SEDAN a surrogate key might be productType = 7

My main problem with surrogate keys is that when humans start to use them they gain a pseudo meaning and become the natural key. Before long instead of saying, "show me a report of all the SEDANS we sold this year" the business will say "show me a report of all the type 7 products we sold this year".

Then someone starts selling a new type of SEDAN and they don't remember that 7 == SEDAN and you get productType = 124 == SEDAN2

Technical problems can occur when you merge data and have two different type 3's

I would caution you against compound key as well. If you go with product type "FG-CR-SED" and then someone later decides that SEDANS are vehicles not cars you are in trouble.

There are a whole host of other problems you might run in to as well

  • parsing the code when you don't always have the same hierarchy ie. FG-CR-SED and FG-CPTAMERICADOLL
  • length limits FINISHEDGOODS-CARS-SEDAN--FOURDOOR-SUPERCOOL
  • reserved characters FG,CR,EIGHT-WHEELER

If something has a natural key, use it. Just have Sedans be Sedans, Yachts, Yachts etc

| improve this answer | |
  • So the main downside, from your perspective, is that if the organizational structure changes down the line, then then a product code such as FG-CR-SED might no longer apply. Wouldn't it be pretty easy to just make a new product code of FG-VH-SED and bulk change all of the items that have that code to the new one? – Andy Mercer Jul 21 at 12:55
  • There seem to be two schools of thought about surrogate keys. See this answer from 2012 and the discussion in the comments. I would like to add that I agree mostly to Robert Harvey here, though some of the concerns against surrogate keys are valid, I have used them very successfully in several systems, so the mentioned issues are often manageable, to my experience. – Doc Brown Jul 21 at 13:03
  • Also found this interesting article about natural keys vs surrogate keys. – Doc Brown Jul 21 at 13:10
  • @DocBrown yeah I agree with robs comments, but it relies on the humans not using/knowing the surrogate key. Which is fine for guids. cos they never will, but bad for small auto incs, cos they will AND you will get dupes – Ewan Jul 21 at 13:30
  • 2
    @AndyMercer not if you have ever printed one out, or sent an email with one on, or have it hard coded in a dozen management reports, backups, 3rd parties etc etc etc basically no, you can't ever change it – Ewan Jul 21 at 13:32

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.