1

For example, I have two tables: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, QuestionID, AnswerContent).

Now, I have a Vote table that support vote for both Question and Answer. I have three ways for designing database:

1. Vote(VoteID, QuestionID, AnswerID) // always QuestionID or AnswerID is null

2. Vote(VoteID, PostID, type) // type = 0. PostID is Question. type = 1. PostID is Answer

3. VoteAnswer(VoteID, AnswerID)
   VoteQuestion(VoteID, QuestionID)

I don't know which database design I should follow in those cases, using ORM and just using native query.

Thanks :)

  • How are you going to report on votes? Are you frequently going to want to run queries that combine votes on questions and on answers? Or are you going to want data on one or the other? Are you going to apply rules to votes in general or to one type of vote? My guess would be that it would be pretty unusual to want to combine the data so you'd be better off with separate tables for AnswerVote and QuestionVote. But if this is an abstraction of your real requirements, my guess might not match your reality. – Justin Cave Jul 15 '15 at 20:43
  • A question has many answers. In one page, I often display a question and list of answers. (and also display number of votes of question and each answer). I don't know, in this case, if I use one table for Vote, will it gain performance ? Thanks :) – hqt Jul 16 '15 at 13:56
0

i'd go for a different approach:

Post(PostID, PostTitle, PostContent, PostParentID)

the field PostParentID is a reference to PostID on the same table: if that field is null then is a question, if is filled then is an answer to the question with id PostParentID.

with the above structure a single table is required to store votes.

there is the potential issue that a post may have an 'answer' as a parent because there are no easy constraints (a trigger is not a constraint and i don't like triggers anyway ^^) to avoid such association but it should be a very limited issue.

with this approach it can be expensive to make statistics because a count of the votes cast on answers require a join with the Post table; having votes on answers and questions in different tables would make such jobs easier and possibly more performing.

it is a winning solution imho if the major task is to access the single question (get votes/counts for a single question/answer) because the logic is simpler, with fewer objects involved and to maintain (I always suppose to interact using stored procedures) and it is the same for answers and questions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.