What would be the ideal unique natural key associated with a person?

Asked

Viewed 83 times

2

I’m developing a system whose one of the tables represents people. Of course, the most accurate document to represent only one person is CPF. However, the system provides for the possibility that foreign persons resident in Brazil may register.

Foreign people are not required to issue a CPF. In this way, I think of some possibilities:

  • Passport
  • RNE

Although these documents manage to represent a single person, we have the following problems:

  • The Passport, although common among non-foreigners and foreigners, is not mandatory, and I believe that most users of the system would not have.
  • RNE is mandatory, but only for foreigners.

Considering the above situation, what is the best solution to represent only one person in my database?

  • Use both CPF and RNE fields, requiring the column to have one or the other.
  • Use another unique key (quote and explain why).
sql database postgresql modeling

  • I think you’re confusing the concepts of the key a little bit with a Unique validation. The key can be a sequential numeric field that will guarantee each record a unique value. The key is a requirement of the bank and does not necessarily need to be a business attribute, so it does not need to be any field that the business needs, such as names, dates or documents. I suggest creating a simple numerical field that increments itself, and placing constraints in the other fields so that it does not happen again and the problem will be solved

  • There is no confusion. I am not referring to the bank’s unnatural primary key. This will be done incrementally. But a unique auxiliary key (Unique index) that allows me to represent a person in a natural and unique way. I can do a backend validation without difficulties, but I’m questioning the correct way to do it in the database data model.

  • if you already have an incremental primary key, the only validation at the bank level that can be done is to define a field such as Unic. Now if the field can contain null values this will not be possible, then you will need to validate at code level

  • 1

    an alternative would be to create a single field for the document, both passport and rne, and another field stating what type of document, so the document would never be null, and could create a Unique index with document+document type, I think it would be a good idea

  • That’s a good question! I don’t usually use this information as a key, but I use a normal numeric UUID or ID, along with a unique email field.

  • A sequential artificial key in these cases can be a solution , depending on the case a table with the documentation , Cpf , rg , Pis , passport , cns , brevé , cnh ... as you did not say the nature of the system I believe it is best to be genoic , the documentation may have a uniqueness treatment within its nature to ensure uniqueness.

  • @Ricardopunctual, I believe that your second option is the closest thing I need. The only problem would be the case of a foreigner who owns an RNE and decides to do a CPF and register with both data. But this case is extremely rare and easy to validate in the backend without worry.

Show 2 more comments

1 answer

1

You are starting from the premise that the only solution to your problem is to use a field containing a numerical code extracted from an official document as a single key but you forget who not all person has the same documents.

If we’re talking about anyone, Brazilian or foreign, using document numbers is not a good idea because:

  • The same person may own more than one CPF;
  • Not everyone is of Brazilian origin;
  • Not every Brazilian has a passport;
  • Not every foreigner has a social security number;

If we’re talking about person, we have to consider natural attributes of a person, such as:

  • Name;
  • Mother’s name;
  • Date of Birth;
  • State of birth;
  • City of Birth;

And even:

  • Country of Birth;
  • Planet of birth;
  • Galaxy of Birth;

Yes, twin brothers with identical names may be an exception. But, what kind of mother would give the same name to two different children ?!

Browser other questions tagged sql database postgresql modeling

You are not signed in. Login or sign up in order to post.