What are the potential pitfalls of storing survey data in a single table with multiple columns in PHP?

Storing survey data in a single table with multiple columns in PHP can lead to a lack of scalability and flexibility. It can make it difficult to add new survey questions or analyze the data effectively. To solve this issue, it is recommended to use a normalized database structure with separate tables for surveys, questions, responses, and respondents. 

// Example of creating separate tables for surveys, questions, responses, and respondents

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

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

// Create a table for responses
CREATE TABLE responses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT,
    respondent_id INT,
    response_text TEXT,
    FOREIGN KEY (question_id) REFERENCES questions(id),
    FOREIGN KEY (respondent_id) REFERENCES respondents(id)
);

// Create a table for respondents
CREATE TABLE respondents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

Keywords

Normalization database design SQL injection data redundancy performance issues

Related Questions