Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    many to many design question

    I am trying to figure out the cleanest way to allow users to add comments on multiple types of tables. Here is an example:


    Tables users can comment on:
    - Cars
    - Trucks
    - SUVs


    Comment Table Layout:
    id
    userId
    targetType (table name for the comment's target)
    targetId (this is the id of one of the above tables)
    content
    dateAdded


    I was wondering if there is a better way to go about this, if this is not clear please let me know and I will do my best to further explain.


    Thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are several ways to approach this, but your idea of storing the table name isn't one of the good ones

    one approach is to have a separate comment table for each table that allows comments

    this is the simplest and easiest, and the only people who don't like it are usually programmers who have spent their entire lives looking (often needlessly) to make "optimizations"

    another approach is to ask why in the world would you have separate tables for cars, trucks, and suvs, when you could have just one table for vehicles?

    in fact, this is the direction you should take -- a table for vehicles, and then of course only one comment table, and in addition, you could have secondary tables for cars and trucks and suvs if there is enough difference in the data columns that these different types of vehicles require

    (and if there aren't enough differences, then of course a single vehicles table is sufficient)


    the concept i just described is called "supertype/subtype" and a web search will reveal lots more information

    just remember that the comments go on the supertype

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

  3. #3
    Join Date
    Jan 2010
    Posts
    4
    The real tables I am creating don't have anything to do with cars or trucks. I am required to keep the actual names under wraps until release. However, to entertain the idea, each table is going to be drastically different. So there would be many fields that would be worthless to other tables.

    I'm not throwing out the idea for making a separate comment table per target table (as it certainly sounds easy to implement), but I am curious in exploring other options; mainly because i haven't had an opportunity to talk with many database gurus.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DarkNSF View Post
    The real tables I am creating don't have anything to do with cars or trucks.
    neither did my answer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    4
    Ok, well to clarify. The tables I am creating seem as though they should not have a parent, they really don't have anything in common other than they share the ability to be commented on.

    Are you recommending that even if this is the only thing they share there should still be a parent for each of those tables that holds a comment.id?

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by DarkNSF View Post
    Ok, well to clarify. The tables I am creating seem as though they should not have a parent, they really don't have anything in common other than they share the ability to be commented on.
    According to your description, it looks like they do have a key in common: "targetid", which is supposed to reference one of the three tables. So it may make sense to create the super type table.

  7. #7
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by DarkNSF View Post
    Are you recommending that even if this is the only thing they share there should still be a parent for each of those tables that holds a comment.id?
    Without specifics, it's hard to give recommendations other than what r937 has already given.

    If there is truly nothing in common among the tables other than that you want to hold comments for them, create a separate comment table for each table.

    However, if there is some commonality among the tables (they're all 'products', for example), then use subtypes and create one comment table referencing the supertype.

    The only other scenario I can imagine -- though I suspect any realistic one I could come up with would point to subtyping (or a missing relation) -- is if the tables are truly distinct but a comment can apply to several of them at once. In this case, create one comment table, then either a) set up intersection tables between the comment table and the tables to which the comment can apply, or b) hold a (nullable) foreign key in the comment table for each table to which it can apply.

    You need to look at your model and determine which case applies. In any case, do NOT consolidate comments into one comment table simply because multiple tables can have comments. Down that road lies the OTLT, and that is a road you don't want be on.

    One final note: the assumption here is that you are interesting in holding multiple comments for each row in the table being commented on. If this is not case, and you need to store at most one comment per row, then simply implement the comment as an additional column in the table(s) of interest.

  8. #8
    Join Date
    Jan 2010
    Posts
    4
    I like the idea of intersection tables, I am assuming that be like adding a table like BlogComments(blogId,commentId)?

    edit: actually i think that nullable foreign keys would be the easiest to implement while maintaining one comment table.


    thanks for your advice guys
    Last edited by DarkNSF; 01-04-10 at 21:12.

Tags for this Thread

Posting Permissions

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