Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Violate second normal form to make life easier?

    I'm creating a web application that receives user input. Because data is being submitted by a user, I have to be careful that they aren't passing me bad data.

    For example, if I have users, each of which have a bookcollection, I have to make sure that the users can't pass me a bookcollection_id that isn't theirs. This involves a lot of long joins and selects to see who the owner of a record is.

    For example, suppose:
    [users] table
    +user_id

    [bookCollections] table
    +bookCollection_id

    [bookCollections_users] table
    +user_id
    +bookCollection_id

    [custombooks] table
    +custombook_id
    booktitle

    [custombooks_collections] table
    +custombook_id
    +bookCollection_id


    If a user is modifying a custom book that they already have, the form sends me custombook_id and booktitle.
    I have to jump from custombooks_collections table to bookCollections_users table in order to find out if the user has the right to modify the custombook_id.

    This isn't so bad here, but I have cases where the chain is closer to 4 or 5 tables down from the users table. Would it be ok to violate the second normal form here and include the owner's user_id in a table further "down" the chain?

    Or should I not worry about having to traverse 4 or 5 tables for each input to find out if the user can modify that part?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I would suggest that you only make one round-trip to the database, but use as many tables as you need in order to get the right information. The difference between one table and a dozen of them is trivial in every database engine that I've used.

    Applications come and go, and even the ones that stay around change to adapt to users needs. Data tends to stay around from the dawn of time in many cases, and though at least five generations of application code in nearly every case I've seen...

    If you get the data storage (schema) right, you can build many applications on top of it, and can even support multiple generations of the application (I've done that more times than I can count). Don't sacrifice long term usability for short term convenience, it will kill you if you stay around long enough!

    -PatP

  3. #3
    Join Date
    Aug 2007
    Posts
    10
    Thanks for the response Pat.

    I guess I was worried about the complexity of my SELECT statements in order to retrieve the required information, but I guess that's the least of my concerns. I'll stick with making sure the database is well designed.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Premature optimization is the root of all evil." -- Knuth, Donald

    search: "premature optimization" +normalization
    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
  •