Using National ID as Primary Key

Question

I'm developing a system that make use of national ID one of "identifying record" is it a good idea to embed nat_id in all the tables to make query easier? What are the downsides of doing so? I just want to get opinions from people here in SO because I might miss something important.

Answer 1

As suggested by @Oded, there may be legal and privacy implications (that very much depends on your country).

One example of a privacy issue is that in some countries the person's date of birth -- and in some countries their gender -- is part of their national ID number. Replicating that into every table in your schema might not be a great idea, since it will make it hard to restrict access to this information.

On top of that, there are several purely technical concerns:

1. Not every country has a national ID system, and those that do, use different formats. Do you really want to have to alter every table in your schema when you need to accommodate a new format?
2. I can imagine situations where a person might not have a national ID (a visitor? an asylum seeker? a stateless person?) How are you going to enter them into the database?
3. In some countries the closest thing to a national ID is the number on the ID card, and that number changes when the ID card is re-issued. In other words, the same person could have different ID numbers at different points in the their life.

I would use a surrogate primary key, and would store the national ID as an attribute.

Answer 2

Whether national ID would make a good key or not depends largely on your requirements. Is it a requirement to record national ID as part of the business process and that users/employees/whoever are uniquely identified in that way? Are you legally entitled to ask people for that information? Are they obliged to disclose their national ID to you? If yes to all these then it probably makes good sense for you to make it a key in the database.

Make sure you understand any standards for the national IDs that you'll need to support. E.g. length, data type, any check digits and whether there are legacy codes or other special purpose codes in use that may not match the expected rules.

If there are privacy concerns and you don't need to retrieve the actual ID from the database then you could store a secure hash of the national ID instead.