1

The harmonized system uses a numbering that shows the hierarchy between different items. Similar to bookkeeping ledger numbers. Here is an example:

01  LIVE ANIMALS
0103  swine, live
010310  Purebred breeding animals

For each level there are two digits available, and this can go down in my case to up to eight digits in total (so four levels). I would like to catalog the harmonized system codes and their descriptions, but wonder what a good data type is to use in MySQL. These numbers are unique so I would like to make them my Primary Keys as well. Given the hierarchy, I should be able to do some SELECTs that allow me to find out the child records. What I am thinking of is using some sort of INT type, and then doing things like this:

SELECT * FROM accounts WHERE CAST(id AS TEXT) LIKE '01%'

Which should allow me to find the child accounts that fall under the '01' category. The problem here would be to work with the leading zeros. If I use zerofill, then 0103 will become 00000103, so that is not ideal. But if I do not use zerofill then this will be 103. Again not ideal.

Using a text data field instead would solve this, but this feels quite wrong as these are clearly numbers. Alternatively I could make several columns - each for the number group - but this again feels like a weird solution. Any advice is appreciated.

| improve this question | | | | |
  • In this system are any of the lower-level codes re-used under different parents? For example might live/ cattle/ purebred be "019910" with the last "10" being understood to be the exact same "10" as is used for swine? – Michael Green Jul 7 '14 at 12:43
  • Any system that requires leading 0s should be taken out behind the barn and shot. – Hellion Jul 7 '14 at 13:45
2

"The harmonized system uses a numbering" - they are not numbers, they are codes. They just happen to use digits instead of Latin letters for the code values.

As you have to retain leading zeros anything numeric will not suit. You have to go with textual types. Suppose the codes were

A01        LIVE ANIMALS
A01B03     swine, live
A01B03C10  Purebred breeding animals

I suspect you would have no concerns about using text? Just think of them this way, but with the A, B and C removed. Or think of your alphabet as consisting of 36 characters, a to z and 0 to 9.

| improve this answer | | | | |
  • Thanks a lot! This makes so much sense! I have another question about the parent/children relations but it's a different subject so I'll create another question for that. – please delete me Jul 8 '14 at 14:33

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.