Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have read numerous books and articles about database design and SQL in which it is said that a database should be in first normal form (1NF). (Some then go on to describe situations in which it may be smart to denormalize a bit, but that's a different subject.)

For example, a typical example of a table not in 1NF would be:

+---------+-------+--------------+
| last    | first | phone        |
+=========+=======+==============+
| Smith   | John  | 303-123-4567 |
----------------------------------
| Doe     | Mary  | 303-456-9933 |
|         |       | 303-456-9944 |
----------------------------------
| Johnson | Bill  | 303-987-6543 |
+---------+-------+--------------+

The primary key is (last, first).

Having studied this issue a bit, it's my belief that it is impossible to create a MySQL table that is not in 1NF. If that's true, then I wonder why such a big deal is made about it. Why tell users not to do something that's impossible? It reminds me of a sign on an elevator door: "Elevator out of order. Do not use."

(In the comments, I'd appreciate it if anyone can give me instructions about how to create a MySQL table that is not in 1NF.)

My example non-1NF row can't be inserted by the MySQL INSERT statement, because that statement allows only one value per row per column. If the type of the phone column is, say, varchar(255), one could certainly insert the value '303-456-9933,303-456-9944', but this would not violate 1NF, because clearly that string is a single value. (If you don't believe me, try getting at the two phone numbers without searching for the comma or breaking the string in half at position 13. SQL and every programming language I know of would treat the value as a single string.)

Another attempt at building a MySQL table that's not in 1NF would be the following, and in this case the table certainly can be created in MySQL and the data shown can easily be inserted:

+---------+-------+--------------+--------------+
| last    | first | phone1       | phone2       |
+=========+=======+==============+==============+
| Smith   | John  | 303-123-4567 | NULL         |
-------------------------------------------------
| Doe     | Mary  | 303-456-9933 | 303-456-9944 |
-------------------------------------------------
| Johnson | Bill  | 303-987-6543 | NULL         |
+---------+-------+--------------+--------------+

However, this table is also in 1NF, since phone1 and phone2 are two different columns (attributes).

I used MySQL as my example because I don't know what's going on with the very latest versions of Oracle, DB2, SQL Server, etc. I do know that recent SQL standards allow array and row types for columns, but, as Chris Date has written extensively, whatever value one might have for a array- or row-type column, it is a single value, and therefore doesn't violate 1NF. Anyway, I'm not concerned with array and row types, because none of the database design books (the ones I've seen, anyway) that warn against non-1NF tables mention those two types, as the books are a few years old. It's all those books that have been written over the past 40 years that concern me. (Because I'm writing yet another book that touches on this subject.)

My proposal is that the typical formulation of 1NF ("no repeating groups", "single value in each column of a row", or whatever) be replaced by a formulation that addresses the actual problem. Something like this:

"A table should never have anything that looks like a repeating value, nor should data that looks like a repeating value be entered, unless one has first determined that the design is the desired one. (The issue is not whether a value is repeating, but whether it looks like it is.)"

Maybe not the best wording, but at least it captures my thinking on 1NF. That is, the database or the application program should prohibit data like '303-456-9933,303-456-9944' from being entered into the phone column unless the designer has decided this is what he or she wants. Likewise, one should never have two columns named phone1 and phone2 unless one has thought about it first.

After all, it's pretty common in database design to have a few phone columns (office phone, home phone, mobile phone, etc.), as opposed to defining a separate phone table and using a one-to-many relationship. Both approaches are reasonable and widely used, and anyone could come up with a list of pros and cons for each. That particular discussion is not related to the point I'm making. My point is this: The way 1NF is almost always defined, both are in 1NF, and therefore the design principle that suggests one approach versus another can't be based on 1NF.

Ironically, with so many places where SQL violates the relational model, it seems that we have a case where it forces a relational principle!

Incidentally, I don't have the same concerns about 2NF and 3NF. Those rules are definitely easily, and even frequently, violated by MySQL databases.

Comments appreciated!


UPDATE: Here's a quote from Chris Date's "Relational Database Dictionary" (p. 69): "It follows that a “table,” in a language like SQL, can be considered to be in 1NF if and only if it’s a direct and faithful representation of some relvar, where direct and faithful means among other things that every row-and-column intersection (i.e., every cell, q.v.) in that table contains exactly one value of the applicable type, nothing more and nothing less. (The value in question can be arbitrarily complex—it can even be a table—but, to repeat, there must be exactly one such, and it must be of the applicable type.)"

1NF says nothing whatsoever about two columns, even if they are named phone1 and phone2. It is solely concerned with the value in a single cell. People try to apply it to two columns with similar names, and there's no justification in the 1NF rule for doing so.

In my proposed reformulation, I am trying to include the multi-column case, something that the rule as currently formulated doesn't do.


Update #2: For those who think that phone1, phone2, etc. is a 1NF violation, forget phone numbers and just look at a table with three columns: first name, middle name, and last name. Most databases have such a table (with more columns). Surely you wouldn't say that having the three names made the table not 1NF? If you don't, surely you'll let me call the columns name1, name2, and name3? After all, "first", "middle", and "last" have no meaning in some cultures.

share|improve this question

closed as not constructive by Jon Seigel, Paul White, Phil, Marian, Jack Douglas May 6 '13 at 10:24

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

7  
You say that the string '303-456-9933,303-456-9944' is a single value. As string, yes, it's a single value. As phone numbers, it's 2 values. As characters, it's 25 values. Since your column is named "phone", I'd say it violates 1NF. The matter is not how SQL treats strings but what you are modelling. –  ypercube May 5 '13 at 19:55
5  
I am treating '303-456-9933,303-456-9944' as 2 numbers (rather than what it is)? Why, isn't it two numbers? I see 2 numbers there. –  ypercube May 5 '13 at 21:24
2  
A "Challenge" is not a question. "Comments Welcome!" is not a question. In fact, this whole post more of a polemic than a question, it is argumentative and ultimately, Not Constructive. It is just a very contrived excuse to argue about conflicting interpretations and applications of Relational Theory. –  RBarryYoung May 6 '13 at 0:07
1  
Can you describe the specific formulation of 1NF that you're disagreeing with? As you've identified, the original definitions have already been debated, and I think anyone who's studied database theory already applies 1NF in a more general sense (as did RBarryYoung, below). Having a definition that includes the words "unless ... the design is the desired one" actually raises more questions than it solves. –  Nathan Jolly May 6 '13 at 0:43
2  
I'll just echo what others have said, this type of question isn't a good fit for this kind of Q&A site. It's a good question, but this isn't a good home for it. –  Jack Douglas May 6 '13 at 10:24
show 3 more comments

2 Answers 2

I think they key phrase is "set".

Codd's 1972 paper said "A relation is in first normal form if it has the property that none of its domains has elements which are themselves sets." (quoted from the Wikipedia article on 1NF).

In your example, the set is "phone numbers on which we can call this person". Whether you store the phone numbers in a single column holding an array ("555 123 4567,555 987 6543") or in two columns (called phone1 and phone2), this is still a set - a repeating group - and forbidden by 1NF.

On the other hand, if all the entities all had exactly two phone numbers and there was a functional difference between phone1 and phone2 then having two columns phone1 and phone2 would be in 1NF.

share|improve this answer
    
I don't disagree, but you are making your argument on how the table looks to you (as the phone numbers being members of a set) rather than on how the table is defined. That is what I am proposing: That a more useful rule would be based on how the table looks, and not on the single-value issue. Date specifically discusses Codd's use of the word "set", which unfortunately would allow an array or a bag, surely not Codd's intention. Codd himself revised this early statement of the rule later. My problem with 1NF as it is stated is that no relation can ever violate it, which Date says himself. –  Marc Rochkind May 5 '13 at 23:35
    
I don't agree with Date that a set allows an array. A set is an unordered collection that can have 0 or more members, each of which is equivalent. An array is an ordered collection of a fixed size where each items position has meaning. Or, to put it another way, telephone1 and telephone2 are a set because it doesn't matter which field contains which of a person's two phone numbers. Or, to use your edit above, (firstname, middlename, lastname) is an array because it is not correct to put the entity's firstname into the second or third position. –  Greenstone Walker May 6 '13 at 1:59
    
My Googling for another question just found me this, which I think is relevant. Database Debunkings: Normal Forms: Dependent on Dependencies. –  Greenstone Walker May 6 '13 at 2:14
    
Greenstone: What Date said is that Codd ruled out sets, but didn't rule out arrays or bags. He was suggesting that Codd had said it wrong. Codd corrected this later. –  Marc Rochkind May 6 '13 at 17:47
    
Thanks Mark, I think I had it backwards. –  Greenstone Walker May 7 '13 at 6:48
add comment

I'm afraid that it's dreadfully easy to do in any SQL/Relational environment:

+---------+-------+--------------+--------------+--------------+---------
| last    | first | phone1       | phone2       | phone3       | phone...
+=========+=======+==============+==============+==============+=========
| Smith   | John  | 303-123-5670 | 303-123-4567 | 800-231-5674 | 212-8...
-------------------------------------------------------------------------
share|improve this answer
    
I specifically gave that example in my OP. I don't see any multi-valued items there. I think what you're doing is working off of the pattern that phone1, phone2, etc., seem to suggest, namely the word "phone" followed by an integer. That is, you are going by what the table looks like, rather than what it is, which is EXACTLY what my proposed 1NF reformulation says. –  Marc Rochkind May 5 '13 at 20:47
3  
I do see multi-valued items there. :-) Or, better phrased, I see repeated columns (not the same as multi-valued). I see nullable columns identically named except for suffixes "1", "2", etc. The nullablity plus the suffixes tell me they are repeated columns. The classic discussion point is "OK, so you have phone1, phone2 and phone3, so what happens if one customer has 4 phones?". If your design is vulnerable to such a point then it is not in 1NF. –  Greenstone Walker May 6 '13 at 1:52
    
Greenstone: Of course, I completely agree that the phone1, phone2, etc., approach MAY have problems. But what bothers me is basing that recommendation on 1NF, which is much too narrowly stated in every almost database book I've read. After all, there are numerous ways in which a database design can and should be improved that have nothing to do with normalization. Part of the issue is that 2NF/3NF are the real problems, much more serious than being non-1NF, yet to be in 2NF/3NF requires that the DB be in 1NF, which may be inappropriate (e.g., if there are only 2 phone numbers). –  Marc Rochkind May 6 '13 at 17:55
    
You could substitute Addr1Line1, Addr1Line2, Addr1Line3, Addr2Line1, Addr2Line2, Addr2Line3, Addr3Line1 ... and have a repeating group. A repeating group with one member is still a repeating group. –  ConcernedOfTunbridgeWells Jun 23 '13 at 11:28
add comment

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