How might I design a commenting system like that on Stack Overflow?

Question

I am a big fan of the design and functionality of Stack Overflow. So, I would like to create my own design for a commenting system based on the one used for this site!

Questions:

• What sort of table structure should I use for storing comments?
• What SQL queries might I then use for fetching stored comments?

Answer 1

This is a fairly obvious structure. The entities are:

Post: ID, Post Type (Question or Answer), Text, User ID
Question extends Post: Title
Answer extends Post:
Comment: ID, Post ID (attached to which Post), User ID, Text

And you can extend this easily to add tagging, editing/revisions, votes, flags and close/reopen votes.

Answer 2

@cletus's answer is useful but uses "extends" which is hardly a standard relational term, so let me offer my 2 cents.

I'd assume that comments (what you're specifically asking about) are about "items", a very general category encompassing questions, answers, and maybe more things yet (though in SO comments are only about questions or answers).

So an item would be a table with a PK of ItemId, all fields common to all items (a timestamp on which it was created, a user ID as a FK on a table of users, etc) and possibly a "ItemType" field usable for picking which other table (Questions, Answers, maybe more yet) to get specific details for the specific item, if any. I'd probably index each of these as I can easily imagine looking for items in a certain range of times, posted by a certain user, &c.

A Comment would have its own PK CommentId, a FK of ItemId, a timestamp, the author's UserId, and maybe a couple more column if you allow up/down votes on comments (or even just upvotes on them a la SO), flagging, &c.

To get, say, "all comments by user IDC on items by user IDI", I'd query:

SELECT * FROM Comments
  JOIN Items USING (ItemId)
  WHERE Comments.UserId = @IDC
    AND Items.UserId = @IDI

I hope all other queries of interest are just as obvious as this one (if all interesting queries are trivially simple, this speaks well about the DB schema that makes them so;-).