Multiple person roles in database(how to define if same person)

Question

I have the following data structure:

For internal purposes I can register my outside partner. In this case I don't know his/her identify card number. So I add my partner to database but without id card number,and has no user access.

*I cannot add more pictures, sorry.

Once my partner comes and he/she wants to use the system. He decides to register as a member. He may would not know that he/she has already registered as an outside partner. Filling all Person datafields and the user fields, including the identify card number.

And here comes trouble. I don't want to split the same person into 2 in the case when the partner is already in the system. I want to use the same person details insted of duplication. It is extremely important that we do not allow person duplication.

What would you suggest?

Answer 1

The Name of a Person and his/her Bith Date are almost enough to make an unique index with both columns. This way you would garantee 100% of unique individuals in your table, but you could have problems with those miracle situations where two people born the same day with same name.

In this case I would recommend you add a new columns called Birth place (City/State/Country) to your Person table and make the Unique Index with those 3 columns.

It will solve your problem.

create unique index single_person_id on Person(name, birth_date, birth_place);