如何设计多语言测验数据库

CREATE TABLE quizzes (
    quiz_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    description TEXT
);

CREATE TABLE questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    quiz_id INT,
    question_text TEXT,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(quiz_id)
);

CREATE TABLE answers (
    answer_id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT,
    answer_text TEXT,
    is_correct BOOLEAN,
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);

CREATE TABLE questions_i18n (
    question_id INT,
    language_code CHAR(2),
    question_text TEXT,
    PRIMARY KEY (question_id, language_code),
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);

CREATE TABLE questions_en (
    question_id INT PRIMARY KEY,
    question_text TEXT,
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);

CREATE TABLE questions_zh (
    question_id INT PRIMARY KEY,
    question_text TEXT,
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);

-- 查询特定语言的题目
SELECT q.quiz_id, qi.question_text
FROM quizzes q
JOIN questions_i18n qi ON q.quiz_id = qi.quiz_id
WHERE qi.language_code = 'zh' AND q.quiz_id = 1;