Dutch names and your database columns
So sayeth Wikipedia:
A tussenvoegsel, in Dutch linguistics, is a word that is positioned between someone’s given name and surname, but is still a part of someone’s last name.
In the Netherlands, the tussenvoegsels strictly speaking are not a part of someone’s last name. For example, someone whose family name is “De Vries” is not found at the letter “D” in the telephone directory but at “V”. Tussenvoegsels are therefore also required to be listed separately in databases; another reason for this is that it makes finding someone’s name relatively easy, as most Dutch prepositions start with the same letter. In the Netherlands, the tussenvoegsel is written with a capital letter if no name precedes it. So Jan de Vries (Jan being a given name), but: de heer De Vries (meaning Mr De Vries) and de heer en mevrouw Jansen-De Vries, (Mr and Mrs Jansen-De Vries).
In Flanders tussenvoegsels of personal names always keep their original orthography: mevrouw van der Velde, mevrouw J. van der Velde, and Jan Vanden Broucke.
So, if you have a database with “first name” and “last name” fields, where do you put the van or the van den or the uijt te de or whatever?
If you really need to store Dutch names with tussenvoegsels in only two database columns, two approaches come to mind, both of which I will explain below. I will reuse the example name ‘Jan de Vries’ also used in the original post.
Approach Ⅰ
First name: Jan
Last name: Vries, de
Explanation: The given name is used as the first name, and the family name and tussenvoegsels (separated by a comma) are used as the last name. This approach is used quite often in the Netherlands, e.g. in tables with people’s names.
Implications: If you sort by last name, the sort order will be correct. However, if you simply concatenate the values to form the full name, this approach results in ‘Jan Vries, de’, which makes no sense at all. This concatenated form must be avoided at all costs if you keep your data like this. Alternatively, you can use ‘Vries, de (Jan)’ if you really need to form a single string out of two parts. Though ugly, at least it is unambiguous.
There is a solution, though. For many relatively simple applications, this approach can be used without problems, but only if you use it in combination with some proper string manipulation, i.e. just split on the comma in the last name, then put the three parts into the appropriate order.
Approach Ⅱ
Alternatively, this format can be used:
First name: Jan de
Last name: Vries
Explanation: In this case the given name and the tussenvoegsels are combined into the first name, and the family name is used as the last name.
Implications: When concatenating this results in ‘Jan de Vries’, which is correct. Concatenating the fields the other way around (with a comma in between!) results in ‘Vries, Jan de’, which is a perfectly acceptable way to spell out a name, especially when used in sorted single-column lists. However, if you use this approach, you can no longer use only the first name without appending the last name to it.
Concluding remark
The concept of a name is way too complex to be reduced to only two fields, but I assume that is not the point raised in this post. Sorry for the long answer, but hopefully I’ve made things a bit more clear.
(If you need more information, feel free to contact me. I will surely forget to come back to this page to read comments.)
Thanks for this very informative comment, Wouter. Actually, in my own application I don’t split the name column at all; I really don’t see much need to sort on last name (search more or less obviates that).
Come to think about it, I once wrote a blog post on the topic in this very blog:
Hacklog » Blog Archive » Name games
I just happened to meet some Dutch folks today, and was entering a name into my contact database, when I realized that I didn’t know a) whether to capitalize van and b) which column to put the van in. It’s not really a big deal in this case, actually, since the app allows for substring searching (in the form of autocompletion). But it exports to vCard, which may end up in an app which doesn’t support substring search.
In one of my projects, names can contain any of these fields: given name (e.g. ‘Jan’), family name (e.g. ‘Vries’), tussenvoegsels (e.g. ‘de’—I use the term surname prefix for this), initials (e.g. ‘J.’), title prefixes (e.g. [Dutch] academic titles, or things like ‘Lord’), and title suffixes (e.g. ‘Ph.D.’). Of course this is overkill for normal applications, but in my case I really needed this level of granularity (it had to to with record linking algorithms). For the normal case, I would recommend just having a display name for people and be done with it, pretty much what you suggested as well.
[...] Alphabetical ordering and Dutch last names. [...]
Oh dear, personal names. What a can of worms, software globalization-wise. I agree it’s better to leave them unanalyzed and just stick on a big “type your name here” box.