Is it good to use a primary database key as an identifier for a business object?

Question

I wonder if it’s good or bad to use the auto increment primary key as an identifier for a business object, such as Partner Id or Account Number ?

Also, what problems can I encounter if I choose this approach?

Answer 1

I don’t think everyone shares the same opinion, but I think this is bad practice. In my opinion, passing an identifier to a user as a “key” is bad for a number of reasons:

• IDs are not natural for users. They are not talking about the "1474623" project, they are talking about the "ABC" project. They are not talking about the man "363528", they are talking about the "Patrick Hoffman";
• Identifiers are fragile. You cannot rely on them without changing. What to do if you decide to switch to another database platform or a new version of the current platform and want to move all the data using the "insert" statements, you can lose the ID fields.

In our products, we always use the "natural key" next to the primary key that people understand.

If there is no human-readable natural key, for example, when it is a coffee table, you can return to the artificial key.

Answer 2

There are at least three desirable characteristics to consider when choosing or designing keys: simplicity, stability, and familiarity. In practice, it is often easier for people to memorize and work with words and letters, rather than just numbers, and therefore alphanumeric identifiers are usually more common than identifiers only for numbers (examples of alphanumeric identifiers: car license plates, airline flight numbers, booking a seat numbers, statuses and country codes, postal codes, email addresses). There are studies and annotated data that support the idea that alphanumeric keys are more convenient than numbers alone. In addition, alphanumeric identifiers can often be shorter than numeric ones. On the other hand, sequential numeric identifiers (for example, account numbers, bank account numbers) are very common for some applications. Therefore, I suggest that you be guided by the needs of your users / business in identifying these things.

Note that DBMS-level sequence generators often have limitations that make them unsuitable for some applications. For example, it may not be easy to update them or use them in a distributed database architecture. Another common limitation is that only one “auto incrementing” column can be allowed for each table, which excludes their use as a business key if you also want to use a surrogate key for the same table.