Tip: Tables containing people

One of the most common things to store in a database are details of people, be they users, contacts, or third parties you record details for. People are also far and away the most common source of mistakes in the design of relational database schemas and applications. Here are some pitfalls with handling names.

1. First name, last name - Japanese people, amongst many others, disagree with the West on this ordering.
2. Christian name - I'm not a Christian.
3. Title as an enumeration - do you limit the choices of prefix titles people use? Have you included Dr., Sir, Prof., Prof. Sir, Rt. Hon., Cllr., Mayor, Cpt., Cmdr., M., Mme., Mlle., Sheik, Pope, HRH, President etc? Do you think you can compile an exhaustive list?
4. Are two names required? Sting, Lulu, Bono, Pocahontas, Countess of Wessex.
5. Guessing initialisms - If you've asked for my full name, don't assume you can address me as Daniel J. Pope or D. Pope. I very rarely use these forms. Joanne Kathleen Rowling is usually announced as J. K. Rowling. Similarly, I'd always initial myself DJP and never DP.
6. Many US Americans (sadly lacking maps) require their generation after their surname for disambiguation. But you can't turn John Davis III into Davis III, John. You would write Davis, John or possible Davis, John III.
7. Don't force Title Case - Mohammed al Fayed, Manfred von Karma, John Davis III and so on shouldn't be put into title case.
8. "Abdhul" is not an informal way to address an Arab whose name begins with Abdhul ie. the first word of a given name is not necessarily an informal name.
9. Have you stored aliases? Should your database be able to record the fact that Thomas C. Mapother IV is also Tom "Only scientologists can save the world" Cruise?
10. I happen to have a peculiarity with my names. I am not called "Dan Pope". Dan is slightly diminuitive but "Pope" is slightly formal, and the combination shouldn't be used. People have often assumed they may refer to me this way and they are wrong. To confuse issues, in recent years I sometimes sign e-mails this way because I've found it's a good fit for the formality of the medium. But different criteria apply in the second or third person.

Names are a very individual thing and people feel strongly about how they should be addressed. How you index or sort names is your business and doesn't influence how people wish to be addressed. If you're indexing by surname, be aware that not everybody will identify themselves to you by the same surname you have indexed. If you're sorting, you should perhaps be aware that individuals' native collations may differ.

The tip, then, is this: store the forms of address you wish to use. Do not assume you can correctly derive other forms.

These are the variants I use:

Full Title: Daniel James Pope MA (Cantab.)

Full Name: Daniel James Pope

Name: Daniel Pope

Formal Name: Mr. Pope

Postal Name: Mr. Daniel Pope

Initials: DJP

Vocative: Daniel

Familiar Vocative: Dan

Nickname: Mauve

Preferred Username: mauve