[viper2843]

I am designing a database (and ER diagram) for a project I am working on and I can't seem to figure out how to properly normalize one particular component. Here's the logic in my head:

• There are three different types of data, or tables, I am working with: users, departments, and devices.
• Users can belong to departments.
• Users have devices.
• Departments have devices.
• The same device cannot belong to a user and a department at the same time.
• A device must belong to either a user or a department.

The last two bullets are frustrating me the most. Can anyone help me normalize this? Thanks.

[r937]

it's real simple

three tables, with the devices table having 2 foreign keys -- one to the departments table, and one to the users table

vwalah, normalized

now, implementing your constraints is a different problem and has nothing to do with normalization

both foreign keys must be NULLable, but you require that exactly one of them must always be NULL

see if you can come up with a simple CHECK constraint

[pootle flump]

Rudy - I was going to recommend the same thing.

I've sometimes pondered, in my less productive and more whimsical moments, whether or not this sort of violates 3NF. The value of one non-key attribute does not determine the other, but it does determine its absence. Similarly, the absence of one does not determine the value of the other but it does determine its presence.

I know that 3NF is not strictly violated, but it kind of goes against the spirit of 3NF to me. Any thunkles or references?

[r937]

just a thunkle

first, a disclaimer: i am not a normalization zealot, i know normalization only as a practitioner

both the spirit and the letter of the laws of normalization deal with the relationship of non-key attributes to the primary key

the only normal form in which non-key attributes are discussed relative to each other is 2NF (i remain blissfully unaware of forms beyond 3NF) and in this case, 2NF is concerened only with functional dependence

if you tell me that the value of the user FK is NULL, this means that the value of the department FK is not null, but it doesn't actually determine which value it is, does it

[pootle flump]

Correct - it does not. And as such there is no functional dependency. But it tip-toes close to the line.

EDIT - BTW - what's the Canadian rule on z-for-s substitutions? I thought standard practice was to keep in line with the queen...

[r937]

if americans can have their own version of english, so can we

just be thankful that we still honour most britishisms

[dportas]

A table with a null is not in any Normal Form. Normalization doesn't have much to do with the actual problem however, which as r937 says is really about how to implement a set of constraints. See the following link for one possible solution.

http://blogs.conchango.com/davidport...-Subtypes.aspx

To implement the constraint that a device MUST belong to either one of two tables is harder. Possible solutions are: deferrable constraints; some form of multi-table update (be it a trigger or other mechanism); or a sequence of inserts and updates where a default value is created for the referencing value and then updated to reference a new row inserted in the referenced table. Your particular DBMS may or may not support some of those things.

[pootle flump]

Originally Posted by dportas

A table with a null is not in any Normal Form.

Not wanting us to get into dbdebumk territory - I presume you mean since the redefinition of a relation by Date? That a relation by definition cannot contain nulls?

[r937]

Originally Posted by dportas

See the following link for one possible solution.

http://blogs.conchango.com/davidport...-Subtypes.aspx

nice solution

i've seen that before someplace too

by the way, that wwu.edu powerpoint presentation mentioned in the last comment on your article is really good, but the sound just drives you batty when it jumps out at you

[viper2843]

Originally Posted by r937

three tables, with the devices table having 2 foreign keys -- one to the departments table, and one to the users table

vwalah, normalized

now, implementing your constraints is a different problem and has nothing to do with normalization

both foreign keys must be NULLable, but you require that exactly one of them must always be NULL

see if you can come up with a simple CHECK constraint

First off, thank you all for your replies. r937, this is the way I currently have the database designed. I have written a CHECK constraint to make sure that only one of those fields has a NULL value. I'm glad to hear that I am on the right path. One of the reasons why I am posting is because I am trying to make this work on more than one database. The CHECK constraint works great on full featured databases like PostgreSQL (my favorite) but there are some other popular databases that are not as full featured *cough* MySQL *cough* and don't support CHECK constraints. That is why I was looking for another way of making this work. The solution that dportas posted is very interesting but it still uses CHECK constraints. If there is no other way, I will certainly move forward with CHECK constraints but I just wanted to make sure there was no other way. Thanks again for everyone's help.

[dportas]

Originally Posted by pootle flump

Not wanting us to get into dbdebumk territory - I presume you mean since the redefinition of a relation by Date? That a relation by definition cannot contain nulls?

