Should I Use Numeric or Alphanumeric IDs

Question

I am constructing a sql database with each record having a unique id as my primary key. I want the ids to be human readable with various parts representing sub-classifications. For example the first two-three digits represent product category, the next two-three represent location, and the last records the item. If I use a numeric code, I will need at least nine digits for each number. Example, 101-001-001 would mean category one, warehouse aisle one, item one. If I use alpha-numeric I could represent the same data with six digits, A1-A1-A1.

What is the difference in space required to store each number (alpha-numeric vs numeric)? Since these id numbers will appear millions of times in my database, size considerations would be helpful in deciding which way to go. Is there any other reason I should prefer one over the other?

Answer 1

Using encoded keys is generally a bad idea. For instance, what happens if -- at some point in the future -- a warehouse has more than 999 items. You have no space for it in the code.

Instead, use an auto-incremented (identity) integer for the id. This occupies 4 bytes, unless you are going to be supporting billions of rows in your table. In that case, use a big integer.

You can have separate columns for the warehouse number, the aisle number, and the item number. These, in fact, should probably be foreign keys to reference tables.