Database design for multi foreign keys 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 :)
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.