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.

Kelderic
  • 131
  • 6

3 Answers3

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.

Robert Harvey
  • 198,589
  • 55
  • 464
  • 673
Christophe
  • 74,672
  • 10
  • 115
  • 187
  • *"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 '20 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 '20 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 '20 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 '20 at 14:26
  • @Steve I think we agree that the days of the old rigid hierarchical catalogues are gone. Today successful businesses let user find articles with a search engine, or a flexible tag system. This is one reason more not to adopt the hierarchical structure for the item. – Christophe Jul 21 '20 at 15:01
  • That's specific to consumer-facing retail websites serving occasional purchases. It's not necessarily the best way to organise things in all circumstances though. I don't want to labour my point, but I'm narrowly concerned with the idea that the way to avoid the costs of reorganisation is to avoid organisation. Even the biggest and most complex military logistics still have a hierarchical approach to storekeeping - it doesn't necessarily become exhausted with size or scale, when the purpose is to serve the routine needs of a business operation, not to serve passing consumer traffic. – Steve Jul 21 '20 at 15:54
  • I recently had to tackle a similar problem with a catalogue. In the absence of categories, it had become unfathomable to identify what items were of a kind or compatible with one another, and some product variants existed as codes but which had no corresponding human description, and there were irregularities and errors in published prices. These faults were only discovered once attempts were made to introduce rhyme and reason. Experienced staff employed categories and knew of exceptions, but not the same ones as one another, and it was difficult to articulate or reproduce this knowledge. – Steve Jul 21 '20 at 16:03
  • 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 '20 at 12:14
  • @Steve: I think the core argument Christophe is making is that the identification of individual entities should not be tightly coupled to arbitrary groupings of those entities (whether by category, trait, search term or how edible it is). For any system that undergoes a change, the groupings are much more liable to change than the identifications (for backwards compatibility). When you separate the identification from the grouping, then the identification should logically not rely on the current grouping criteria, hence the posted answer. – Flater Jul 24 '20 at 12:18
  • @Flater Thank you. It was indeed the purpose of my answer. I said, numeric to echo the sentence in the question and also because it’s easier to increment, but you could do alphanumeric as well, and I even would not be against an control digit/letter to avoid typos and their consequence. Just not structural infos that could much more flexibly added to and changed in the entity without affecting the identity of the item :-) – Christophe Jul 24 '20 at 12:35
  • @Flater, categories like "sedan" and "16-bit" changed far less frequently than the products in them. Whole companies rose and fell more quickly. Also, keys do not have to be wholly meaningful - they can consist both of categorical and sequential elements. My point is that people don't get into the way of thinking "our grandfathers were silly fools, what with their obsessive naming and categorising, when we could have just randomly numbered everything!". Good structure adds enormously to the ergonomics of any system. (1/2) – Steve Jul 24 '20 at 15:55
  • It's possible to poorly categorise things, and it's possible to over-categorise leaving no flexibility, but the general advice definitely should not be a sweeping "don't categorise", at least not for any part of a system which humans touch and communicate about. Anything printed on goods containers will likely require human descriptions and categories anyway, and if you start saying "we won't print anything but the code" you're going to drive your consumers round the twist when they receive a delivery of opaque nondescript boxes and random codes. It's not an acceptable solution. (2/2) – Steve Jul 24 '20 at 16:15
  • @Flater, (I feel like I'm writing excessively) on this "identification ... should not be tightly coupled to arbitrary groupings", I agree. To be clear, the point is to create a system of canonical groups which are relatively stable for your industry, sector, factory, or other purpose, and a key system based on this which is capable of some flexibility or extension in future. Even if groups are rejigged in the fullness of time, it's still easier to deal with (and reproduce knowledge of) two key ranges for one group, rather than people dealing with unstructured information from the start. – Steve Jul 24 '20 at 16:45
  • @Steve: You're arguing against points that no one is making. _"general advice definitely should not be a sweeping "don't categorise""_ I didn't say "don't categorize", I said "don't categorize using unique identifiers". _"if you start saying "we won't print anything but the code" you're going to drive your consumers round the twist when they receive a delivery of opaque nondescript boxes and random codes"_ At no point has anyone been discussing that articles should _only_ have a unique identifier and cannot contain any other properties or data. – Flater Jul 24 '20 at 23:58
  • @Flater, Christophe's answer certainly spoke of the difficulty of updating printed material. It's perfectly feasible that things will be organised on stock shelves according to category, for example. If the category is not in the product key, then it's printed elsewhere on the label. And if labels now need to be updated when the system of categories changes, because the labels "incorporate meaningful information" which controls or assists how the humans deal with them, what effort have you saved with the sequential key? My point is not to come down hard for one particular approach here... – Steve Jul 25 '20 at 01:23
  • ...it's to emphasise that categories, patterns, shorthands, etc. are useful (in all sorts of ways) to the *people* who comprise a business. Don't *assume* outright that you can just dismiss the use of "meaningful information", including in product keys, just because the computer seems happy either way – Steve Jul 25 '20 at 01:46
  • @Flater, and I've just re-read everything so far to make sure I wasn't getting the wrong end of the stick. Christophe originally finishes a one-sided analysis by asking rhetorically "Why not just sequential numeric?", and I put what was intended to be a one-shot comment, attempting to say something on that within the length limit. The summary is that you don't just sequentially number everything as a rule, because real businesses usually involve product info being handled extensively by people for whom brevity, structure, and efficiency counts, not just by computers for which it does not. – Steve Jul 25 '20 at 02:07
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.

FH-Inway
  • 119
  • 5
  • 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. – Kelderic Jul 22 '20 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 '20 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

Ewan
  • 70,664
  • 5
  • 76
  • 161
  • 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? – Kelderic Jul 21 '20 at 12:55
  • There seem to be two schools of thought about surrogate keys. See [this answer from 2012](https://softwareengineering.stackexchange.com/a/174872) 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 '20 at 13:03
  • Also found [this interesting article](https://www.techrepublic.com/article/the-great-primary-key-debate/) about natural keys vs surrogate keys. – Doc Brown Jul 21 '20 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 '20 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 '20 at 13:32