1

Is it bad practice to use an incrementing primary key to refer to a real world field, such as an id number for an employee? Suppose that 1 is a valid id number for an employee. I am really tempted to use the incrementing primary key as the id number. But I might have ignored some potential problems it might cause. I've read that it is not bad to expose the primary key to the user (just like what SO does). Is what I'm doing bad practice?

  • 3
    Depends on what you mean by "bad practice." – Robert Harvey Oct 28 '17 at 4:47
4

The primary key's one and only job is to be the primary key. Ask it to do something else and suddenly it has reasons to change that have nothing to do with being the primary key.

A famous example of this was when Steve Jobs found out he was going to be listed as employee #2 after Wozniak at #1. He couldn't take this so he insisted that it be changed. Rather than demote Woz Jobs wanted to be employee #0. Well the system couldn't do this. Jobs was not pleased.

Why was it a problem? Because the system was showing its internal structural info to the customer. This is as wacky as letting the customer know the name of a table or that you store their middle name in the 4th field. There is no reason for the customer to know or care about this. But if you let them know about it they will find a way to care about it. Now you either have to change, or can't change, things that shouldn't have been an issue in the first place.

Infact I'd advise starting any published employee ID numbers with a meaningless random number, even if you do increment it, just to keep the programmers who do see it from thinking it's the same as the primary key.

If you need the fancy words for this here they are: this is basic encapsulation, information hiding. It's the best way to ensure decoupling.

3

Primary key is meant to uniquely identify a record. If the record is something that can be identified in the real world by some attribute (an employee definitely fits that description), then choose something that is immutable through time and space. GUID might be a good choice, but it can be a bit too big in some cases.

One of the reasons why it is not a good practice to use autoincrement as an ID for items that can be identified externally is that it might cause problems when the data is migrated from one database to another, or when you need to integrate two systems. Imagine you have two systems that work with the same set of employees. If you use autoincrement as a primary key, there would be no way to uniquely map employees from one system to employees in another. However, if employees are identified by some unique code, you would not have that problem and you could integrate those two systems rather easily.

Use autoincrement with records that are vast in numbers and cannot be identified by anything other than the whole record (in some cases, not even by that). The examples of that might be some measurements, which are identified by measurement device ID, timestamp of the measurement and the measured value, or the cross-reference tables that can have multiple records pointing to the same records in the referenced tables.

1

Auto-incremented PK's, integer or otherwise, are not inherently problematic. "Best practice" for a PK is for it not to relate to real world attributes of the object. The PK exist to uniquely identify an entity in the event that other attributes change -- even seemingly permanent attributes, which are subject to corrections.

The PK should "never" need to change, barring an architectural overhaul or data migration. So, as long as your PK fits that bill, it's not "wrong" or "bad" for any entity, inherently, real-world or otherwise.

That said, there can be challenges when using an auto-incrementing PK. Namely, uniqueness is more difficult to achieve among hosts. In the event that data host A and B need to combine their user pools, or the event that new data hosts C through Z need to be added, there's a reasonably big challenge there if you hadn't already planned for it. On the other hand, if you're using semi-random UUID's or something, with machine, datetime, and random components, you're probably OK to migrate, merge, or add hosts pretty worry free.

The chief advantage of the auto-incremented PK's is lookup efficiency. An advantage that's waning a little every year, mind you...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.