CODEWITHSUNDEEP
sql
parap
parap

Reputation: 1985

Should I Use Numeric or Alphanumeric IDs

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?

Upvotes: 3

Views: 3619

Answers (6)

Ivar Harris
Ivar Harris

Reputation: 156

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

Upvotes: 0

Dave
Dave

Reputation: 11

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.

Upvotes: 0

James
James

Reputation: 698

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.

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

I'm suggesting you to use Separately.

Try like this

create table #b 
(
id int identity (1001,1),
userid as 'C' + cast(id as varchar(10))
)

insert #b default values
insert #b default values
insert #b default values
insert #b default values

select * from #b

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28781

Storing values signifying multiple information in same column is bad idea . Suppose you wan to fetch records of particular aisle . You will have to apply split function on each column value to retreive aisle number and match .

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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.

Upvotes: 2

Related Questions