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.
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".
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:
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
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:
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.
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:
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?)
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.
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.
