Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2008
    Posts
    9

    Database Architecure / Normalization Quandry

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if americans can have their own version of english, so can we

    just be thankful that we still honour most britishisms

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2008
    Posts
    9
    Quote 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.

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote 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.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote 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
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cheers
    I'll need to follow some of this up TBH. I'm not as well grounded in the theory as some.
    Quote 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.

    Quote 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.

    Quote 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?

    Quote 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!

    Quote 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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •