Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Jul 2008
    Posts
    10

    Always normalize to 5NF or not just index instead?

    Before reading what is below please realize I have never designed a DB for any one or organization with the exception being what I have dabbled with out of textbooks.

    Over the last two weeks, I have researched DB normalization for days, literally.

    "The Key, The Whole Key, and Nothing but the Key, so help me Codd."

    OK. Finally, I found this suspected guru, Pinal Dave:

    SQL SERVER – Effect of Normalization on Index and Performance Journey to SQL Authority with Pinal Dave.

    and I agree with him, after you fully understand a client's needs normalize/map the firm into 5NF tables.

    When I consider how we are still, for a little while longer at least (nano nano) following Moore's law, I think Mr. Dave is right. Normalize to the 5NF, unless your firm's needs require beyond 5NF, and monitor usage for any potential
    denormalization needs.

    Every time I read something to the effect of: "yes, but all of those joins will slow the DB down....".

    I think to myself.... "OK get faster machines. I have to assume we have all of these joins, because the firm is size-able, large, at least in transaction size and hopefully has the ability to invest in faster hardware."

    So problem solved. Please, DO NOT try to sell me on the idea that too many joins will slow the system down.

    I am interested in some one describing a real world example which contradicts my idea that too many joins can be solved by faster hardware or more processing power, arrays of machines.

    Of course the example can stay anonymous, no actual company names are necessary.

    On a message thread somewhere, someone wrote something to the effect of the following:

    Youtube and Facebook maintain large well indexed tables and essentially DO NOT normalize.

    AND I cannot sleep, literally. It is unbelievable to me that the fore mentioned web sites do not normalize their tables.

    Thanks in advance for your comments.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    From what little I have seen of Pinal before this, in my opinion he is lacking in common sense.

    But you seem to have already made up your mind, so go ahead with 5th normal form and enjoy the many hours you will spend coding and maintaining it.

    We, here at dbforums, eagerly await the pleas for assistance that are sure to originate from whatever unlucky sap has to maintain your application after you are gone.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,426
    I personally thought he gave the game away by making references to the most recent TWIT received or sent on Twitter.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Wait a minute....where does Pinal recommend 5th normal form?
    And what do you mean by "unless your firm's needs require beyond 5NF"?
    Are you confusing 3rd normal and 5th normal?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    lexcola,

    Clearly the main advantages of a Fifth Normal Form database over a denormalized one are that it eliminates many common problems caused by redundancy, including update anomalies and redundant logic in code.

    Also a normalized schema reduces any "bias" in the design and makes it more suitable for changes as requirements evolve - obviously very important for iterative and Agile projects (in fact normalization has been called one of the "secret assumptions" of Agile).

    Regarding performance. You have to look at each particular scenario with its indexing, storage, data and queries. Normalization has nothing to do with performance because it tells us nothing about how the data will be physically stored and accessed. Of course it would be possible to contrive an example to prove that one implementation performs faster or slower under certain conditions but that won't tell you how your database will perform.

    With that in mind though, using Normal Form with all the advantages I just described is very likely to give you the best chance to get good performance from your database overall. Simply by avoiding redundant data and code, avoiding biased designs and reducing the work needed to maintain integrity you will be doing everything right to get the best out of the DBMS you are using.
    Last edited by dportas; 10-23-09 at 15:42.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by lexcola
    Clearly the main advantages of a Fifth Normal Form database ... ...
    It goes past third????

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Quote Originally Posted by mike_bike_kite
    It goes past third????
    ...or else it gets the hose again.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jul 2008
    Posts
    10

    got it short and sweet, thanks gents

    got it short and sweet, thanks gents

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by blindman
    go ahead with 5th normal form and enjoy the many hours you will spend coding and maintaining it.

    We, here at dbforums, eagerly await the pleas for assistance that are sure to originate from whatever unlucky sap has to maintain your application after you are gone.
    What makes you say this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Schadenfreude. The perverse sense of pleasure derived from saying "I told you so".
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Ok - so why do you think a database normalise to 5th normal form require more code and maintenance?

    If your database is not in fifth normal form and you want to ensure there can never be any update anomalies then you need to write some triggers. If it is in fifth normal form then you don't.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    Quote Originally Posted by pootle flump
    Ok - so why do you think a database normalise to 5th normal form require more code and maintenance?

    If your database is not in fifth normal form and you want to ensure there can never be any update anomalies then you need to write some triggers. If it is in fifth normal form then you don't.
    Exactly. You'll need more lines of code if the database is denormalized. You'll create more headaches for application developers. You'll also spend much more time refactoring code as the data model changes because you'll have to update some things multiple times.

    Denormalization violates the principle of DRY. If you want to protect yourself against change, support good development practice or use iterative development methods then Normal Form really is an essential discipline. There's not much excuse for not doing it.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    I was equating fifth-normal form with EAV. I stand corrected.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Aug 2009
    Posts
    68
    Quote Originally Posted by dportas
    Exactly. You'll need more lines of code if the database is denormalized. You'll create more headaches for application developers. You'll also spend much more time refactoring code as the data model changes because you'll have to update some things multiple times.

    Denormalization violates the principle of DRY. If you want to protect yourself against change, support good development practice or use iterative development methods then Normal Form really is an essential discipline. There's not much excuse for not doing it.
    IMHO, things are not as clear as you say. In my experience, the normalization process *alone* leads to develop terribly slow gas plants, requiring much useless work from applications programs. Hopely, they're able to get the job done, but sometimes even not… I have many examples of this.
    Because databases designers applied blindly their favorite theory, believing naively this is enough. Open your eyes: database communication with application program is an issue which cannot be solved ONLY creating normalized databases.Agile programmers speak of an "impedance mismatch" (an understatement, I think).
    I'm both an application and database designer, and to me, standing fiercly on relationnal model or on object model, ignoring or deprecating the other one is equally stupid. To me, the real problem is getting the best of the two, and this means often trade-offs and compromissions.
    Regards
    Laurent

  15. #15
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    740
    Agreed. I certainly didn't mean to imply that Normalization is all you need. Good application design and OO design is just as important.

Posting Permissions

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