Is it a bad idea to use a database's primary key as business object identifier?

Question

I wonder, is it bad or good idea to use auto increment primary key as business entity identifier such as Partner Id or Account Number?

Also, what pitfalls I can face if I'll choose that approach?

Answer 1

I don't think everyone shares the same opinion, but I do think it is bad practice. Passing ID's to the user as the 'key' is bad in my opinion, for a number of reasons:

• ID's aren't natural to users. They are not talking about project '1474623', they are talking about project 'ABC'. They aren't talking about person '363528', they are talking about 'Patrick Hofman';
• ID's are fragile. You can't really rely on them not changing. What if you choose to move to another database platform, or a new version of the current platform, and you want to move all data using 'insert' statements, it is possible to loose the ID fields.

In our products, we always use a 'natural key', next to the primary key, a key that is understood by humans.

If there is no human understandable natural key available, for example when it is a logging table, you can revert to a artificial key.

Answer 2

There are at least three desirable characteristics you should keep in mind when choosing or designing keys: Simplicity, Stability and Familiarity. In practice people often find it simpler to remember and work with words and letters rather than just numbers and that is why alphanumeric identifiers are generally more common than numeric-only identifiers (examples of alphanumeric identifiers: car licence plates, airline flight numbers, seat reservation numbers, state and country codes, postal codes, email addresses). There are studies and annecdotal evidence to support the idea that alphanumeric keys are more usable than numbers alone. Also, alphanumeric identifiers can often be shorter than numeric ones. On the other hand, sequential numeric-only identifiers are very common for some applications (e.g. invoice numbers, bank account numbers). So I suggest that you should be guided by your users' / business needs when determining these things.

Note that DBMS engine-level sequence generators often come with limitations that make them unsuitable for some applications. For example it may not be easy to update them or to use them in a distributed database architecture. Another common limitation is that only one "auto incrementing" column may be permitted per table, which precludes their use as a business key if you also want a surrogate key for the same table.