Showing Primary Keys in Lookup/Combo Boxes

[PhillD]

In a lot of older developed systems, I see a lot of Textual based Primary keys used for lookup values in tables. E.G. Order Status R = Ready, S = Shipped, C = Complete or in our product codes table CF = Cage, OBS = Barrel etc.

In today’s programming world, is it necessary to use textual keys for these types of values? Given the advent of the combo box, it is a lot easier to use a numerical key and simply show the description to the user in the combo box.

There are a lot of spin off issues that arise because of this. For example, if you use a hidden numerical key, the user isn't aware of the actual data they are entering, thus causing maintenance problems later down the line. On the other hand, this makes programming easier, especially when the combo boxes only allow single column of information to be displayed. Also, it makes the programmers job easier because you don’t have to manually maintain the primary key data.

I would love some feedback on this, I am developing a system and this practice has raised some questions.

 

[cjard]

Text based keys allow more values in fewer characters and can provide a memory jog if chosen sensibly. I use text based values for enums

"..this transaction type column here.. What is 62, and 39? I'm looking for Load or Redeem. I'll have to go to the decode table or the documentation to find out whether 39 is the load or the redeem..."

Or the programmer could have picked L and R


In .net, we have Enums as a way of giving "names" to "magic numbers" in code. Magic numbers should always be avoided as they don't help the code self document:

If myDialog.Result = 4 Then 'wtf?
If myDialog.Result = DialogResult.Cancel Then 'readable

But then we come back to magic numbers. When stored in a DB, these column values will be numbers, and atable will ahve to decode then, and SQLs must look like:

SELECT * FROM people WHERE ethnicity = 17 --14=pacific islander

Someone changed the 14 to 17 but then couldnt be bothered updating the comment - code turns to a dog's dinner

Whether you show the user this code or not really depends on whether they need to know it. Maybe they type it, for speed, and the combo just helps them choose?


As a rule of thumb, avoid numbers because they are meaningless.. If enumerated values never leave .NET, use enums (particularly if they are flags enums where values combine).. in other systems, try to make them text ones so that it's easier for developers to come to know them without having to hit decode tables all the time

 

[PhillD]

I have read you response a few times and I understand where you are coming from.

As a general rule of mine, I would not use a meaningless key where the user would actually have to know the value. It would only ever be used as the value portion of a dropdown box. The user would only see the description.

For example, with an order status, it makes sense to use textual values for the primary key O = Ordered, H = Hold, C = Complete. The user would not necessarily have to enter the codes O, H or C they would only see the description portion in the combo box.

With larger datasets, such as vehicles, it would make sense to have a "meaningless" Primary key where the user does not have to enter a unique value to represent a vehicle. The key would just be a sequential number and the plate would be the description field that the user picks from the combo.

However, things like EmployeeID's Item ID's, these are values where the user will need to enter the primary key and most likely, want to reference the data by it's primary key. So the combo box would show both the employee ID and the employee Name for employee's and item and item description for items.

I am wondering if there is a general categorization for the different types of information and general best practices for displaying it. Or is it common sense on behalf of the programmer?

 

[cjard]

I usually consider who uses the info and what sort of level they are likely to be at, are they coming from a legacy system where they have certain learned patterns of behaviour etc and also address any existing concerns they have

Suppose youre replacing an ordering system that has certain shipping codes and the users are au fait with the codes.. You can use numbers internally, and include the codes in the visible values and they can still use the combos etc, because they are sorted in codes order (or use a grid so they can sort another column if they want)
But then you find out that they have some guy who's a SQL whizz and will want to run reports.. And he's used to the codes, so he wants to write SELECT * FROM shipped WHERE code = 'uk' without messing about, looking in shipping_code_lookup_table to find out that uk = 17, or write: SELECT * FROM shipping INNER JOIN shipping_codes_lookup_table ON blahblah WHERE lookup_text = 'UK'

If its a brand new ordering system, and users have no muscle memory that "I" is an invoice and "E" is an estimate then you can make these numbers if you like.. Actually if you think about this laterally, numbers and letters are the same thing: numbers are fields that store data in base10 using the symbols 0 to 9, letters are fields that store data in base.. er.. 62 if input is restricted to [a-zA-Z0-9].. I have several fields where I need to store an incrementing counter of huge maximums in a small width, and thats a base62 number 0..1..2.. ..9..a..b.. ..y..z..A..B.. ..Y..Z..10..11..
The number 999999999 (nine wide) is 15ftgF (6 wide). The max value is 'zzzzzzzz' which is 218340105584895 in decimal - nearly twice as long


In terms of vehicles, you could use the reg plate as PK, or if its a car sales website and youre listing makes and models, then a number is fine. In a banking system that doles out account numbers, an autonumber as the PK.

Every context is different, and i typically try to find something about the data itself that makes a PK rather than just slamming an autonumber on everything - typical trick of newbie/bad programmers whose first experience was Access and they just clicked yes to the "you don'thave a PK, want access to add one for you?" because they didnt understand the question

Any table that shouldnt allow duplicates by definition has some combination of data that must be unique and that makes for PK

 

[PhillD]

Thanks for taking the time to explain this. This will help me with my projects.