CODEWITHSUNDEEP
phpmysqlschemaparent-child
justinl
justinl

Reputation: 10538

What is a good db schema and method to query a "Facebook Wall" feature

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

  1. Is this a good schema for this feature? If not, what schema would you use?
  2. 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.
  3. Or is it better to do 2 queries? One query for all the parents, 1 query for the children.

Upvotes: 0

Views: 554

Answers (2)

mathon12
mathon12

Reputation: 181

  1. No idea/probably. Lose the is_parent column.
  2. 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']...[]).
  3. One should do it in method described in #2

Upvotes: 0

Darkerstar
Darkerstar

Reputation: 932

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.

Upvotes: 1

Related Questions