MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesMySQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 9th, 2012, 08:28 AM
BlackAce BlackAce is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2010
Posts: 164 BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Day 17 h 37 m 21 sec
Reputation Power: 44
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.

Reply With Quote
  #2  
Old October 9th, 2012, 09:01 AM
cafelatte cafelatte is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Mar 2008
Posts: 1,923 cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377
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!

Reply With Quote
  #3  
Old October 9th, 2012, 09:32 AM
BlackAce BlackAce is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2010
Posts: 164 BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level)BlackAce User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Day 17 h 37 m 21 sec
Reputation Power: 44
Quote:
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.

Quote:
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMySQL Help > Best table structure for multi-section, nested comments

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap