Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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, specially 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.

share|improve this question
13  
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 at 15:04
3  
in Dutch a "van de Iets" type name is quite common. just let the last name field contain spaces –  ratchet freak Mar 26 at 15:15
2  
Is there a reason you couldn't just have a single name field? –  asfallows Mar 26 at 16:37
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? –  pablox Mar 26 at 18:52
2  
@FedericoPoloni: Anyone looking up a patient by the last name probably knows the patient's last name. –  Mooing Duck Mar 26 at 22:39

6 Answers 6

up vote 16 down vote accepted

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".

Falsehoods programmers believe about names is a good read on the subject too.

share|improve this answer
2  
+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 at 15:58
    
+1 for the link at the end, I was searching it right now! –  Darkhogg Mar 26 at 17:18
    
@Darkhogg utnapistim has it as the first 'This' link in his answer. Its one of those 'definitive guides'. –  MichaelT Mar 26 at 17:40
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. –  MichaelT Mar 26 at 20:50
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. –  MichaelT Mar 26 at 23:24

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

share|improve this answer
    
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 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 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. –  pablox Mar 26 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 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 at 23:10

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.

share|improve this answer
1  
What if the family name is given first? link –  MichaelT Mar 26 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: " –  Jorge Mar 26 at 21:01

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.

share|improve this answer

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?)

share|improve this answer

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.

share|improve this answer
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? –  MichaelT Mar 26 at 18:45
    
@MichaelT, What to say that everyone has a family name? –  Ian Mar 26 at 20:32
2  
Getting a name from a person from Japan (or many other eastern naming countries 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. –  MichaelT Mar 26 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 at 20:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.