0

I'm working on a project that requires assigning ID numbers to records about people in a database. Each person may have many records associated with their ID number.

There was a discussion about whether it was desirable to have information about each person encoded in their ID number. For instance, the ID number could be [year person was first added (4 digits)][state number where person was first added (2 digits)][sequential counter (4 digits)], like 2016010002. Alternatively, it could contain no semantic content at all.

On one hand it could be nice for sanity checking that we can make sure the ID number matches the data in each field, and it's easier to remember an ID if you want to. On the other hand, if something in the record needed to change, we might have to figure out whether to change the person's ID, which could cause problems.

What are the upsides and downsides to including information about the record in the ID number? When is each approach preferred?

  • 1
    On Stackoverflow, your question would be closed as a dupe of this one: stackoverflow.com/questions/337503/…. My personal recommendation: avoid business information in primary keys like hell. If you need a human readable person ID with semantic content, for example, to use it in mailing, make it a secondary key. – Doc Brown Jun 15 '16 at 6:52
1

My two cents:

Note: don't confuse concatenating multiple values into a single string and use it as a PK with having multi-column PK.

That said:

Concatenating multiple non-key data into a string to populate a key column has several disadvantages:

  • People could guess IDs for hacking purposes
  • When the non-key data changes the key is left outdated and has to be changed in order to reflect the changes made to the data
  • All the columns to be concatenated into the key string has to be known an the moment of insertion.

Nonetheless, I've seen identifiers that are the result of concatenating several other values in bank account numbers in my country where the string is composed of the bank code, bank branch code, account type, then a sequence, then an error cheking number. I don't know why that practice is still in use. I've also seen that kind of practice in ledger account identificator which use a hyerarquical ID like 10.1.2.3.0.1.

My recommendation is:

  • Don't do it.
  • If a good natural key exist use it (it has to be stable, i.e. very rarely change -- as opposed to never --, it has to be known at the moment of insert). As we are talking about a persons table, there will be many circunstances where a good natural key won't exist.
  • If the natural key would be composed of more than three columns then use a sequential surrogate key instead, even if you have to create a unique key on the composed candidate natural key to enforce business rules.
  • If no good natural key exist then use a surrogate.

It depends of the business rules. If it's a payroll system an employee number (which is a surrogate key) is a good option.

A good to point to ponder is that an artificial, sequential key generated by a certain recognized authority can be considered a natural key by other organizations. Keys are only artificial when you generate them inside your organization either sequentially or randomly and have no meaning.

0

There was a discussion about whether it was desirable to have information about each person encoded in their ID number.

Encoding multiple items of data into a single field is Bad Design if for no other reason than it breaks basic Data Normalisation rules.

Alternatively, it could contain no semantic content at all.

That would be my choice.

On one hand it could be nice for sanity checking that we can make sure the ID number matches the data in each field ...

If you need to ensure that the "right" ID number has been entered, then consider adding a check digit. OK, it's not strictly [just] a number any more, but that shouldn't make any real difference. Using a check digit guards against simple errors like digit transposition and miskeying.

This is one reason that I shy away from pure, sequential numbers (sequences, autonumber fields, etc.) as Data Identifiers. As internal, "surrogate" keys, managed by the application and never seeing the light of day, maybe, but never in any sense that somebody might have to type one in.

If you need to check that you're dealing with the right Person, then an ID on its own is just not enough.

On the other hand, if something in the record needed to change, we might have to figure out whether to change the person's ID, which could cause problems.

It would most definitely cause problems; changing Primary Keys is not something you should take on lightly, especially when there's [even] a [small] change that you might get [all] those IDs wrong.

0

Including information in the Id of the record:

The upsides:

  1. Minor (but debatable) initial convenience to the few humans who need to read the underlying data store.

The downsides:

  1. Nearly impossible to make changes. What happens when the Id suddenly needs to include information X? What happens when information X needs to be removed, or becomes irrelevant?

  2. Inconvenience to the few humans who have to use the underlying data store. There is nothing simpler than an integer id to identify a record in a database. And it is easy to use.

When is it preferred? Rarely. In certain small lookup tables, it is preferred. In every other case, it is not preferred.

Principally, the primary key has one purpose, to uniquely identify a record. It should not have more than one purpose. It uniquely identifies a record so that other records in the database can refer to it efficiently. Why does table X need to know the social security number of a record in table Person? Answer: It doesn't. It just needs to know the identity of the specific Person record it refers to. So that identity should be clearly suited to that task.

Example, I once worked with a database that had only natural keys. So everything but a handful of root tables had multiple primary keys. So in order to run a simple query on something like sales orders, I had to link the company branch, the order number, and the order line just to get the order information. Now imagine what I had to do to link invoice lines to orders, or anything more complicated? It should have been a single link on an id field every time. It was easy to read the first time through, sure. But to use the database was a great inconvenience.

  • Concatenating non-key data into one column to use it as a PK is a bad idea, but it has nothing to do with natural vs artificial keys. – Tulains Córdova Jul 15 '16 at 13:02
0

Think about security too. If you identify these records EVERYWHERE by data that may be sensitive - will there eventually be sensitive data somewhere it shouldn't? 'Shouldn't' being defined by requirements, client needs, and the Data Protection Act.

It might be 'encrypted', but I assume if you want it for efficiency of cross-checking - it won't be heavily encrypted, it'll just be a little obfuscated when you build it by concatenation or whatever. That ain't good enough to call 'secure'.

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.