CODEWITHSUNDEEP
mysqlsqlpublish-subscribecomments
tim peterson
tim peterson

Reputation: 24305

Stackoverflow-like commenting database schema

I'd like to make a commenting thread like stackoverflow does on each Q&A page. The issue I see is how to notify each subscriber (questioner, answerers, commenters) of a new comment allowing them each the ability to mark the comment as read.

I see 2 general solutions:

  1. Make a row for each subscriber, replicating the actual comment in every one of these rows. This would allow a field in each row for each subscriber to mark the comment as read (eliminating it from the little red circle at the top of the page).
  2. Only have 1 row for each comment. The subscribers would be lumped in a single field and there would have to be some complex queries to SELECT and UPDATE whether each subscriber has marked the comment as read.

Maybe there is a better way?

Here's a generic schema show the 2nd approach so answers can have something to refer to:

Comments table

comment_id user_id question_id     comment       comment_read
1            1          1        good stuff      yes=user_id=1, no=user_id=2, no=user_id=3
2            2          1        bad stuff       yes=user_id=1, yes=user_id=2, no=user_id=3
3            3          1        worse stuff     yes=user_id=1, yes=user_id=2, yes=user_id=3
4            4          2        good question   ...
5            1          2        bad question    ...

Upvotes: 1

Views: 367

Answers (1)

zevra0
zevra0

Reputation: 209

It seems as if the comment_read column second approach would be too intensive. What if there are a thousand comments on the thread?

When faced with these types of questions you want to think about perspective... How are you going to query the data most often? You will want to tailor schema to that. You will probably query based on the user logged in: what new comments should the user be notified about?

Why not make that easy and have a comment / user table in addition to the Comments table.

 user_id comment_id is_read
 1       1          yes
 2       1          no
 3       1          no
 1       2          yes
 2       2          yes
 3       2          no
 ...

That way, the query to determine a user's unread comments is fairly easy (e.g. select where is_read=no and user_id = 1). From the comment/question perspective it's also easy to get the list of unread comments.

Upvotes: 2

Related Questions