I’m curious of people’s opininos on whether it makes sense (in general) to use alphanumerics or numbers in various primary keys. Please assume a farily large number of records for each. Do you prefer numbers, alphas for the following:
Item No.
Customer No.
Vendor No.
Location Code
I’ve seen some arguments for and against. I frequently see people like numbers only because it becomes easier to “code in” logic to the numbers and therefore manage a lot data. Others say they try to keep it mostly alpha because it’s easier for users to read to makes reports “self documenting”; we don’t need to modify a bunch of reports to include descriptions.
Also if you’re familiar with LS retail I’ll throw the following into the mix:
Numeric values in code fields can create problems for users since the field is a Varchar and will sort as text. It’s one of the issues I’ve seen when converting users from native to SQL.
For all these cases, I would rather see a Letter in position one. (A-Z) the issues with sorting and searching are problemati other wise. I would only have full integers as bbrown says, by mandating integer only, but even then I don’t like it.
If you have integers at the begining (even if its alpha, eg 1102DD01 or 0021B4) there is always going to be an issue with droped leading "0"s. Cases where there is Item 000234499D1FF and somewhere else there is 2034499D1FF92A.
So I alwasy recomend to clients to have A-Z as the first charachter, then pretty much the rest is OK.
Technically, when you have an Item No. ‘0021’ that is not a number but a string with numerical characters. If you really want to get it to perform fast, an integer value is best, but then you’re going to see multiple things in the Navigate form that coincidentally have the same number.
Personally I don’t like giving numbers a meaning. If the ‘number’ “001004005” means Item 001 in color 004 and size 005, then I would say give the Item No the value ‘001’, and add a field for the other attributes. What happens if the customer wants to change the numbering scheme for colors or sizes? The Item No is just for identifying the record from the database, and it should not contain any other attributes. You could still add a custom field with the concatenated values, or provide for a custom search screen to enter the number as 001004005, which then internally breaks it apart.
I have one particular client that had a definite need for a smart numebr scheme. It would involve an Item No of 35 charachters. I fought very hard, and luckily won, and now Item numers are sequential, and basically not used for anythign. We then have a new “Smart number table” where they build the details of the item. Its been a huge success, and over the years we have been able to do so much that would have been impossible had they integrated this into the Item No.