12

In Spanish-speaking countries we use more than one last name, like:

First name ↘                           ↙ Last name
                Pedro Arturo Rodríguez Loyola
        Middle name ↗                               ↖ (?)

I'm trying to model data for patient name. In our country it's important, so I can't neglect the second last name, but I would like to build an application that can makes sense to other developers, especially from English-speaking countries.

I created a similar question on English.SE, it has some insights about why it's important to persist both values.

Community
  • 1
Pablo Olmos de Aguilera C.
  • 231
  • 2
  • 6
  • 15
    Is there the slightest reason why a "last name" value should not be allowed to contain spaces? Do you really want to use one last name in some use cases and all last names in others? If not, then I fail to see the problem. Leave the "last name" exactly as the user entered it and you're good. – Kilian Foth Mar 26 '14 at 15:04
  • No it doesn't matter. It's important though (I added a link giving some insights). Here everyone uses both last names, and we rarely use our middle name (or "second name" [literal translation]). We also don't use terms like "Jr.", therefore just by looking at the names you don't have a chance to differentiate between two "Pedro Pérez". It's also useful, when looking through family members (our primary health care is focused on familiar medicine [I dunno if that concept exists in english]). – Pablo Olmos de Aguilera C. Mar 26 '14 at 15:09
  • 3
    in Dutch a "van de Iets" type name is quite common. just let the last name field contain spaces – ratchet freak Mar 26 '14 at 15:15
  • My 'first' last name is "Olmos de Aguilera", which is a "compound last name" (_apellido compuesto_). The problem here is that we use it both. Thanks for the suggestion though. – Pablo Olmos de Aguilera C. Mar 26 '14 at 15:54
  • 2
    Is there a reason you couldn't just have a single `name` field? – asfallows Mar 26 '14 at 16:37
  • Why do you need to know "last name" or "first name"? That way lies madness (see utnapistim's link below). – Alex Feinman Mar 26 '14 at 17:56
  • @AlexFeinman when working with other systems such as sending filling out the proper insurance electronic paperwork may need to differenate the names. If you are working from a single name field or not identifying the type of name part you will likely get it wrong. You might get it wrong anyways, but there is a big difference between getting most of them wrong vs a few of them wrong. –  Mar 26 '14 at 18:52
  • 2
    @asfallows: because if we print a patient list we need to sort them by the last name. For example, if your name is "José Carlos Fernando Almodóvar Soto", how am I supposed to know in advance that "Almodóvar" it's the first last name? – Pablo Olmos de Aguilera C. Mar 26 '14 at 18:52
  • @pablox - that is a very good reason. – asfallows Mar 26 '14 at 19:25
  • @pablox I realize that this is the common way of sorting names, but paradoxically it makes thing worse: then everyone looking up a name on the list would have to know that "Almodóvar" is a last name as well... – Federico Poloni Mar 26 '14 at 20:18
  • 2
    @FedericoPoloni: Anyone looking up a patient by the last name probably knows the patient's last name. – Mooing Duck Mar 26 '14 at 22:39

6 Answers6

22

Q: How does a DBA count?

A: 0, 1, many

An individual has 1 or more given names and 1 or more family names, and possibly a title. These names have an order to them. It is up to the localization and culture to determine how to refer to an individual.

ContactId
NamePart {"John", "Smith", ... }
NameType {title, given, family, ???}
Order {1, 2, 3, ... }

For Pedro Arturo Rodríguez Loyola (contact #1), you would have four rows:

1 / Pedro     / given  / 1
1 / Arturo    / given  / 2
1 / Rodríguez / family / 3
1 / Loyola    / family / 4

This way it is not limited to any given structure yet still makes sense for a given contact on there. What do you do when you have someone with 3 or 4 given or family names? or a maiden name?

Note that I've changed the order from a from previous revision of this answer - the order is an order over the entire name rather than just an order within the name type because in some cultures, the family name comes first, you may have split title parts "Sir John Smith II".

Additional Reading

Community
  • 1
  • 3
    +1 But this programmer has worked with many offshore team members, but the longest name I've ever seen is still "Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Crispiniano de la Santísima Trinidad Ruiz y Picasso". Child abuse or not? – Elliott Frisch Mar 26 '14 at 15:58
  • @Darkhogg utnapistim has it as the first 'This' link in his answer. Its one of those 'definitive guides'. –  Mar 26 '14 at 17:40
  • I like the idea as a concept, but it isn't a bit hard to present it to the user that it's gonna write the actual name? The only thing I can come up it's something like "GMail" contact fields, that let's you "add" parts of the name. Have you thought on how you can actually use that kind of data model? – Pablo Olmos de Aguilera C. Mar 26 '14 at 19:00
  • @pablox yep, the 'add parts to the name' would be the UI approach that would correspond to this (you might find your way to UX.SE when working with that idea). I could see a number of ways to do this, but thats what it really boils down to. This model is really the only way I can see handling someone with 6 family names (see that bit on Portuguese names) or where the family name is the first name and the given name is the last name. –  Mar 26 '14 at 19:09
  • I'd rather say that trying to structure the name (either according to your recommendation or any other) is futile - you'd be better off trying to *not* structure them. Simply accept that names include spaces and don't ever split them in parts yourself. Any name structure that you need should be left *up to the user*, as only they can do it properly. – Peteris Mar 26 '14 at 20:47
  • 1
    @Peteris often in medical applications (the original question mentions 'patient') you need to pass around a structured name for integration with other systems. Having unstructured data means that you'll have significant difficulty integrating with other systems, even things like transferring a patient from one hospital to another or populating form fields for insurance claims. And thus the problem on how to structure the data in a way that allows you to manipulate it in all of the required ways. –  Mar 26 '14 at 20:50
  • Still, it is simply impossible (not in the "it'll be hard" sense but in "it'll be wrong anyway) sense to structure a name that wasn't structured in the first place. There is no way to properly go from a string with 4 'words' to a firstname/lastname split - you have to ask the user or check his official identity documents. And if they say that they have a 'Last name' that is 'Rodríguez Loyola', then you don't impose any additional structure whatsoever, because it'll likely be wrong - in some cultures part of that string will be maiden name, in some a patronymic, etc. – Peteris Mar 26 '14 at 20:55
  • @Peteris I in no way suggest having a four word split, its just that was the demonstrating string. This structure would properly handle a Portuguese name where there are 6 distinct name parts (each may be multiple words): 2 from husband's side, 2 from father's side, and 2 from mother's side. The key is to make it flexible enough to be used by the appropriate systems. If you store just a single string you will *always* have trouble passing the name via EDI for an ambulance service. –  Mar 26 '14 at 21:01
  • Though I understand why you've done it this way and sometimes being able to pick out the "last" name is extremely important this is horrible... unless knowing the "last" name definitively is important it's better to just have a free text field. – Ben Mar 26 '14 at 22:51
  • 1
    @Ben a free text field does not work well when the application calls for sorting by family name or when trying to interface with an old EDI system for transfer of a patient from an ambulance to the hospital (or vice versa), or filling out the appropriate fields in various insurance claim forms. These are all key areas where it is easier to have a bit of data that designates each part of the name than it is to try to assign the meaning after the fact. A free text field is nice, but sometimes its just not an option for working with other systems. –  Mar 26 '14 at 23:24
11

This may help. The post is humorous, but insightful.

[First name] [Last name] is not a universal rule for names. It is just common where you live. If you impose rules in this, sooner or later you will have people who cannot be added to your system.

Basically, make sure you impose as few restrictions as possible, and allow flexibility between the actual stored name, and any extra ones.

I would go with something like this:

  • Display name (for consistent names when showing forms/data): (should be [first] [last]).
  • Other names/full name (for searching, more precise matching, etc). Here, allow user to write anything, up to given length; length should be more than you think should reasonably be enough - e.g. if you think 40 characters should be enough, put 500 :) ).
  • Addressing (Mr, Mrs, Ms, Jr, Sr, -san, custom value (like Tov.) etc).
  • internal ID (this ID should uniquely identify every person within your application, preventing name collisions).

For example, my name isn't correctly writable in most European languages, due to Romanian-specific diacritics (so, whenever I write my name outside my home country, technically I write it wrongly - so people can read and pronounce it).

Some interesting name examples and links:

http://en.wikipedia.org/wiki/Nicholas_Barbon

http://en.wikipedia.org/wiki/Prince_%28musician%29

http://en.wikipedia.org/wiki/P_diddy

http://en.wikipedia.org/wiki/Burmese_name

utnapistim
  • 5,285
  • 16
  • 25
  • One more thing to consider is sorting. It is often desirable to be able to sort a list of people by their last name (or only name, or first word in last name, etc.). If the application needs to do that then we need some way of identifying their "sorting name". – BenM Mar 26 '14 at 18:02
  • But I do like this idea overall. Instead of representing names based on cultural assumptions, we represent them based on the functions they need to perform in the system. – BenM Mar 26 '14 at 18:12
  • I like your answer. I don't understand though how this can help me to put names. I need to sort them for example, we normally use last name. How putting them on a field/column called "other names" or "display name" could help me with that for example?. Kudos for the burmese name, though. Awesome. – Pablo Olmos de Aguilera C. Mar 26 '14 at 19:06
  • @pablox, my point was that conceptually, you should not treat a person's name as an identity, but as metadata for the identity (and the id should be a unique code). You can consider that metadata as organized, according to your application's needs. For example, you could choose the role of "sorting name" to the second name (if any) in "the group of name tokens", the role of "display name" as the first token, and the role of "addressing name" as "Dr.|Mr.|Ms.|etc" + "display name". – utnapistim Mar 26 '14 at 19:15
  • Sorting of names is typically going to be "telephone book sorting", where you ignore various appendages to the name and sort on the important part. E.g., "van de Bogart" would be sorted on "Bogart", not "van". How a multipart Hispanic name is handled is going to be culturally dependent -- where is it being used and how its use fits in. – Phil Perry Mar 26 '14 at 23:10
1

There are three types of personal names: Polynyms (names with multiple components), Mononyms (names with only one component, ex "Cher") and Pictonyms (names represented by pictures, ex The Artist).

A person can have multiple names, playing roles, for example Legal Name, and Preferred Name.

A polynym has several components, of which given name and surname should be required. There are a few kinds of surnames:

  1. Regular surnames (Jones)
  2. Double-barrelled surnames ("Vaughn Williams" or "Louis-Dreyfus")
  3. True Compound surnames { givenName:"Juan Pablo" surname:"Fernández de Calderón", secondarySurname:"García-Iglesias" }

3 is important because he would be expected to be addressed as Mr. Fernández de Calderón, not Mr. Fernández de Calderón García-Iglesias.

So basically, have a mandatory surname field, and a nullable secondarySurname field.

user118920
  • 11
  • 1
1

How do you model the various components of someone's name? You Don't. Instead of modeling separate fields for all the components of someone's legal name, plus their preferred salutation, appelation, informal name, username, nick-name, and handle, only model what you care about.

Odds are your system only really cares about at most four "names" for someone:

  1. A Display Name which allows them to know that they are logged in to the system.
  2. An Informal Name by which you would address them on a phone call.
  3. A Formal Name with which you would refer to them on correspondence.
  4. A Sort Name which expresses #2 in a way that it would be ordered in a list.

Many systems would simply use someone's formal name for #1 and #2, leaving the only names you care about as "a string representing the user's name" and "a string representing how the former should be sorted."

From a data modeling perspective, define only those values that you would actually need. How best to optimize the storage of these values is an implementation detail -- one that you will only cause headaches for if you over-optimize. (Sure, you "could" just store a first name and last name, but what would you do for someone who only has a single name?)

DougM
  • 6,361
  • 1
  • 17
  • 34
0

It's not a complicated thing..... or I am missing something.

Have these fields: name (varchar), lastname (varchar).

Then a form to fill the complete name, example: Jorge Patricio and the last name Pèrez Gonzáles.

In the search, you have many comparison operators, for example in MySQL, like like which will help you looking.

Mostly the composed last names always are in order. Pérez González mostly says its last name that way, and not in reversed order.

You will be over-designing your database other way.

JorgeeFG
  • 659
  • 4
  • 13
  • 1
    What if the family name is given first? [link](http://en.wikipedia.org/wiki/Personal_name#Lexical_order) –  Mar 26 '14 at 20:38
  • 1
    @MichaelT, what about it? Explode the field and search in name and lastname table for any occurrence of the value. Always supposing you have a Google-like search field, otherway you could just have a form like "Name: " and "Last Name: " – JorgeeFG Mar 26 '14 at 21:01
-1

Let’s consider this in another way.

Your system has to connect to other systems, it does not matter how flexible you are if they are not. So what does the systems you have to transfer data with do?

When I phone up my doctor for an appointment they ask me for my date of birth, the systems then shows them a list of people that match, then they ask me for my name, lastly the ask for my address to confirm that they have selected the correct person.

The doctor needs to know the name address me by when talking to me; they also need to know the name to put on an letter if they write to me.

Therefore way not just a single text field that allows spaces. There is no reason to even separate the first from the last name, unless you will process them in same way.

Ian
  • 4,594
  • 18
  • 28
  • 3
    You're filling out a form on an insurance claim. It has "Last Name". The person is from Japan. While you have the transliterated version, you need to put the proper family name in the field. Which word is it? –  Mar 26 '14 at 18:45
  • 1
    @MichaelT, What to say that everyone has a family name? – Ian Mar 26 '14 at 20:32
  • 3
    Getting a name from a person from Japan (or many other [eastern naming countries](http://en.wikipedia.org/wiki/Personal_name#Lexical_order) the family name is given first. That is how it is addressed. However, when filling out a medical form one may require that the family name and given name are in different fields - how do you take the one field and match to the proper field? The other example given is if you are sorting on family name and given "José Carlos Fernando Almodóvar Soto" you need to sort on `Almodóvar` rather than `Fernando`. –  Mar 26 '14 at 20:37
  • @MichaelT, so there is a need to process the Family Name separately, so give it it's own field and hope the person that types in the details understand eastern naming. – Ian Mar 26 '14 at 20:42