Should I Use Numeric or Alphanumeric IDs

user_2077583 0
Viewed 2466

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?

sql
6 Answers

Way too late for this answer but in case someone jumps into this question:

I'm doing research on how to handle products and their ID's, I myself don't like the ID to be say "3" and or "1", seems too short IMO.

Said that, I've come to agree to the fact that product ID's should be auto increment integer values.

About your question:

I think you're looking for the SKU (Stock Keeping Unit). The SKU is useful to form a structured alphanumeric string that contains information about your product and it is normally set by the retail store individually (meaning is not universal, like UPC [Universal Product Code or Barcode]).

You can use the SKU to identify product category, retail store and much more (you define your structure) and unlike the product ID, the SKU is more of an attribute than an entity identifier number.

Hope this helps as it helped me understand the difference of use between this fields.

user_29511430

Sorry, but auto increment IDs are a bad idea if you ever expect to have redundant servers. You cannot sync auto increment columns between databases.

Come up with a scheme that will produce a random, highly unique alpha-numeric value for your primary key so when you do scale beyond one server, you won't have any collision issues.

user_59542890

Here's a great way to generate a unique id that is not based on auto increment:

Select newid()

the result looks like this:

9b5d2ec1-c968-4760-a12d-dfeae80547fc

They say that newid() "generates sequential 128-bit identifiers that are collation compatible with SQL Server as a clustered primary key."

Here's a reference to some details about using newid():

https://masstransit-project.com/architecture/newid.html#the-problem

user_171952250
Related