What are some best practices for normalizing a database when storing quiz data in PHP?

When storing quiz data in a database using PHP, it is important to normalize the database to avoid data redundancy and improve data integrity. One way to achieve this is by creating separate tables for entities like quizzes, questions, answers, and user responses, and establishing relationships between them using foreign keys. 

// Example of normalizing quiz data in a MySQL database using PHP

// Create a table for quizzes
CREATE TABLE quizzes (
    id INT PRIMARY KEY,
    title VARCHAR(255)
);

// Create a table for questions
CREATE TABLE questions (
    id INT PRIMARY KEY,
    quiz_id INT,
    question_text TEXT,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id)
);

// Create a table for answers
CREATE TABLE answers (
    id INT PRIMARY KEY,
    question_id INT,
    answer_text TEXT,
    is_correct BOOLEAN,
    FOREIGN KEY (question_id) REFERENCES questions(id)
);

// Create a table for user responses
CREATE TABLE user_responses (
    id INT PRIMARY KEY,
    user_id INT,
    quiz_id INT,
    question_id INT,
    answer_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id),
    FOREIGN KEY (question_id) REFERENCES questions(id),
    FOREIGN KEY (answer_id) REFERENCES answers(id)
);

Keywords

database normalization quiz data PHP SQL queries data integrity

Related Questions