Search code examples
database-designrelational-databaseprimary-keynatural-key

What are some good candidate keys for ‘person’?


In an application that will be used world-wide, What are some good candidates for natural keys to uniquely identify users/persons ?

How do we deal with edge cases? - people without country or official documents (refugees) - people who have changed their name or gender/sex

The key should be robust and unique.


Solution

  • As others have commented, getting something 100% unique is going to be difficult - a balance may need to be struck between how far to go with the edge cases and what's practical.

    Being pragmatic, you could assume the following combination of fields is likely to uniquely identify a person the vast majority of the time:

    • Date of birth
    • Full name (forename, middle names and surname) on birth certificate

    But with common names this falls down - if your name is James Smith and you were born during a baby boom, it's not all that unlikely there's someone else out there with your name and date of birth.

    To narrow it down further you could add the time and place of birth - but these may be harder to come by. And even then, there's no 100% cast iron guarantee - it's not completely impossible that at some point in history, two babies were born in the same hospital on the same day with the same recorded time of birth and the same name...