newbie Q: last, first, middle name

[dlazenby]

I'm setting up a table and trying to decide on some field choice with person's names. (Anticipate 1000s of records, big db)
Let's say you anticipate that the a person's name might be Harold S. Truman-Jones III
Would it be stupid to just have a last name field (Truman-Jones) and a first name field (Harry S, III)?
Or should you have unique fields for middle name, and suffix?
What if a person has 2 or 3 "middle" names?
Are there issues with having periods, commas or hyphens in the field?
What about suffixes? do you right it as III (three capital I instead of Roman numeral 3) or do you write 3rd or third?
Thanks

[DaveM]

Re: newbie Q: last, first, middle name

Generally I do:


pref 7
first 20
nick 20
middle 20
last 50 reason is for a business name which may be long.
sur 10

This allows Mr. John "Jim" Jimmy Doe sr

Concatenation comes later.

Use the sizes that make you comfortable.

.

[Davidk]

Re: newbie Q: last, first, middle name

I've always found that, depending on who is doing the inputting, parts of names always get messed up. You have to decide how many name parts you want and just not allow any more. e.g. 1 or 2 middle names but no more. My naming conventions usually follow:

SName (Salutation... Mr., Dr., Sir) - 10
FName - 50
NName (Nickname) - 20
MName1 - 50
MName2 - 50
LName - 50
CName (Company Name) - 50

I make 'em big... will always run into a name that doesn't fit. It's just a bit of space.

[DaveM]

Re: newbie Q: last, first, middle name

Either DavidK or my way depending what you want.

I use the last name long for company because I learned it from others.

No matter how long you make them, you will do the same thing on concatenating. something like:

trim(sur)+" "+trim(fname)+"  and so on "

One programmer I know made all his fields 255 long. All numerics were in 255 long character fields. He had a lot of work to do to use that mess, but it worked for him.



.

[CALocklin]

Re: newbie Q: last, first, middle name

I'm setting up a table and trying to decide on some field choice with person's names. (Anticipate 1000s of records, big db)
Let's say you anticipate that the a person's name might be Harold S. Truman-Jones III
Would it be stupid to just have a last name field (Truman-Jones) and a first name field (Harry S, III)?
Or should you have unique fields for middle name, and suffix?
What if a person has 2 or 3 "middle" names?
Are there issues with having periods, commas or hyphens in the field?
What about suffixes? do you right it as III (three capital I instead of Roman numeral 3) or do you write 3rd or third?
Thanks

Personally, I'd include a separate field for each part of the name.

I would also consider "Truman-Jones" to be the last name - not two separate parts. Just in case you were wondering.

However, the suffix (there's got to be a 'real' name for that but can't think of it right now) should be separate. If, as you suggested, you had only a "last name field (Truman-Jones) and a first name field (Harry S, III)", then it would look pretty silly if you ever wanted to show the name as <First Name> <Last Name> such as in a letter.

Harry S, III Truman-Jones just doesn't get it.

If you put the "III" with the last name in order to get:
Harry S Truman-Jones III
then Truman-Jones III, Harry S looks wrong.

My general rule is to always break things in databases into the smallest logical pieces. The "why" is important -- because it's very easy to put things together in a database. And you can do it in any order you want. Conversely, it is often extremely difficult to separate them. Just ask the many people who have tried to separate "full name" fields that had all the following into Title, First_name, Last_name, Suffix. (And, yes, the punctuation errors are on purpose because that will happen in real life.)
Fred McGillicutty
Blow, Joe
Harry S. Truman-Jones, III
Mr. Fred J Truman-Jones Esq
Truman-Jones. Cara
Truman Jones, Anna
Porter
Dr. Smith
etc.

There are probably a very few exceptions to my rule. However, I've even been sorry occasionally when I didn't separate the address number from the street.

Warning - If you ever run into this, think about it very carefully:
I've also been asked to separate the "N,S,E,W" from the street name. And, was even asked to separate the "Drive, Court, Blvd, etc" from the street name - in the end I turned down that job simply because (a) it was going to be way to difficult to accomplish what the customer wanted even though it did make a certain amount of sense and (b) I thought the users would have revolted and it would all have to be undone. (I later found out that the users really don't like it but some have stuck with them anyway.) However, I can imagine conditions where it might make sense.

Make your future life easier - separate the names into their different components now!