What are the potential pitfalls of using a dynamic number of columns in a MySQL table for storing answers in a quiz website?

Using a dynamic number of columns in a MySQL table for storing answers in a quiz website can lead to difficulties in querying and managing the data. It is recommended to use a separate table to store the answers, with each answer linked to the corresponding question using a foreign key. This approach allows for easier querying and scalability as new questions can be added without altering the table structure. 

// Create a separate table to store answers
CREATE TABLE answers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT,
    answer TEXT,
    FOREIGN KEY (question_id) REFERENCES questions(id)
);

// Inserting answers into the answers table
INSERT INTO answers (question_id, answer) VALUES (1, 'Option A');
INSERT INTO answers (question_id, answer) VALUES (1, 'Option B');
INSERT INTO answers (question_id, answer) VALUES (2, 'Option C');

Keywords

MySQL dynamic columns quiz website data modeling scalability

Related Questions