-- ============================================================ -- Live Quiz · MySQL / MariaDB schema -- Run this once against your cPanel database. -- Keep this file in your repo — it is the definition of your -- data model, and it belongs under version control like code. -- ============================================================ SET NAMES utf8mb4; -- utf8mb4 (not plain utf8) is used everywhere below. MySQL's -- historical "utf8" only stores 3-byte characters, which breaks -- on emoji and some Indic text. Trainee nicknames will contain -- both. Get this right now; migrating later is painful. -- ------------------------------------------------------------ -- 1. QUIZZES — a reusable set of questions you author once -- ------------------------------------------------------------ CREATE TABLE quizzes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 2. QUESTIONS — belong to one quiz, displayed in sort_order -- ------------------------------------------------------------ CREATE TABLE questions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quiz_id INT UNSIGNED NOT NULL, sort_order SMALLINT UNSIGNED NOT NULL, question_text TEXT NOT NULL, time_limit_sec SMALLINT UNSIGNED NOT NULL DEFAULT 20, max_points SMALLINT UNSIGNED NOT NULL DEFAULT 1000, CONSTRAINT fk_questions_quiz FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE, -- Two questions cannot occupy the same slot in the same quiz. UNIQUE KEY uq_quiz_order (quiz_id, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ON DELETE CASCADE: deleting a quiz deletes its questions -- automatically. Without it you accumulate orphan rows — -- questions pointing at a quiz that no longer exists. -- ------------------------------------------------------------ -- 3. ANSWER_OPTIONS — the choices shown for each question -- (named answer_options, not options: OPTION is a reserved -- word in MySQL and will bite you eventually) -- ------------------------------------------------------------ CREATE TABLE answer_options ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, question_id INT UNSIGNED NOT NULL, label CHAR(1) NOT NULL, -- 'A', 'B', 'C', 'D' option_text VARCHAR(300) NOT NULL, is_correct BOOLEAN NOT NULL DEFAULT 0, CONSTRAINT fk_options_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE, UNIQUE KEY uq_question_label (question_id, label) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 4. SESSIONS — one live run of a quiz with a real audience. -- A quiz is the script; a session is the performance. -- ------------------------------------------------------------ CREATE TABLE sessions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quiz_id INT UNSIGNED NOT NULL, join_code VARCHAR(8) NOT NULL, -- goes inside the QR code status ENUM('lobby','running','ended') NOT NULL DEFAULT 'lobby', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, started_at DATETIME NULL, ended_at DATETIME NULL, CONSTRAINT fk_sessions_quiz FOREIGN KEY (quiz_id) REFERENCES quizzes(id), -- Two live sessions must never share a join code, or trainees -- end up in the wrong room. The database guarantees it. UNIQUE KEY uq_join_code (join_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Note: no ON DELETE CASCADE here. Deleting a quiz should NOT -- silently erase the record of sessions you already ran. MySQL -- will block the delete instead — which is the safer default -- for historical data. -- ------------------------------------------------------------ -- 5. PARTICIPANTS — one row per trainee per session -- ------------------------------------------------------------ CREATE TABLE participants ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, session_id INT UNSIGNED NOT NULL, nickname VARCHAR(40) NOT NULL, score INT UNSIGNED NOT NULL DEFAULT 0, joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_participants_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE, -- No two people in the same room can pick the same nickname. -- The leaderboard would be unreadable otherwise. UNIQUE KEY uq_session_nickname (session_id, nickname) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 'score' is denormalised on purpose: it could be recalculated -- by summing the answers table every time, but the leaderboard -- is read constantly during a session. Storing the running -- total is a deliberate trade — slight redundancy for speed. -- ------------------------------------------------------------ -- 6. ANSWERS — the heart of the system. -- This table is where requirement 5 (who answered first) -- actually lives. -- ------------------------------------------------------------ CREATE TABLE answers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, session_id INT UNSIGNED NOT NULL, question_id INT UNSIGNED NOT NULL, participant_id INT UNSIGNED NOT NULL, option_id INT UNSIGNED NULL, -- NULL = ran out of time latency_ms INT UNSIGNED NULL, -- arrival time minus broadcast time is_correct BOOLEAN NOT NULL DEFAULT 0, points_awarded SMALLINT UNSIGNED NOT NULL DEFAULT 0, answer_rank SMALLINT UNSIGNED NULL, -- 1 = first correct answer -- ('rank' is reserved in MySQL 8) answered_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_answers_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE, CONSTRAINT fk_answers_question FOREIGN KEY (question_id) REFERENCES questions(id), CONSTRAINT fk_answers_participant FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE, -- THE important constraint: one answer per person per question. -- Your application code will check this too. This is the -- backstop for when the code is wrong. UNIQUE KEY uq_one_answer (session_id, question_id, participant_id), -- Index for the query you run after every single question: -- "give me all answers to this question in this session". KEY idx_session_question (session_id, question_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- DATETIME(3) stores milliseconds. Plain DATETIME rounds to the -- second, which is useless when twelve people answer inside the -- same second and you have to rank them. -- ============================================================ -- SAMPLE DATA — one quiz, two questions, so you can see the -- shape of real rows. Delete once you have the admin panel. -- ============================================================ INSERT INTO quizzes (title) VALUES ('Networking Fundamentals'); INSERT INTO questions (quiz_id, sort_order, question_text, time_limit_sec) VALUES (1, 1, 'Which OSI layer does a switch primarily operate at?', 15), (1, 2, 'What is the default administrative distance of OSPF on Cisco IOS?', 20); INSERT INTO answer_options (question_id, label, option_text, is_correct) VALUES (1, 'A', 'Layer 1 - Physical', 0), (1, 'B', 'Layer 2 - Data Link', 1), (1, 'C', 'Layer 3 - Network', 0), (1, 'D', 'Layer 4 - Transport', 0), (2, 'A', '90', 0), (2, 'B', '100', 0), (2, 'C', '110', 1), (2, 'D', '120', 0); -- ============================================================ -- TRY THESE after loading, to get a feel for SQL. -- Run them in phpMyAdmin's SQL tab. -- ============================================================ -- Every question with its options, in display order: -- -- SELECT q.sort_order, q.question_text, o.label, o.option_text, o.is_correct -- FROM questions q -- JOIN answer_options o ON o.question_id = q.id -- WHERE q.quiz_id = 1 -- ORDER BY q.sort_order, o.label; -- -- That JOIN is the price of normalising options into their own -- table — and it is a price worth paying. -- The leaderboard query you will use in Phase 3: -- -- SELECT nickname, score -- FROM participants -- WHERE session_id = 1 -- ORDER BY score DESC, joined_at ASC -- LIMIT 10;