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.

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.

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