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?

Relly

I don't think you say the using ID's or numbers is a bad idea on itself. It depends. Often, ID's and numbers are just perfect. Ask yourself : what other key were you thinking of otherwise ? And, would that be better ? I don't think either is essentially better. A trick with numbers, is to not start with 1, but let one ID start with 1000000, the other with 20000000, etc. – Bemis

@tvCa: Of Course I do not mean Id or numbers itself. I mean auto incremental Id as part of database specific information. – Relly

@Relly please clarify your question. When you say "as business entity identifier" this is not a clear statement. Most of us would think of business as customer facing. But your comment suggests that you mean is it ideal for the Primary Key for the table. And the answer to THAT question is it is definitely a good fit for a PK when you are defining it as an auto incrementing column and the PK... so long as you are not generating a GUID... I would also add that showing a customer an account number that is an auto incremented value is just fine... so long as it's not a GUID – Gono

@Gono When I am saying business identifier i mean something that is customer facing. – Relly

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.

Malevolent

I believe the OP is asking if it's a good idea to use it as his PK, based on the subsequent comment they made. Additionally, in having worked with users of applications in a support developer role for a good number of years, I have found that users actually tend to latch onto an ID type number rather quickly. As in "Hey will you look @ Record number 14567 and tell me if the description is confusing?". My caveat is if a GUID is used as the PK, if that's the case then yeah use of a natural key is a great idea (but I would never show the actual PK to the user in that case). – Gono

@mer: I absolutely don't advice to replace the pk in favor of a nk. That is even impossible. – Malevolent

I apologize for my lack of clarity. I wasn't suggesting that you where telling him to use a natural key as the PK, (though it IS possible agiledata.org/essays/keys.html (point 2), it's just a bad idea). My only point should have been that, in my experience, users don't like the idea of referring to the number, but then quickly switch to using the generated number as the primary point of reference for a given record in an application (record meaning all of the collected data that is shown in an application such as an account #, or a file # or project # etc...). – Gono

@mer: my personal experience is that users are delighted having the ability to speak their language in the system. – Malevolent

good information & I agree, having some sort of natural key (or better yet a name field) is important for users to feel comfortable looking @ the data. I'm more referring to what they actually end up using after they've become comfortable with it, (though if the system puts them off badly enough @ the beginning then they will never use it enough to be comfortable...). – Gono

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.

Intricate

points out what I didn't have room to comment on previously, auto incremented Primary Keys can be a problem in application designs in which the data must be unique across multiple databases... in this case resort to a GUID as PK. However I will add that my anecdotal experience with actual users using actual applications has been that they believe a name will be easier when initially asked but within a few weeks of using the application they are referring to the surrogate key value far more often than the name, (even fully numeric values). – Gono