Tech Data modeling question... logic in identifiers?

mangowife

Seriously tempted...
O.G.
Aug 8, 2006
3,200
122
Too far from the beach!
(Okay... I really hope this is the right place for this question...)

Calling all data modelers/DBAs/developers... what's your philosophy when needing to create unique identifiers - should logic be embedded in the ids? For example, if I'm creating a unique id for companies, would it be a good idea to concatenate a ticker plus exchange code to create the id? Or is a sequential numeric id be a better idea?

I feel pretty strongly that logic should not be embedded into id creation, but was curious how others felt.

Thanks for humoring me!
 

gloss_gal

O.G.
May 3, 2006
3,286
0
I agree, I don't think the key should mean anything and a sequential number is sufficient in most cases. Say you have an ID that includes logic that defines a region, currently you have 4 regions (North, South, East, West) (00001N, 00001S). What if the regions get so large that you have to break them up (Northeast, Northwest, Southeastm Southwest,...) (00001NE, 00001SW). It gets messy. Of couse there are times went it makes sense to possibly incorporate logic into your identifier (maybe trying to retrofit into an older system) but those cases should be rare. Also the key can be sequential (non numeric).
 

Charles

Sucks at budgeting
O.G.
Jan 5, 2007
15,473
3,474
46
Jacksonville, Fl
I don't like concatenating strings/numeric values to create a Unique/Key ID. I prefer straight numeric. If something more user friendly is needed, then I'd create a reference table based on the numeric Key ID mapped to a non Key ID user friendly identifier.
 

merika

Wol
O.G.
Nov 1, 2006
10,409
105
A sequential numeric id would be better. As Charles said you could add a couple lines of code to use a reference table to map the numeric id into a user friendly identifier.

EDIT: you could create a non sequential numeric id which is based on certain properties as well. For example if you wanted North, South, East and West, you could give them values 1,2, 3, 4 and add that as the last two digits of your numeric ID, so that 103321 would mean 10332North and so on.

Then when you want to mine the data, it is easy to create a loop in the code that recognizes the last character and puts it in a correct regional category.
 
Last edited:

robyn

Are we there yet?
O.G.
Mar 31, 2006
1,097
1
57
D.C. suburbs
Having done it both ways, I prefer to use a sequential numeric ID and a separate "label," but most of what I am doing is either in Stata or SPSS which both make it very easy to label your variables.
 

gloss_gal

O.G.
May 3, 2006
3,286
0
I agree with the post that the key should be non-numeric. Just stating cases where it may not be.

Robyn, hi neighbor, I am in DC!
 

LaudiAlison

Member
Aug 14, 2009
54
0
Chicago,IL
My two cents: it depends. Logical ids are sometimes the way to go - like Starbucks. They have to create logical ids for every drink because it's not practical for baristas to remember drink number 10,456. However, they do require writing more error checking.

No matter what it's best to make the key for the table a sequential numerical automatic id. The question comes when do you create another supposedly unique id that isn't marked that way in the table. And that has everything to do with how you're going to use the data.

Also if you use logical ids with alphanumeric sequences you have to take out characters that look like each other - 0 and O for instance.