3rd Normal Form and NULLs

Posted on 2005-02-26
Medium Priority
Last Modified: 2008-01-09
Somebody was telling me that if there are NULLs, then data can't be normalized to 3rd Normal Form.
Question by:lcor
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 25

Assisted Solution

jrb1 earned 400 total points
ID: 13412854
I'm not sure what they meant.  It has been argued that you can't even get to 1st normal form with NULLs.


If you agree with this, then you are correct.  Data with NULLs can't be normalized to 3rd normal form, because to get there you have to meet the conditions for 1st and 2nd normal form first.  However, I tend to disagree.  For example, you have a table called person.  It includes:

personid (primary key)

Now, since you have people in your table that are still living, what do we do with death date?  Do we allow nulls or not?  I say, YES.  If a person has not died, they do not have a value for date of death.  And yet, this table cannot be normalized any further.  So NULLS can be in a 3rd normal table.
LVL 19

Assisted Solution

billmercer earned 400 total points
ID: 13413014
It depends on the definition of all the terms. How do you define NULL? How do you define Data? Whose definition of third normal form are you using?

If you're talking about taking a SQL table that contains NULL values and converting it to 3NF, you can argue that this is impossible, because SQL itself violates some of the basic principles of Codd's RDMBS definition. Strictly speaking it's impossible for a SQL database to be 3NF at all, regardless of the NULLs.
Here's an article with a quick overview of the model and some of the debates...

If you're using NULL simply to mean that there are some cases for which you don't know the value of a field, then the answer is no. You can create a fully normalized database in 3NF that does not use NULL-as-in-SQL-NULL within the tables, but still allows representation of NULL-as-in-missing-information values. Here's a PDF describing how this is done.

Jrb1, the response to your example from Mr Pascal and the Third Manifesto guys would probably be that there is no need for a NULL here, and the correct thing to do would be to store the date of death in a separate table with just the date and a primary key. The reasoning being that the lack of existence of a record in the related table is sufficient to indicate that there's no relevant information available. Their notion of the ultimate database is the so-called "Sixth Normal Form", and has only one field per table, plus a key. Personally, I think it should be carried even further, ultimately concluding in Eighth Normal Form (8NF), wherein each binary digit of data is stored in its own table. ;)

Of course to put this whole debate into perspective, while the ivory tower academics rage on over esoteric relational theory, their secretaries are probably keeping their appointments in flat files.
Some people have to get real work done with the tools available.

Assisted Solution

SkipFire earned 400 total points
ID: 13414624
It's a rule of thumb, and just like many other rules it is subject to interpretation.  If a null can indicate something than it is fine.  Frequently I use NULLs in connection with dates and currencies as a way of indicating no value entered, which is often a valid state.  Just do what works for your situation.  How many people actually do addresses as 3nf?  I almost always have city + state + zip just because it is more convienent and it works for most situations.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Accepted Solution

msroberts earned 400 total points
ID: 13421320
According to all definitions I have read and used, all attributes of a relation must contain vaules.  nulls are not allowed.  for the person example

personid (primary key)

is not in 1nf  because it would occasionally need a null in deathdate (not all persons are 6 feet under).  To get into 1NF we would need to make another table for deaths

TABLE: person
personid (primary key)

personid(primary key)

this is in 1NF because all people have a name and all people have a birthdate.  all dead have a deathdate.  no nulls anywhere.  
this is also a bonus in that we only record deathdates for the dead.  and any personid not in dead is still alive.  

nb: this is also in 2NF and 3NF due to it's simplicity.

-- violence is the last refuge of the incompetent -- Issack Asimov
LVL 19

Expert Comment

ID: 13422789
Pragmatically speaking, rather than declaring NULLs to be anathema, it makes more sense to simply avoid them as much as possible. If it looks like you're going to have a lot of NULLs, you probably need a separate table for the field. But if unknown values are a rare exception, it may be both easier and neater to use NULLs. Why did Codd make systematic handling of NULLs a mandatory requirement for an RDBMS, if he didn't intend that they should ever be used for anything?

In practice, it probably makes more sense to think of normalization as a continuum rather than a binary state. This database is MORE normalized, that one is LESS. Purists probably disagree, but I tend to think in terms of what's  going to be used in the real world. If you're able to convince a user to split their flat file up, consider that a victory, and don't bemoan the fact that it's not in 3NF.


Assisted Solution

Jankovsky earned 400 total points
ID: 13437311
There is nothing with nulls within 3rd normal form.
Only violence could be at 5th normal form (may be).
Normalization could just reduce amount of null values in data (particularity influents normalization process), but, as has been said, it's not binary.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question