MSSQL/MySQL动态树形问答流程数据库设计方案咨询

Alright, let's tackle this dynamic tree-based Q&A database design that works for both MySQL and MSSQL Server—perfect for mimicking that Urbanclap-style guided flow where each answer leads to the next relevant question. Here's a practical, scalable approach that’s easy to implement and extend:

We’ll use two main tables to model the question-answer tree. This setup keeps things simple while supporting unlimited branching paths.

1. Questions Table (Stores all questions in the flow) #

This table holds every question, including the starting "root" question that kicks off the process.

MySQL Create Statement #

CREATE TABLE Questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    question_text VARCHAR(500) NOT NULL,
    is_root BOOLEAN NOT NULL DEFAULT FALSE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

MSSQL Create Statement #

CREATE TABLE Questions (
    question_id INT IDENTITY(1,1) PRIMARY KEY,
    question_text VARCHAR(500) NOT NULL,
    is_root BIT NOT NULL DEFAULT 0,
    created_at DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    updated_at DATETIME2 NOT NULL DEFAULT GETUTCDATE()
);

-- Add trigger for auto-updating updated_at in MSSQL
CREATE TRIGGER trg_Questions_Update
ON Questions
AFTER UPDATE
AS
BEGIN
    UPDATE Questions
    SET updated_at = GETUTCDATE()
    FROM Questions
    INNER JOIN inserted ON Questions.question_id = inserted.question_id;
END;

2. AnswerOptions Table (Links questions to their answers and next steps) #

This table is where the magic happens—it connects each question to its possible answers, and maps each answer to the next question in the flow (or NULL if the flow ends here).

MySQL Create Statement #

CREATE TABLE AnswerOptions (
    option_id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    option_text VARCHAR(500) NOT NULL,
    next_question_id INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES Questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY (next_question_id) REFERENCES Questions(question_id) ON DELETE SET NULL
) ENGINE=InnoDB;

MSSQL Create Statement #

CREATE TABLE AnswerOptions (
    option_id INT IDENTITY(1,1) PRIMARY KEY,
    question_id INT NOT NULL,
    option_text VARCHAR(500) NOT NULL,
    next_question_id INT NULL,
    created_at DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    updated_at DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    FOREIGN KEY (question_id) REFERENCES Questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY (next_question_id) REFERENCES Questions(question_id) ON DELETE SET NULL
);

-- Trigger for auto-updating updated_at
CREATE TRIGGER trg_AnswerOptions_Update
ON AnswerOptions
AFTER UPDATE
AS
BEGIN
    UPDATE AnswerOptions
    SET updated_at = GETUTCDATE()
    FROM AnswerOptions
    INNER JOIN inserted ON AnswerOptions.option_id = inserted.option_id;
END;

Let’s populate the tables with a sample flow matching your Urbanclap reference:

1. Insert the root question (starting point):

-- MySQL/MSSQL (adjust syntax if needed)
INSERT INTO Questions (question_text, is_root)
VALUES ("What service are you looking for?", 1);

2. Insert follow-up questions:

INSERT INTO Questions (question_text)
VALUES 
("What's your apartment size?"),
("Which appliance needs repair?"),
("How many rooms need cleaning?");

3. Insert answer options linking questions together:

-- Link root question to its answers
INSERT INTO AnswerOptions (question_id, option_text, next_question_id)
VALUES 
(1, "Home Cleaning", 2),
(1, "Appliance Repair", 3);

-- Link "Home Cleaning" question to its answers
INSERT INTO AnswerOptions (question_id, option_text, next_question_id)
VALUES 
(2, "1 BHK", 4),
(2, "2 BHK", 4),
(2, "3+ BHK", 4);

-- Link "Appliance Repair" question to end of flow (next_question_id = NULL)
INSERT INTO AnswerOptions (question_id, option_text, next_question_id)
VALUES 
(3, "Refrigerator", NULL),
(3, "Washing Machine", NULL);

To power your application’s Q&A flow, use these simple queries:

1. Get the starting root question:

SELECT question_id, question_text FROM Questions WHERE is_root = 1;

2. Get all answers for a selected question, plus the next question (if any):

SELECT 
    ao.option_id,
    ao.option_text,
    q_next.question_id AS next_question_id,
    q_next.question_text AS next_question_text
FROM AnswerOptions ao
LEFT JOIN Questions q_next ON ao.next_question_id = q_next.question_id
WHERE ao.question_id = [YOUR_SELECTED_QUESTION_ID];

If next_question_id is NULL, that means the flow ends here—you can trigger a confirmation, form submission, or result display.

• Add Indexes: Speed up queries by adding indexes on question_id and next_question_id in the AnswerOptions table.
• Support Branch Merging: This structure naturally lets multiple answers point to the same next question (like how both 1 BHK and 2 BHK lead to the "number of rooms" question in our example).
• Multi-Language Support: Add a language_code column (e.g., 'en', 'hi') to both tables if you need localized questions/answers.
• Version Control: If you plan to update the flow over time, add a version column to track which questions belong to which flow version—this avoids breaking active user sessions.

内容的提问来源于stack exchange，提问作者Malik Kashmiri