How to link entities: question, answer and comments to the answer like qna.habr.com?

Question

MySQL

How to optimally connect entities: question, answer, comments to the answer like qna.habr.com?

I understand that 3 models are needed: question, answer, comment.

A question model can have many answers (a one-to-many relationship).

What about the comment model? To which model should it be tied to a question or an answer? And is it possible to call data from three tables in one query?

What are the options for solving such a problem and what is the best way to proceed in this case?

Answer 1

There is such an option:
link comment through "one to many" with both question and answer, while specifying foreign keys as NULLable.
Example:

CREATE TABLE comment (
CId INT PRIMARY KEY AUTO_INCREMENT,
fQId int(11) NULL,
fAId int(11) NULL,
FOREIGN KEY (fQId)  REFERENCES question (QId),
FOREIGN KEY (fAId)  REFERENCES answer (AId),
)

If the comment is related to a question, fAId will be NULL, if the comment is related to an answer, fQId will be NULL.
Data can be called using joins, for example:

select comment from question join answer on QId=fQId join comment on AId=fQAId where QId='value'

The code above will, in theory, select the requested question, the answers to it, and the comments for each answer.

Answer 2

Option 1:
Make different entities "comment on question" and "comment on answer"
Option 2:
Make "comment" but don't make FK, and determine what this comment is for by flag.
Option 3:
Make the question a single entity-document, within which there will be a field with answers, a field with comments on the question, and the answers will have their own comments.
But it seems that it will not be very convenient to do it on mysql, and it will be quite expensive to get all the user's comments or answers

And is it possible to call data from three tables in one query?

Can. Why not?

Answer 3

I'll add Vasily Bannikov to the answer Option 4:
Store questions, answers and comments in one table, separated by type, of course.
Well, a link to the parent, as usual.