mysql - Stackoverflow-like commenting database schema -

i'd make commenting thread stackoverflow on each q&a page. issue see how notify each subscriber (questioner, answerers, commenters) of new comment allowing them each ability mark comment read.

i see 2 general solutions:

1. make row each subscriber, replicating actual comment in every 1 of these rows. allow field in each row each subscriber mark comment read (eliminating little red circle @ top of page).
2. only have 1 row each comment. subscribers lumped in single field , there have complex queries select , update whether each subscriber has marked comment read.

maybe there better way?

here's generic schema show 2nd approach answers can have refer to:

comments table  comment_id user_id question_id     comment       comment_read 1            1          1        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        question   ... 5            1          2        bad question    ... 

it seems if comment_read column second approach intensive. if there thousand comments on thread?

when faced these types of questions want think perspective... how going query data often? want tailor schema that. query based on user logged in: new comments should user notified about?

why not make easy , have comment / user table in addition 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, query determine user's unread comments easy (e.g. select is_read=no , user_id = 1). comment/question perspective it's easy list of unread comments.