Results 1 to 11 of 11

Thread: normalized?

  1. #1
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    normalized?

    could i get an opinion, please, on whether or not these tables are "properly normalized"

    say we have a users table with username, password, and fullname

    case 1, we make username the PK

    what normal form is it? at least 3rd, right?

    okay, case 2, we add a surrogate key, userid, an autonumber-type column, and make this the PK

    is this still normalized?

    case 2a, what if we also add a unique constraint on the username

    is this any more or less normalized than case 2?

    AtDhVaAnNkCsE


    oh, and if you're wondering, shouldn't i know the answers to this, yes, i believe i do, but i'd like a second opinion


    "okay," says the doc, "you're ugly too"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In case 2, is there only one key, namely the surrogate? Or is username still a key?

    So in case 1 there is one key. In case 2 there are two keys. In case 2a there are three keys.

    If the above is true then they are all in 5NF.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    In case 2, is there only one key, namely the surrogate? Or is username still a key?
    yeah, username is still a key, because it would get a unique constraint, to prevent two users from having the same username

    here's the guy i'm having problems with...

    same scenario, a users table where username is the key, and now you add a surrogate autonumber key (ostensibly the reason for this is to allow a user to change his username without any ripple effect on the database, since all relationships would be built on the surrogate key)

    he claims that this would no longer be normalized...
    There are fields there that are not dependent on the key, the whole key and nothing but the key and so it is not normalised.

    To get a table into third normal form you must "Remove non-key columns representing facts about other non-key columns into a separate table" so since all of the fields except the key and the username are dependent on the username to properly normalise that data you should split it into two tables with username as the primary key of the second table since all of the fields apart from username represent facts about that user.
    i especially like the last bit... username no longer represents a fact about the user???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So essentially the question is "can a table with a surrogate key and non-key attributes be in first normal form?".

    The guy is saying that the relation should be decomposed in to two relations related by a 1:1 relationship to be normalised? So:
    user_1 {surrogate, username}
    user_2 {username, password, fullname}
    Candidate keys underlined, primary keys bolded.

    In the case you've described he's talking nonsense (and poorly phrased nonsense). Is this someone on another forum? Is he someone you respect? I imagine he must be otherwise you wouldn't be double checking here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ouch. Just seen the thread. Now I see why.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you googled the thread?

    so you see what i'm up against, a bombastic know-it-all who actually doesn't

    the reason i asked here is because i am not 100% confident in my own ability to explain normalization, so i was hoping for some more ideas, one of which might allow me to make a succinct point that he would actually understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't fully understand his statement - his definition of 3NF is not clear and suggests an error - I am not certain. The more I read it the more it sounds like he believes that a relation only ever has one key. It is certainly not a helpful summary of 3NF since it is subject to interpretation.

    I have seen it very briefly presented once before that surrogate keys violate first normal form. Ultimately a surrogate key is just that - it is a unique column used in lieu (i.e. a surrogate, in place of) of the natural key(s) to represent the entity throughout the other entities of the database. That Date and even Pascal advocate their use in certain circumstances is enough for me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2008
    Posts
    277
    Pootle flump inspired me to google the original thread as well.

    all of the fields except the key and the username are dependent on the username
    This is wrong. If we put a unique constraint on username (and certainly, this is necessary to enforce the natural key), then username is a candidate key for the row, and by definition is a determinant for the surrogate key. He seems to misunderstand the whole reason a surrogate key was added in the first place: to create a "synonym" for the natural key.

    id -> username, password, fullname
    username -> id, password, fullname

    We could choose either one. Obviously, we go with the first, since the whole reason for doing this is to allow changes to the username without causing cascading updates throughout the database.

    So now we need a definition for 3NF. I like to use the one from Data Modeling Essentials:

    "A table is in 3NF if the only determinants of nonkey columns are candidate keys."

    Username remains a candidate key (obviously -- it's the natural key), but that doesn't mean the table isn't normalized.
    Last edited by futurity; 10-19-10 at 12:50.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the confirmation, futurity

    i could point you to other examples of this gentleman's database delusions, but i'm not interested in humiliating him further (he does a splendid job of it all on his own)

    well, okay, except for one example

    in a different thread discussing surrogate versus natural keys, he claimed he had never, in several decades of working with databases, seen an example where a natural key didn't exist

    so i walked him through the forums/threads/posts model, in which the natural key for the forum was the forum name, the natural key for the thread was the composite forum name plus thread name (since different forums could have threads with the same name), and so on

    therefore, in the interest of practicality, it is necessary to assign an "id number" to each forum, thread, etc. -- much like vbulletin does it (see the url for this thread or this post)

    his reply was that the id then "becomes" the natural key

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

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The problem seems to resolve around nomenclature.
    What the OP terms a "role" would be more appropriately described as an "Access Level".
    It makes sense that users could have more than one role, and in such cases it should be split into a separate table. But Access Level is more likely unary, and should remain in the user table.

    But (as you might guess), I think felgall is going way overboard about the dangers of having a numerical (surrogate) key.

    "Adds several dozen other complications"?
    Pish-tosh, and total FUD.

    "The only time you should be adding extra numeric keys to tables is when they have no natural key, when the natural key is too long to be used, or the key value changes too frequently (say twice or more since you started reading this)."
    A completely unsuportable and narrow-minded blanket-statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    ... total FUD.
    unfortunately this is what we get from him on a regular basis

    dportas!! you are my hero!! (david has signed up and jumped in to the thread)

    however, i'm afraid that your explanations of normalization will be ~way~ over his head -- i'm still struggling with them myself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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