Facebook style comments database design


I want to implement a facebook comment script to my webpage where users can post to other users walls but then other users can comment on the original comment…

Anybody have any ideas on the database design for this for me to extract using php.

I’m guessing 3 tables will do it (users, status_update and replies) but i’m clueless as to how i should design it (join tables etc…) every user will have a profileID but i can’t figure out how the newly added replies to the original comments will work.

Are you familiar with foreign keys? If not, do a little R&D on the topic.

The status_update table has an ID field of its own (say, updateID) and references the profileID of the user making the update.

Your replies table will reference the profileID of the user making the reply, and also the primary key field from the status_update table.

So, to display a user’s page along with their updates and all replies, your SQL may look something like this (all column names are made-up, but you should get the idea):

  status_update s
  left join
  replies r on s.upateID = r.updateID
  left join
  users u on r.profileID = u.profileID
  u.profileID = @some_users_id

The left joins are used to ensure with no replies are not filtered out.