A relation can't contain nulls. But even if you want to allow tables with nulls then BCNF or 5NF don't apply for example. All the textbook Normal Forms (except for 6NF I think) pre-date the "invention" of nulls. They deal only with values that behave according to some standard rules for dependencies.

To retro-fit nulls into the Normal Forms would require a whole new set of definitions. There have been attempts at dependency theory for "missing" values but I'm not aware of any widley accepted re-definition of the first 5 NFs. In his book (circa 1990) Codd said that dependencies containing null marks should just be ignored on the grounds that nulls are not values. That doesn't help much in cases such as the one suggested here where EVERY row contains a null!

Normalization (without nulls) is supposed to be a formal method for anaylsis and design. With nulls, however, different people seem to adopt different approaches without much consistency. So to say that any table with nulls is "normalized" is a fairly arbitrary statement unless you can explain just what you mean by that.

[georgev]

Originally Posted by dportas

So to say that any table with nulls is "normalized" is a fairly arbitrary statement

Quite right... I'd say a table with nulls can be normalised

[pootle flump]

Cheers
I'll need to follow some of this up TBH. I'm not as well grounded in the theory as some.

Originally Posted by dportas

A relation can't contain nulls.

Is that absolute fact or depend on the definition of a relation? I thought relations were first allowed to contain nulls and then this was "changed". As you can probably tell I first came across relations via relational theory rather than mathematics.

Originally Posted by dportas

5NF don't apply for example.

I might misunderstand you but I don't know how 5NF could be related. I have not come across (nor know of) a 5NF violation that includes any non-key attributes, primarily because the table would have been decomposed to satisfy lower normal forms if it did. In other words - the table is only ever composed of key columns which cannot be null.

Originally Posted by dportas

All the textbook Normal Forms (except for 6NF I think) pre-date the "invention" of nulls.

I thought Codd dealt with nulls in his original paper and that normalisation came later?

Originally Posted by dportas

In his book (circa 1990) Codd said that dependencies containing null marks should just be ignored on the grounds that nulls are not values. That doesn't help much in cases such as the one suggested here where EVERY row contains a null!

That sounds like a much more sophisticated and informed way of expressing my point above!

Originally Posted by dportas

Normalization (without nulls) is supposed to be a formal method for anaylsis and design. With nulls, however, different people seem to adopt different approaches without much consistency. So to say that any table with nulls is "normalized" is a fairly arbitrary statement unless you can explain just what you mean by that.

I'll think on that.

[dportas]

Codd's original paper didn't mention nulls or anything like and they would be unthinkable in his original relational alegbra (what would the projection over a single nullable attribute be?). His normalization papers don't mention nulls or "missing" data either. Citations at: http://www.sigmod.org/codd-tribute.html

Relations were and remain a maths concept dating back well before computing. What Codd did was apply them to data management and extend the concept somewhat with the idea of keys and constraints for example.

I think the first idea of nulls in connection with RM began with Codd's RM/T paper ("Extending the RM to capture more meaning"), which was in 1979 - 10 years later. The normalization work by Codd, Fagin and Boyce all seems to pre-date this.

Fifth Normal Form is very simple. The most elegant and by far the most important of the normal forms. Its basis is the concept of a join dependency (JD) and a relation is in 5NF if it consists only of JDs "implied by super keys". Super keys may include attributes that aren't part of any candidate key.

So what does a JD mean when applied to nullable attributes? Do nulls count as part of any super key? If not, then the table in question is something other than the join of its super keys. If you do want to include nulls in join dependencies then you have other problems. In SQL "nulls do not join". So the JD:

*{A},{A,B}

would not satisfied by R{A,B} if A includes nulls because R{A,B} would not be the same thing as R{A} JOIN R{A,B}.

A no less tricky problem is to define just what relational equality actually means for tables containing nulls. SQL has no obvious equivalent to relation comparison or equality. I can't even tell you what R{A,B} = R{A} JOIN R{A,B} means if either A or B includes a null. I am not convinced that the answer can EVER be True where nulls are involved.

Functional and multi-valued dependencies are just less general examples of join dependencies. So if a null cannot be subject to a join dependency then there is no clear basis for saying that a table with nulls is in BCNF, 4NF, 3NF or 2NF, etc.