5

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.

4
  • 2
    If only internationalization was so simple. Is this a homework assignment? w3.org/International/questions/qa-personal-names - It really is a massive can of worms.
    – Philᵀᴹ
    Dec 14, 2015 at 12:15
  • It's a personnal research. I made a document and let a colleague read it. He let me few annotations I was able to answer, but this one is the darkest in my mind. Dec 14, 2015 at 12:26
  • 1
    See my answer to this similar question: dba.stackexchange.com/questions/65808/…. There is a difference between coincidental occurrence and relationships.
    – Joel Brown
    Dec 14, 2015 at 17:06
  • Would you store a price of a product in a separate table just because two products can both have the price of $1?
    – jkavalik
    Dec 15, 2015 at 9:49

2 Answers 2

8

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.

1
  • 2
    Many people may share the first name or the last name. So it seems 1:N to me in both cases.
    – Andriy M
    Dec 14, 2015 at 17:47
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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