How to structure data... Sequential or Hierarchical?

Question

I'm going through the exercise of building a CMS that will organize a lot of the common documents that my employer generates each time we get a new sales order. Each new sales order gets a 5 digit number (12222,12223,122224, etc...) but internally we have applied a hierarchy to these numbers:

+ 121XX
|--01
|--02
+ 122XX
|--22
|--23
|--24

In my table for sales orders, is it better to use the 5 digital number as an ID and populate up or would it be better to use the hierarchical structure that we use when referring to jobs in regular conversation? The only benefit to not populating sequentially seems to be formatting the data later on in my view, but that doesn't sound like a good enough reason to go through the extra work.

Thanks

Answer 1

"There is a special hell reserved for people who give meaning to sub-fields in numerical identifiers."

While I can certainly appreciate and value that remark, I feel compelled to add that if the business is such that this happens to be the numbering system that is used for orders, and if it happens to be the case that the business does attach meaning to the sub-fields, then the IT people implementing the automated system supporting that business have little choice left but to go by that numbering system.

(Those IT people might point out the disadvantages of such systems, but it does not pertain to IT people to dictate what the business stuff ought and ought not to look like.)

Answer 2

This looks like a system for organising OS directories or manila files in cabinets than a data model. So the question is, what is the value of this two-tier structure? Are there any attributes or functions attached to the higher tranche (121XX, etc)? If not, this becomes a mere display issue and should be handled as such.

On the other hand, I don't see any real harm in implementing (121XX, 01) as a composite business key and using a surrogate key as as the primary key. There is one snag to watch out for: if the sales pick up you may reach 100K orders, at which point the order number will click over to six digits. In most flavours of database the natural sort order would be:

100XX
1000XX
101XX