Multiple person roles in database(how to define if same person) 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?
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);