Best storage and retrieval practices for comments in PHP/MYSQL

I'm having a bit of a problem when it comes to my dat开发者_运维技巧abase and query design. Here is what I'd like to have (Honestly, a lot like stack overflow).:

• An item has many posts.
• A post has many comments.
• Comments can be flagged, liked, and disliked.
• Comments cannot have subcomments.

The table structure is as follows:

Items
-----
iid
desc
...

Posts
-----
pid
iid
uid
date
desc
...

Comments
-----
cid
pid
uid
date
desc
...

The logic for all of this is: get all posts for item -> for each post, get all comments. Would it be best to do this all in one query? Is it best to do one query to get all posts then a separate query for each set of comments on each post?

If I do one query, then I'll potentially have a 100 row behemoth where I'll have a ton of duplicate data. If I do a separate call for each post, then I'll have far too many queries. Adivce?

I imagine your system is going to have 3 distinctive views and I would structure it as follows:

1. A list of items - showing the item description and the number of posts attached to that item. e.g. SELECT i.desc, ...., COUNT(p.pid) FROM items i LEFT JOIN posts p ON i.iid = p.iid GROUP BY i.iid ORDER BY i.date DESC
2. A list of posts for a selected item - showing the posts and number of comments made for each post e.g SELECT p.desc, ...., COUNT(c.cid) FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid GROUP BY p.pid ORDER BY p.date DESC
3. A post page which shows the post and then comments underneath. This page would require two queries. A query to collect the post information, returning exactly 1 database row. And then a query to collect each individual comment attached to this post. E.g. SELECT p.desc, ... FROM posts p WHERE p.pid = $pid LIMIT 1 and SELECT c.desc, ... FROM comments c WHERE c.pid = $pid ORDER BY c.date DESC. You could alter the ordering here to mimic how stackOverflow orders the data by oldest, votes, etc.

I would say this is scalable model and I would always advise writing lean MySQL queries that only retrieve the information needed each displayed entity.

To mimic this page I would use the following code ...

itemRs = mysql_query("SELECT i,iid, i.desc, ... FROM items i WHERE i.iid = $iid LIMIT 1");

// all posts relating to the item
postsRs = mysql_query("SELECT p.pid, p.desc, .... FROM posts p LEFT JOIN comments c ON p.pid = c.pid WHERE p.iid = $iid ORDER BY p.date DESC");

// all comments for all posts relating to the item
commentsRs = mysql_query("SELECT c.pid, c.cid, c.desc, .... FROM comments c INNER JOIN posts p ON p.pid = c.pid INNER JOIN items i ON p.iid = i.iid WHERE p.iid = $iid ORDER BY p.date DESC");

You then would need to create a method to display this data. Perhaps encapsulate all this functionality into a DAO (Data Access Object) called Item that retrieves all this required data before calling a display() method to show the data in the format you want.

I would do this in two queries: one query for to get all the posts of a particular item and other query for to get all the comments of all posts of a particular item. Some ActiveRecord implementations work the same.

Assigning auto id to post and comment and also item and retrieving post and comment belongs to that item id will be best, i suppose.