I'm trying to do something like a "decision tree" in my BBDD. The objective is kind of the questions of Google, you have a question with three answers and each of the answers have assigned a different question.
Well, I know I need recursion to do that. So far I've tried these two data models.
CREATE TABLE Level (
question_id SERIAL PRIMARY KEY,
question_description varchar (100) not null,
level smallint not null
);
CREATE TABLE Response (
response_id SERIAL PRIMARY KEY,
response_body varchar (25) not null,
principal boolean not null,
level int not null,
parent_response int,
FOREIGN KEY (parent_response) REFERENCES Response (response_id),
FOREIGN KEY (level) REFERENCES Level (question_id) ON DELETE CASCADE
);
And, the second data model it's for this question, trying to pass it to SQL.
CREATE TABLE question (
id_question SERIAL PRIMARY KEY,
question_text VARCHAR(255)
);
CREATE TABLE responses (
id_response SERIAL PRIMARY KEY,
response_text VARCHAR(255),
question int,
FOREIGN KEY (question) REFERENCES question(id_question)
)
Neither of these worked correctly because I need to know which question comes after each answer.
I have outlined for a better understanding of the problem.
I want to achieve this
