MySQL / PHP Quiz Engine - preventing multiple attempts

Question

Now I am writing a web application for a quiz using PHP and MySQL. I do not want to bore you with the details of this, especially here that (I think) you need to know.

Questions have multiple choices and can be stored in a simple table with several columns:

• ID: question number (primary index)
• Category: the category this issue applies to (e.g. animals, vegetables, minerals).
• Text: question (for example, what is 1 + 1?)
• Answer 1: Possible answer (e.g. 2)
• Answer 2: Possible answer (e.g. 3)
• Answer3: Possible answer (e.g. 4)
• CorrectAnswer: correct answer to the question (1, 2 or 3 (in this case 1))

Users can register by creating a username and password, and then try to ask questions from the categories.

The problem is that the questions I write are for trying more than once. However, users need to be given detailed information about their progress. The FIRST attempt in the question matters, and contributes to the general user question responsible for the first time. So I need to keep track of how many times the question has been posed.

Since the application is designed to be flexible, I would like to have support for many hundreds of users trying to answer many thousands of questions. Thus, an attempt to integrate this information into a user table or a question table seems impossible. The way I would like to approach this problem is to create a new table for each user when they subscribed, with different columns.

• Table name: individual user table (e.g. TableForUser51204)
• QuestionID: The identifier of the question that the user tried to complete.
• CorrectFirstTime: A boolean indicating the first question was answered.
• Correct: the number of answers to the question is correct.
• False: the number of answers to the question is incorrect.

So, I think I would like to ask if the organization of the database in this way is a reasonable thing. Is there a better approach than creating a new table for each user? How much can this interfere with work if 500 users and 2000 questions speak?

Thanks.

Answer 1

You do not want to create a new table for each user. Instead, restructure your database.

Usually you have a table for questions, a table for parameters (possibly a Boolean column to indicate the correct answer), a user table and a connection table for users, and parameters for storing user responses, Example of a scheme:

CREATE TABLE `options` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `question_id` int(10) unsigned NOT NULL, `text` varchar(255) NOT NULL, `correct` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `question_id` (`question_id`) ) TYPE=InnoDB; CREATE TABLE `options_users` ( `option_id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `created` timestamp NOT NULL, KEY `option_id` (`option_id`), KEY `user_id` (`user_id`) ) TYPE=InnoDB; CREATE TABLE `questions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `question` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`,`question`) ) TYPE=InnoDB; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(60) NOT NULL, `password` char(40) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) TYPE=InnoDB; ALTER TABLE `options` ADD CONSTRAINT `options_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `options_users` ADD CONSTRAINT `options_users_ibfk_2` FOREIGN KEY (`option_id`) REFERENCES `options` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `options_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; 

These are links to user questions and answers to parameters. I also added the created column to the options_users table so you can see when the user answered the question and tracked their progress over time.