How do I handle Duplicate members in a table?

[KingOf206]

I am putting together a "Clients" table and I am wanting some advice on what is the best way to handle Clients with the same name.



For example:

Here are a few fields from my "Clients table":

ClientID
Fname
Mname
Lname
DateOfBirth


I was thinking of making a unique index for first and last, but then i thought, what if I come across 2 John Smith's?

I am sure there is a professional way of handling this, but Im not sure what the norm is.

-SHould I make a unique index for Fname, Mname, & Lname?
-SHould I make a unique index for Fname, Lname, & DateOfBirth?

I would like to know what is the norm when storing people in a table and the best way to prevent duplicate clients from being entered.

[JeroenMioch]

You can't, based on names alone.
This is why company's have personell number, and country's social security numbers.
They are unique and thus no two records are alike.

I see you have clientID, that should be a unique number to identify your clients.

You can however look for this number by asking first, last names and date of birth.

[JamesDeckert]

I agree with Jeroen. It's a bad idea to make a PK based on names (unless the db is very small and static like keeping track of friends/family or something, and even then I would hesitate). Having an autonumber PK would be the simplest and more robust than one based on names.

[KingOf206]

Yeah but the "ClientID" is an auto number.. so what if John Smith signs up for an event jan 1st then 8 months later john smith signs up for another event, how can I prevent entering him as 2 different clients when in fact he is already in their?

I am putting together a "Clients" table and I am wanting some advice on what is the best way to handle Clients with the same name.

For example:

Here are a few fields from my "Clients table":

ClientID
Fname
Mname
Lname
DateOfBirth


I was thinking of making a unique index for first and last, but then i thought, what if I come across 2 John Smith's?

I am sure there is a professional way of handling this, but Im not sure what the norm is.

-SHould I make a unique index for Fname, Mname, & Lname?
-SHould I make a unique index for Fname, Lname, & DateOfBirth?

I would like to know what is the norm when storing people in a table and the best way to prevent duplicate clients from being entered.

[JeroenMioch]

I never said it should be an autoID number.
When a client calls you check if there is a John Smith in your database using a filter (unbound textboxes in the header of your form). Once you confirm that he is in fact the John Smith your looking for you know his ClientId because that number is in his record.

This is exactly how big companys work. Tiu call them and they will ask for your client ID. Most people dint kniw their ID so they will ask you things like name, date of birth and zipcode and adress.

The ID number is used to tie records together.
You will have to assign each client a unique number.

When you then want to know wich products your client has ordered you base it on the clientID.
When running reports or querys you know for cenrtain that all records belong to the same client and nit some other John Smith.
You simply refer to the number rather then a complicated combination of personalia

[orange]

I would keep the ClientID PK autonumber.
If you may get duplicates with LName & FName and MName, I would add DOB a create a unique composite index.
That will prevent duplication on the combination FName, Mname, LName and DOB.
The database software will use ClientID as PK and for relationships with other tables. The unique composite index will prevent the duplicates you mentioned.

Good luck.