1

Practically, I would never store names for customers in a separate table. But why not? It's a one to many relationship (a person can have one name but that name can be used by multiple people). Isn't it proper design to separate one to many relationships over multiple tables, or am I missing something?

8

No, it is absolutely not proper normalization to move a name out to another table.

Normalization reduces redundancy based on functional dependencies, not based on coincidental repetition of data values.

Let's say there are 100 Joe Smiths in your database. If you move the name "Joe Smith" out to another table and have 100 records point at this name, what happens when one of the Joe Smiths decides to change his name to "Bob Smith"? Unless they all change their names in unison, you can't work with the data that way.

  • 1
    You'd have to create a new record for "Bob Smith" and then have the customer who changed point to that new record. It's an insert and an update, a little more complicated than just an update. – FrustratedWithFormsDesigner May 26 '14 at 14:03
2

Background

Apart from what Joel Brown's answer says, names are not one-to-many. They are a many-to-many relationship with a temporal dimension as well.

  • A person may have multiple names simultaneously, all equal in status (that's indeed my case, as a dual national with different legal names in each country due to different cultural conventions)
  • A person may have different representations of the same name, e.g, { אהוד ברק, Ehud Barak, Эхуд Барак }
  • A person may be known by different names in different contexts: { Karl Ernst Thomas de Maizière, Thomas de Maizière, Tommy }
  • A person may use different names in different cultural contexts: { Alžběta Pospíšilová, Alzbeta Pospisilova, Alzbeta Pospisil, Elizabeth Pospisil }
  • A person may change their names over time: { Maria Smith, Maria Roderick-Mountbatten }
  • Any combination of the above

Response

You were asking in the context of normalisation, and you say you "would never" store normalised names. Maybe not in a name-of-customer setting, but if you were designing a database intended to track name usage and distribution, then very likely you would be normalising names (or using a non-relational approach).

But outside of that context, a personal name is a very fuzzy attribute and one which really does not lend itself well to ontological treatment. It is better treated, in its entirety¹ as an opaque multi-valued and temporally sensitive attribute.

¹ I pity the fool who has to design a database that deals with First Name, Middle Name, Patronymic (Russians and others), Last Name, Second Last Name (Spaniards and others), Nisbat / Toponymic, Religious Name, Nickname, etc., etc., etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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