2

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.

Random Joe
  • 640
  • 4
  • 10
  • 25
  • 5
    There are legal implications as well as privacy concerns. – Oded Dec 22 '11 at 08:28
  • 1
    Did you know that there are cases of duplicate SSNs in the US? All else aside, it's not *quite* the magic unique number... –  Dec 22 '11 at 08:42
  • @Oded This will be an electronic government application in a south east Asian country where such usage of national IDs are quite pervasive. – Random Joe Dec 22 '11 at 11:34
  • @pst I didn't think of that before. It's definitely a point to consider – Random Joe Dec 22 '11 at 11:36

2 Answers2

9

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.

NPE
  • 486,780
  • 108
  • 951
  • 1,012
  • How would a surrogate allow you to identify people? It wouldn't, and is no substitute at all for a natural key like a national id number. – nvogel Dec 22 '11 at 09:25
  • @aix The application will be strictly for citizen only. Although unlikely, the change of format is something we will bring to discussions. I'm leaning towards the usage of surrogate key at the moment but I like the idea of straight forward queries using national ID. – Random Joe Dec 22 '11 at 11:47
  • @dportas I have to admit I never use natural key because I rarely have the chance to use them. This might be the case for it. But I needed to be sure first. – Random Joe Dec 22 '11 at 11:52
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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • 1
    It is part of the requirements since this is an electronic government application and there have been many similar large scale online applications in that country. I like the secure hash idea thanks. – Random Joe Dec 22 '11 at 11:58