Relation between first name and last name

Question

I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model)

I've read that the relation is of type: 1-1: They are linked together.

But the same first name can be attributed to multiple persons. And a single person may have multiple first names...

What I understood is:

Even if "John Doe" has many homonyms around the world, in a database, his first name would be directly linked to his last name.

To avoid this question, we can regroup the last name and first name in a single row but that would end with humans problems (Formatting errors: Which order, in capital letters ? separed by spaces or comas... ?)

In the case of a mail generation (for example), I should use the following format: Title + Last Name (Mr. Doe) instead of (Mr. DOE John).

Then I should atomize my two values.

This reflexion should seems useless as my research should be based on ID or others criterias but as I'm still learning, I try to understand the best way possible each details of a databases's conception.

Answer 1

I think the example is a very theoretical one. I have never seen any real world examples of separate FirstNames and LastNames tables. Of course you can normalize your database this way, but it wouldn't make much sense in most of the cases.

Back to the relation. I think there are two relations here:

1. First name - Person 1:1
2. Last name - Person 1:1

I don't think there is a relation between the first and last name.

We can take a slightly different approach with one surname(last name) and multiple "first" names. Then there can be a many to one relationship between first names and person.

However, in most cases first name and last name are just attributes of one person. To solve the issue of mail generation, you can have 3 columns: first name, middle name and last name. Then you use only first and last names for mail generation and store the middle name for other purposes.

Answer 2

In most databases, I have seen persons defined like this:

Person: 
FirstName 
MiddleName       -- rarely defined, usually included in FirstName <br>
LastName

FirstName and LastName are usually modeled as attributes of the entity Person (or relation Person to use database theory language) and they always appear together in the same row. The relevant relationships are those mentioned by BuahahaXD (each attribute is in 1:1 relationship to Person)

As already mentioned, merging them may lead to trouble (I have seen this in some legacy systems).

Theoretically, you can have 1:n relationship between Person and FirstName / LastName, if tables are modeled like this:

Person: PersonId
PersonFirstName: PersonFirstNameId, PersonId, Name
PersonLastName: PersonLastNameId, PersonId, Name

So, this means that first and last names are entities (relations) and not attributes. I think this is rarely used like this, as it is harder to maintain and the performance degrades when managing the data. However, it makes sense to have similar structures for applications that deal with names (e.g. BehindTheName)