DB for Commenting System

Question

i wanna create a 2 level status message system. Which is the best way to create a tables ?

Scope:

1. User sets a Status Message
2. Users Reply to the status message

this is a picture showing it

Tables i have created

users (id, name .... ) status_messages (id, message, time, user_id) status_message_replies (id, message, time, status_message_id, user_d)

Some one suggested this can be done in a single table format

status_messages (id, pid, message, time, user_id)

where pid = selfId or ParentId of the status.

I wanna know which is the best method to create the system ?

Answer 1

A classical IS-A relationship: every reply is a message with an extra attribute (the message it is a reply to). This is probably not the best way to model it. You'll be running the risk of having to write a lot of UNION queries over those two tables.

Alternatives:

• just one table: status_messages (id, message, time, status_message_id, user_id), and allowing status_message_id to be NULL
• use a HAS-A: one table status_messages (id, message, time, user_id) and one table replies (reply_id, replies_to_id

The former has the disadvantage that working with NULL is tricky in SQL. The latter will necessitate joins when you want to query replies specifically.

BTW it's much clearer (IMO) to name columns after the relationship they stand for, not the table they refer to.

Answer 2

As long as the original messages and the responses have the same structure (set of attributes, or columns) then you can use the single table approach. It has the advantage that you can search over original messages and responses with a single query.

The set of original messages can be found where pid = selfid and the responses where pid <> selfid. If it's important to be able to see the original and response messages separately (without knowledge of the storage mechanism) you can encapsulate the above conditions in two VIEWs: OriginalMessages and Responses.

If the originals and responses have different attributes (for instance, if you want the original to allow links to URLs, photos, etc) you might consider using two separate tables. But even there, I'd probably argue for the one table structure with a separate, extender table for the additional attributes. That means you don't have to store often-empty columns for those original messages that don't use the extended attributes, and you can later easily add the extended attributes to the response messages as well (if desired).