Database Design for a Multiplayer/Single Quiz game

In fact, your system has three logical parts (modules):

• users module that contains user data and implements authentication and the authorization of user actions
• questionnaires module that includes management of questions and answer
• questionnaires history module that contains history by each user

Database design of those modules can look as follows

USER MODULE:

role - contains user roles in the system

• id - unique identifier of the role
• name - the role name, for example, admin, worker, etc.

user - contains users and information about roles were assigned to them

• id - unique identifier of the user
• username
• password
• role_id - identifier the role was assigned to the user

QUESTIONNAIRES MODULE:

topic - contains question themes

• id - unique identifier of the theme
• name - a name of the theme

question - contains questions

• id - unique identifier of the question
• topic_id - topic identifier of the question
• text - content of the question
• is_exam_question - exam question or not
• type - type of answers (boolean, checkboxes or etc.)
• difficulty

answer - contains all answers of questions

• id - unique identifier of the answer
• question_id - identifier of the question that contains the answer
• text - content of the question
• is_correct - flag that means the answer is true or false

room - contains information about rooms

• id - unique identifier of the rum
• name - name of the rum
• capacity - the maximum number of workers which can join to the room
• type - room type: group, solo or etc.
• learing_type - room type: exam, practice or etc.

user_in_room - contains information about users which were joined to the room

• user_id - identifier of the user that was joined to the room
• room_id - identifier of the room
• score - current score of the user in the room

HISTORY MODULE:

user_question_history - contains information about questions which were answered by the user

• user_id - identifier of the user
• room_id - identifier of the room where the user answered questions
• question_id - identifier of the question that was answered by the user
• score - user score by the question

user_answer_history - contains information about answers which were chosen by the user

• user_id - identifier of the user
• room_id - identifier of the room where the user answered questions
• question_id - identifier of the question that was answered by the user
• answer_id - identifier of the answer that was chosen the user

Usage of this schema gives the ability to build different reports. For example, you can display the result of all users by room

SELECT r.id,
    r.name,
    u.username,
    ur.score
FROM room as r
LEFT JOIN user_in_room as ur ON ur.room_id = r.id
LEFT JOIN user as u ON u.id = ur.user_id
WHERE r.id = <id>

Or you can see detail information about answers of the user

SELECT 
    q.text,
    a.text
FROM user_in_room as ur ON ur.room_id = r.id
LEFT JOIN user_question_history as uqh ON ugh.user_id = ur.user_id AND ugh.root_id = ur.room_id
LEFT JOIN question as q ON q.id = ugh.question_id
LEFT JOIN user_answer_history as uah ON uah.user_id = ugh.user_id AND uah.room_id = ugh.room_id AND uah.question_id = ugh.question_id
LEFT JOIN answer as a ON a.id = uah.answer_id
WHERE ur.room_id = <id> AND ur.user_id = <id>

Honestly, if you're certain you'll only have to possible types--both now and in the future--a simple bool isAdmin in your user table will work nicely. Everything else the admin does can be handled from the programming side. No need to clog up your db.

That said, if there's even a small chance you will have other types of users in the future, Maxim's answer is the way to go. That way, adding another role such as "Editor," for instance, is a simple as adding a record to the "Role" table. In fact, adding 1000 new types users is as simple as adding records to your "Role" table. Because you already have a table that you look to for the role, your code doesn't have to worry about all the possible types of users (which is a pain if you have a lot of them), only the ones it needs. The db is handling the rest.

One drawback to Maxim's answer is that it takes more work to implement in the db, and more work to view/update the role of the user.

To implement in the db, you need to create a whole extra table and make sure it's linked properly. This isn't hard, but, especially if you're new to dbs, is extra work that might not be worth it to you. This also means, from a maintenance side, that you have another table to keep tabs on. Not a huge deal, but, again, something to think about.

From the code side, this creates extra chores as well. For one, the user type is no longer directly in your users table--an ID is. This means when you want to know the name of the user type, you will have to either a) query the db for that user type based on the ID you have, or b) join the 2 tables, which can be tricky at times. Updating the role also has similar chores.

Again, these aren't huge problems, just things to think about. It might not be worth the extra work if you will only have two possible options.

So, in short, a boolean will work and is simple to implement, but is not scalable. Maxim's answer is quite scalable and makes future expansion easier, but somewhat harder to implement and maintain. You'll have to make the decision what you prefer.