Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Which design approach is better?

    Hello, I have been discussing database design in my developer forum and we can't seem to decide which is a more pure. Any thoughts would be appreciated.

    scenario i have a comment object and want to be able to associate it, to any given number of objects, more could be added in the future.

    Is it better to
    A: add the FK to the comment table? 1:M
    or
    B: Create an association table ? M:M
    or
    C: Other?


    Solution A:

    comment table
    ----
    id
    comment
    blog_id
    photo_id
    user_id


    Solution B:

    comment table
    ----
    id
    comment

    blog_comment_assn table
    ----
    blog_id
    comment_id

    photo_comment_assn table
    ----
    photo_id
    comment_id


    With Solution A, it seems that you waste space by adding the extra columns and then to find out which one it is related to you have to check each key.

    With Solution B, you obviously create extra tables, but no extra space. But you change the relationship from 1:M to M:M

    I apologize if this has been asked. I am trying to read up on normalization and can't seem to understand which method is best. 1NF suggests don't waste space.

    Thanks in advance for the help!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd probably pick option C. Put the id into the comment table, then use the commentId as an FK in the tables that need a comment.

    The exception to that choice would be if there were many tables that had dependancies on each other that needed to reference a single set of comments (in other words, you truck chassises that had engines in them and wheels on them, and you needed to logically tie one comment to all of the pieces in a given puzzle). Then I would give more thought to a set of linking tables.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'd use GUIDs instead of Identity values. That way you can store the foreign keys from multiple object tables in a single column of the comments table.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    better yet, if the database supports it, use a sequence.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    I'd use GUIDs instead of Identity values. That way you can store the foreign keys from multiple object tables in a single column of the comments table.
    aaaaaaaaaaaarrrrrrrrgggggggggghhhhhhhhhhhh

    there goes the idea of actually defining it as a foreign key



    the correct solution, of course, is D -- have a separate comment table for each object type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2007
    Posts
    4

    Question

    Thanks for the quick replies. I am using MySQL so I am looking into GUID's and Sequences now.

    Would there be a noticable performance hit in using GUID's or Sequences. GUID's seem like large id to join with?

    Looks like mysql has a UUID() function so I will compare that to the GUID and see if that will work me.

    Am I correct to assume that Option A below is not normalized or in normal form because it wastes space?

    Option B is bad because I am trying to represent a 1:M using a M:M form?

    I want to understand the whole thing, instead of just taking the first answer and running! Thanks again!

  7. #7
    Join Date
    Jul 2007
    Posts
    4
    Thanks r937,

    So just to clarify, Option D would look something like and that would be proper normal form?

    blog_comment
    -----
    id
    blog_id
    comment

    user_comment
    -----
    id
    user_id
    comment


    My dilemma is this. Comments seem like they would be their own object and havinvg multiple tables in the Option D would create seperate objects for each comment type.

    What about Solution E

    comment table
    ----
    id
    text

    comment_type table
    ----
    id
    type

    comment_lookup table
    ---
    comment_id
    child_id
    type_id
    user_id
    Last edited by socc; 07-19-07 at 17:20.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you could take a step back and create some sample rows which actually relate the comments to the objects

    this will be a first-order proof-of-concept for you

    i mean, let's look at your suggestion for the blog comment table --
    blog_comment
    -----
    id
    comment
    sample rows for this would be

    id comment
    42 no that's not true
    43 fifty two
    44 yes, i agree with todd
    45 i never said that

    see anything unusual? you can't tell which blog entry each comment is associated with!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2007
    Posts
    4

    sorry about that

    hey r937,

    yea sorry about that. i did catch it and edit my post, but apparently not quick enough =)

    so.. Option D would be like
    blog_comment
    -----
    id
    blog_id
    comment

    user_comment
    -----
    id
    user_id
    comment
    Last edited by socc; 07-19-07 at 17:38.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yep, dat's da ticket!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    aaaaaaaaaaaarrrrrrrrgggggggggghhhhhhhhhhhh
    Is it pirate day today? I forgot both my cutlass and my mascara...
    Quote Originally Posted by r937
    there goes the idea of actually defining it as a foreign key



    the correct solution, of course, is D -- have a separate comment table for each object type
    There are drawbacks to using separate tables as well. I've used both methods, and the decision really comes down to the details of the requirements for each particular application.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by r937
    there goes the idea of actually defining it as a foreign key

    the correct solution, of course, is D -- have a separate comment table for each object type
    I actually agree with the blindman on this!!

    It's all well and good saying use multiple tables just to preserve your foreign keys but it comes at a cost. All your code now has to support multiple tables rather than just one. This means more time to produce the code, more effort in maintaining it. You're also quite likely to get more bugs where one query is pulling comments from most of the comment tables but perhaps missing the last comment table that recently got added.

    More tables means more code!

    Mike

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    More tables means more code!
    so use a punch card file system, and you're golden

    only one "table" so the code will be at an absolute minimum

    and no pesky FKs to worry about



    and with that, this is another thread i'm leaving because of where it ended up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I'm not even going to comment... Except for this of course. Oh, and that.

    No more comments after this!
    George
    Home | Blog

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by mike_bike_kite
    I actually agree with the blindman on this!!
    I'm sure Rudy will never let me live this down...
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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