CODEWITHSUNDEEP
asp.netdatabase-design
Pabuc
Pabuc

Reputation: 5638

Comment system database design

I'm developing a system like SO (completely different topic) and replies and comments are alike with the system we see everyday on StackOverflow.

My question is, I'm loading the question with a Stored PROC, loading replies with another Stored PROC and now I'm adding comment system. Do I need to fetch the comments 1 by 1 for each of the replies on topic?

This means that if I have my page size set to 20 replies, I'll be doing 22 database operations which is more than I was thinking.

I don't think I need to add my database diagram for this question but still here it is:

Questions
-----------
QUESTION_ID
USER_ID
QUESTION_TEXT
DATE

REPLIES
-----------
REPLY_ID
QUESTION_ID
USER_ID
REPLY_TEXT
DATE

COMMENTS
------------
REPLY_ID (fk replies)
USER_ID
TEXT
DATE

Upvotes: 0

Views: 1071

Answers (2)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

Pabuc,

For your initial Question, why not get all the results using a single Query for the given question / reply ?

select reply_text, user_id 
  from REPLIES
  order by DATE asc

Also, as you pointed out, except for the minor differences, the question and answer have almost the same attributes as that of a post.

Wouldn't a model like the one below make more sense? The Question and Answer are both "posts" with the only difference being an answer has the question as the parent and the question has no parent.

Create table post -- question/reply (
   post_id number,
   parent_post_id number, -- will be null if it is the question, will have the question id
                          -- if it is a reply to a question
   post_text varchar2(4000),
   user_id   number,
       post_date date);

-self referential foreign key
Alter table post
  add constraint foreign key (parent_post_id) references post(post_id);

--comments to all posts (questions/replies).

create table comments(
  comment_id number,
  post_id number,
  comment_txt varchar2(140),
  comment_user_id number,
  comment_date  date
);

alter table comments add constraint fk_comments_post
  foreign key (post_id) references post(post_id).

-- for a given Question (post) id, you can get all the replies and posts using...

select replies.*,
       comments.*
  from posts replies,
       comments
  where replies.parent_id = :Question_id --input
    and comments.post_id = replies.post_id

You might have to add an order by clause to get the results based on points, updated_timestamp or any other attribute as needed.

Upvotes: 1

Kelsey
Kelsey

Reputation: 47726

You should get all your comments at once.

Then make DataViews from the result with a filter for each reply and bind to that DataView. You could also use linq to entities and just filter out new sets on each bind. Here is a basic pseudo code example:

  1. Get all comments for all replies to question
  2. Bind replies
  3. Implement the OnDataBinding for the reply control that will display the comments
  4. In the OnDataBinding add a filter to the result set for the comments with the same reply ID
  5. Bind the filtered list of comments to the display control for comments

This should work and I have implement the same scenario for similar types of data structures.

Upvotes: 2

Related Questions