First normal form and first and last names

Question

I am trying to understand 1NF and I am wondering if this next table is 1NF or not. I suppose not, because in the codes first_name, last_name and full_name can be repeated and, therefore, they need to be moved to a new table where there are columns user_id and first_name, last_name and full_name. Below is a screenshot of the database.

http://imgur.com/kerlB

Answer 1

It certainly can be.

What you are saying with your current design, if this is the first normal one, is that one “object” (allowing you to simply call it a person) is associated with one and only one user record.

If you move the name fields to a separate table, what you basically say is that one “person” can be associated with one or more user records and that when updating his name he must do the same change at all their "users"

If you need a structure like this, the table will look more like:

user_id|username|password|email|person_id 

and you will have a separate table for each "person"

 person_id|first_name|last_name|full_name 

The 1st norm does not apply to data duplication. Just looking at the first name, you may well have a lot of people with the name "Bob" or "Alice" Just because this data is repeated over and over, this is not the same as saying that the table has duplicate data. The fact is that each record must be atomic.

Answer 2

1NF is atomicity, not redundancy (as for higher normal forms). Essentially, if all attributes are atomic, your table is in 1NF.

Obviously, whether the table depends on 1NF on what you define as atomic. What “atomicity” actually means is a matter of some disagreement, but I would suggest a pragmatic approach to each case and just ask:

In the context of the problem I'm trying to solve, does it make sense to have access to ^{1 of} any part of the value, or do I always get access to the whole value?

If I always access the whole, it is atomic in this particular context.

In your example, you probably want to access first_name and last_name separately, so full_name will be non- full_name , and this will cause a 1NF violation. If, however, you know that you will never need access to the first and last name separately, then you can only have full_name and not violate 1NF.

---

¹ “Access to” meaning should be understood quite broadly here. Perhaps this would mean reading it from the database, but you could also use it in a restriction or index it, etc.