Mysql – Best way to store currency values on MySql database

Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:

UOM Conversion              

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.

That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.

That sort of coding format is simply a representation of a tree or forest - each code represents the node's location in a hierarchy where each node (except the root node(s)) has exactly one parent.

That hierarchy could be directly modelling the physical world (as Dewey Decimal Classification does for books in a library) or it could be something more "virtual".

I don't think this sort of coding has a generally recognised name, so if you need a concise phrase for use in your documentation just pick something short that results in a relatively unambiguous acronym, be consistent with its use, and make sure it is included in your terms of reference. Perhaps "Hierarchical Encoded Identifiers", "Hierarchical Classification", "Business Location Codes", or similar.

Don't be hung up on it being numerical: this is not significant as there are no mathematical operations that are meaningful when applied to these codes. The codes could be using any set of characters, even multiple characters per level in the hierarchy, and for this system someone has chosen to limit the alphabet of the encoding to the characters 1, 2, 3, ... It would be just as meaningful for the encoding to use other characters or to have separators between each position, so your last two examples could be MQE/EOG/CM/CM/BaL & MQE/EOG/CM/CM/BUM or MXCCL & MXCCU in similar schemes with different alphabets - all operations would be equally meaningful (searching, lexical sorting, extracting the meaning back from the code, and so forth) or not (mathematical operations) in these encodings.