#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45

    Best table structure for multi-section, nested comments


    I'm hoping you experts can help me think through the best approach for something I am working on.

    On the site, we have 3 sections that contain various details about specific phases (Phase 1, Phase 2, Phase 3) of a project. Users will have the ability to post comments in each of the sections. They will also be able to make comments on previous comments, but only 1 nested level deep.

    So:
    ===================
    Project 1
    Phase 1
    Phase 2
    Comment 1
    Comment 1a
    Comment 2
    Phase 3
    Project 2
    Phase 1
    Comment 1
    Comment 1a
    Comment 1b
    Comment 2
    Comment 2a
    Phase 2
    Phase 3
    ===================
    Etc.

    To handle this, I am thinking that my table will need to contain the following columns:

    ===============
    | comments.tbl |
    ===============
    | comment_ID |
    | project_ID |
    | phase_ID |
    | top_ID |
    | user_ID |
    | comment_ts |
    ===============

    WHERE
    comment_ID is the primary key
    project_ID ties to the project ID stored in the projects.tbl
    phase_ID indicates which phase section this comment belongs
    top_ID if not null references the comment_ID in this table that this comment is in reference to
    user_ID ties to the user ID stored in the users.tbl for the poster
    comment_ts stores the timestamp when the comment was posted

    Assuming that is the correct structure (which I'm sure you'll tell me if it's not), how would I best query this table in PHP to output my comments in the format shown above. I know I would put the project_ID and phase_ID in the WHERE clause to narrow it down that far. But how would I show the comments nested appropriately:

    ===============
    Comment 1
    Comment 1a
    Comment 1b
    Comment 2
    Comment 2a
    ===============
    Last edited by BlackAce; October 9th, 2012 at 08:32 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I'm not a fan of "what's the best..." type questions - I think they're kind of sneaky! That said, it's probably kindest if you don't now search these forums for examples of where I've done exactly that!

    I think this structure's OK. The only problem with this model is that there's a redundancy: a child comment MUST belong to the same project and same phase as its parent. In these instances, it's probably best to separate the 'response comments' out into a separate table.

    Also, your table may want to include a column for the actual comment!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    Originally Posted by cafelatte
    I think this structure's OK. The only problem with this model is that there's a redundancy: a child comment MUST belong to the same project and same phase as its parent. In these instances, it's probably best to separate the 'response comments' out into a separate table.
    Good call, of course you are right here.

    Originally Posted by cafelatte
    Also, your table may want to include a column for the actual comment!
    Hee hee... Whoops!

    So once I break this into two tables, can you (or someone) help me with the query structure to loop through the results and output the nested comments? If you do a search for my name, you'll see I do a really good job at screwing up JOINs, and I'm trying to save myself a little bit of preemptively.

    EDIT: Actually, maybe I don't need the JOIN at all. Maybe it's two separate queries, with an IF statement within the first one, checking to see if there are any associated nested comments. Hmmm... will have to try that.
    Last edited by BlackAce; October 9th, 2012 at 09:47 AM.

IMN logo majestic logo threadwatch logo seochat tools logo