Database Design For Developing 'Quiz' Web Application using PHP and MySQL
Building a quiz application requires more than just creating a form to collect user data; it demands a schema that can handle dynamic content while preserving historical performance. When designing for a system where users answer five multiple-choice questions, the core challenge is separating the static question logic from the dynamic user responses. A flat table approach often leads to rigid queries and makes displaying leaderboard results inefficient.
To begin, you need a robust foundation for your application data. Your users table should handle the personal information you are collecting: last name, first name, email, birthday, and gender. Standard practice dictates that the email field must be unique to ensure each user has a distinct identity, while the created_at timestamp tracks registration history.
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
birthday DATE NULL,
gender ENUM('Male', 'Female', 'Other') NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
The critical architectural decision lies in how you store the quiz questions and answers. If you attempt to hardcode five specific questions directly into a single quiz_results table, your database becomes brittle. Adding a sixth question later would require altering the schema or writing complex application logic. Instead, adopt a normalized design that treats questions as data entities.
Create a questions table where each row represents one of your multiple-choice options. This allows you to scale from five questions to fifty without touching the core application code. You will need columns for the question text, the correct answer key, and perhaps an optional explanation or image reference if needed later.
CREATE TABLE questions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
question_text TEXT NOT NULL,
option_a VARCHAR(255),
option_b VARCHAR(255),
option_c VARCHAR(255),
option_d VARCHAR(255),
correct_option ENUM('a', 'b', 'c', 'd') NOT NULL
);
With the questions defined, you need a quiz_sessions table to capture individual user performance. This is where the complexity often trips up beginners: how do you link a specific user's answers to the dynamic questions? The solution is a junction approach using foreign keys. Your session table should store the user ID, the score achieved, and a timestamp for the attempt. Crucially, it needs a way to retrieve the full details of what was answered.
However, simply storing the score in quiz_sessions loses granular data if you ever want to analyze which specific questions caused users to struggle or if you decide to implement detailed feedback later. A more flexible approach is to create a quiz_answers table that acts as a log for each session. This table links a session_id (from the quiz_sessions table) to a question_id.
CREATE TABLE quiz_sessions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
score TINYINT UNSIGNED NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE quiz_answers (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
session_id INT UNSIGNED NOT NULL,
question_id INT UNSIGNED NOT NULL,
selected_option ENUM('a', 'b', 'c', 'd') NOT NULL,
is_correct BOOLEAN NOT NULL, -- Can be derived from logic or stored for quick lookup
FOREIGN KEY (session_id) REFERENCES quiz_sessions(id) ON DELETE CASCADE,
FOREIGN KEY (question_id) REFERENCES questions(id)
);
This structure elegantly solves the "display results versus previous users" requirement. To generate a leaderboard, you simply aggregate the score and created_at fields from quiz_sessions, joining with the users table to display names. If you need to show who answered correctly or incorrectly for specific questions in a review screen, the quiz_answers table provides the necessary granularity without requiring complex GROUP BY calculations on static data.
One potential pitfall to avoid is storing the entire quiz result as a single JSON blob in a single column. While this saves schema alterations, it makes querying historical performance and generating leaderboards significantly slower and harder to index properly. By keeping questions and answers in separate relational tables, you maintain query efficiency and ensure that your database design supports future feature expansion, such as adding timed quizzes or randomizing question order.