How do I create an effective mysql database design, where it stores different values for multiple users?

34
February 11, 2019, at 9:10 PM

I am trying to make a website, and I need to make a system where it shows random questions, and if the user answers correctly, it exludes the question from the random print.
I need a table where one of the columns is the question number, and one is a boolean, indicating whether or not it has already been answered correctly.
But I cannot create a table for each user, because it would take too much space. Is there any good ways to create a database in such a way it can manage all users at once?

I am really lost in what I should do, so I couldn't really try anything.

Answer 1

The canonical way to do this is to add a user field to the table, that is joined by a foreign key conctraint to the users' table primary key.

This field needs a (non unique) index and will work as a selector to filter only those questions, that are applicable to a special user.

In your case, using a single key over the user field and the answered field would create a perfectaccess path for queries with a WHERE clause along the lines of WHERE user=? AND answered=0

Answer 2

I think the best option to resolve this problems is to create 3 distinct tables;

1º Table -> Users, which will store a PRIMARY_KEY as user_id (or whatever name you will give).
2º Table -> Questions, also will have a PRIMARY KEY and a VARCHAR with the text
3º table -> UserQuestion, This table will be a mix of both tables before that will be empty at the begining, but will eventually generate...
----> this table will relate the user_id with question_id, so you will be able to exclude the questions that will be on this table.

READ ALSO
Conditional Column in Mysql

Conditional Column in Mysql

I have an orders table which looks like this:

41
What is the difference between values 1 and 4096 of output_buffering in php.ini

What is the difference between values 1 and 4096 of output_buffering in php.ini

What does exactly the value of output_buffering ini variable mean in phpini? On our older server, it was set to 1

56
How to replace javascript variables with greasemonkey? [on hold]

How to replace javascript variables with greasemonkey? [on hold]

I want to change a variable in javascript with greasemonkey

36
Nodemon not working after updating to version 1.18.10

Nodemon not working after updating to version 1.18.10

nodemon was working perfectly in my mac previously, I updated it to the latest version and when I try to start it, I keep getting the below error

90