[AccessD] Names, Uniqueness and Normalization

It has been interesting reading about the impossible unique keying of 
names.  And the mistakes other systems make in trying to use something like 
social security number for a unique key.  Unfortunately, our names are not 
unique, nor will they really ever be.  I have 2 sons, twins, that have a 
fair chance at it.  We made up their first names, Calen and Braeg.  Tried 
to give them a Gaelic sounding name.  So, in the end, names are not 
unique.  The best design for a Party table would be something like:

Party_ID          Autonumber
Salutation        Text(20)
FirstName         Text(20)
MiddleName        Text(20)
MiddleName2       Text(20)
LastName          Text(20)
NameSuffix        Text(20)
GoesBy            Text(20)
GreetingUsed      Test(40)

You might be able to enforce uniqueness across all but the party_id, but, 
depending on the number of entries into the database, you might still have 
problems.  And, adding in a birth date would also help but still could be a 
problem in a database of millions of names.  Note:  MiddleName2 is there 
because some people, like my oldest, do have 2 middle names.

I would never add phone number or address into the mix because they can 
change too easily.  You might want to store them and keep a history of them 
so you could identify a person through that history, like the large credit 
verification companies.

One of the companies I work with is a non-profit social service agency that 
has a lot of people doing community service.  They are going to a time 
clock that do a fingerprint biometric.  Now, that is a pretty unique way of 
identifying a person and the name they carry.  ;-)

It was also interesting to read another post in the same digest about 
normalizing names.

Going a bit further with that, it really depends on the size of the 
database.  In the class that I teach, I used the example of a company that 
created phone books.  How many tables would it take to completely normalize 
that data?  Well, I gave them a month to think about it and then we went 
over it.  We came up with between 27 and 31 tables.  If someone is 
interested in some of our thoughts about it, I will post them for you to 
the list.  Otherwise, I will not waste the space.

Robert