How to design for names

Names come in many and various forms. Naming conventions across the globe are many and plentiful. As a database designer (and/or application programmer and/or technical analyst or, indeed, anyone with the power to affect future applications and communications protocols), bear in mind that your diligence is the difference between a happy and an unhappy customer.

Minimum requirements

Minimum requirements on a name database is that it should be able to separate a "used name" and "used name and surname" (these may, actually, be the same). This is so you can familiarise form letters and the like (or web pages). It's absolutely essential you get this right.

Ideally, you should also be able to capture "full name, including any middle names and all surnames". If you do this, you will need some method of capturing "used name" that is different from "the first of the names".

A little story

Let me tell you a story about a fictional banking and insurance customer, named Pappnamn Johan Eriksson, usually called "Johan" by his friends (oh, that adds another kettle of problems, you might want to have a method of catching preferred gender pronoun and possibly marital status).

Johan went and applied for a bank account, with (say) HSBC plc. As his application is processed, the whole name question comes up. Every single piece of full ID that Johan has states that his name is "Pappnamn Johan Eriksson" and Johan queries what will be on his cards, because, you know, you want your used name spelled out in full and all that. And the bank replies that to make that happen, they will have to register him as "Johan P. Eriksson".

A couple of years pass. Johan decides it is time to proceed with two things, first buy a house and then get a pension insurance.

Johan finds a suitable house, gets a written mortgage offer and things proceed apace, until a diligent solicitor looks at Johan's ID and at Johan's mortgage offer. Horror! Catastrophe! One is issued to "Johan P. Eriksson" and the other to "Pappnamn Johan Eriksson"! All for the sake of getting names right.

This is eventually sorted and Johan gets himself some pension insurance. Eventually, the insurance company sends him a letter, addressed to "Pappnamn Eriksson". Now, there is no one by that name living in Johan's house. In fact, "Pappnamn Eriksson" is Johan's father, dead the last 15 years (OK, fair enough, the deceased father was named "Olof Pappnamn Eriksson", but was usually called Pappnamn by family and friends) and Johan is, obviously, a bit annoyed about getting paperwork that is important to himself addressed to his very departed father.

So, can we do better?

Actually, yes, we can. If we allocate enough space to keep the name either split in "first names" and "surnames" or keep it all in a single chunk and keep an index of what name(s) are the "used name(s)".

Using this slight expansion, namely keeping an index of the used name(s) (alas, there might well be more than one, depending on how you classify a name split, like "Bobbi Jo" or "Sven-Göran"), you can rest assured that you run a MUCH better chance of not annoying any eventual customers.

This is one of Ingvar's essays