Question

I m writing a simple "Facebook Wall" type feature where a user can post on another person s wall and people can simply reply to that Wall post, or post a new wall post. Users cannot reply to a reply, you may only reply to the original wall post (just like facebook)

My original mySQL db schema that I had thought of goes like this:

post_id (pk)
post_text (char)
date_posted (datetime)
is_parent (bool)
parent_id (id of parent)

How it works:
If someone posts a new wall post, is_parent will be set to 1, and parent_id will be set to null.

If someone posts a reply to that post, is_parent will be 0, and parent_id will be set to the ID of the parent post.

My Questions

Is this a good schema for this feature? If not, what schema would you use?
If it is good, how can I do a single query that will return all the wall posts in order of most recently posted, while grouping the children with the parent so that when I iterate over the query result, the parent and children all come together.
Or is it better to do 2 queries? One query for all the parents, 1 query for the children.

Answer 1

First of all. I don t think you need two field for the same purpose. parent_id is enough in this case. When you have post_id set auto-increament. The first assigned value will be 1, so parent_id=0 will never happen. You can assume when parent_id=0 it is a first level post.

It would be better to use one single query for all replies.

Answer 2

No idea/probably. Lose the is_parent column.
I recently did this by querying for all posts sorted by the ascending order of post_id. Then cached it in a 2D array with some PHP (array[ parrent ] -> array[ post ]...[]).
One should do it in method described in #2

友情链接