Database Design for a Multiplayer/Single Quiz game

Question

I saw a lot of questions here but no one fits with my problem. I'm trying to create an ER model scalable, and if I want to add more data don't break almost anything, so what I've trying to create is :

There are 2 types of users, let's say Admin and Worker, and they have different roles.

Admin can do a CRUD of questions, and also can create a room where the users can join to play together (this is just a name, something like Kahoot! does) but maybe is a good idea to create more attributes inside of it like, WHO is playing in this room, POINTS for everyone but let's talk it afterwards when I show you the design.

Ok the thing is, on my design I have :

Table User which contains :

_id
username
password
date_creation

This is a default one, but then I'm wondering how do I define the Role if it's an Admin or a Worker, something like isAdmin:true and then I check this Bool? Or I can create another table that is Role and connect it to User table?

But maybe I have to create a table for both, I mean there's an Admin which has a password, and some functionalities and then ther'es the user Worker which has another password and another functionalities.

Then I'd like to have the Question table where contains :

_id
question_name
answers[1,2,3,4]
correctAnswer or answers because it can be multi option chooser
topic
isExamQuestion
dificulty

Then the Room table should contains :

_id
name
capacity
type (game can be as a group or solo) that's why this attribute
exam (This should be a flag to know if this question is for an exam or not (It can be for EXAM or PRACTISE)
ranking (This is the top X from 1 to X)
don't think if I have to add the winner here because if I get the position 0 from ranking I get the winner...

There's a table named Topic as well, if my question have a topic then I can select the question by Topic. An example of Topic should be Math so user can do only exams or do tests with math questions.

_id
Name
Questions[...]
Then I have to store like a historic about what are the questions worker has answered correct and what did not, to make some statistics, but I need to store some historicals for Admin to see in this topic the average that Workers have failed more is : Question23 (for instance) something like that.

What I'm missing, could you try to help me to figure it out how to make this design better?

NOTE : I'm using Spring for server side, Angular for Frontend stuff, and Android for App, I can change anything to work faster/better with this database though.

EDIT

There's the flow of the game if you need more details and if I'm explainted wrong .

Admin flow

Create questions (with different kinds of answers like True/false, with a checkbos (single and multianswer), text, etc...)
Create a "game" where workers can join (This is mostly programming stuff) but it should be a room with attributes there, like id of the room, maxNumber, type (exam), and store historicals, theres also a type of game (for instance, images, videos, etc..)
See statistics about Workers it means see how many answers they answered correct, failed, see per topic (This is like joins and stuff, but the design has to be good done)
See historic of the exams that he did before with all of the information (participant, score, time, stuff)

And the Worker flow is

He can practise means that he's answering questions randomly or by topic (every single answer should be saved for statistics and to avoid repeat the ones he respons correct), also he can do exams (not multiplayer) just an option that Admin can check if the question is part of an exam or not.

And then the room stuff, he can join with the Id.

If you need further clarification let me know and I'll reply you as soon as possible.

What does "better" look like? Faster? Easier to code with? Easier to change? — Neville Kuyt, Jan 21 '19 at 10:27
Hello @NevilleKuyt thanks for your interest, yes from the start I have something similar to the [MaximFedorov answer](https://stackoverflow.com/questions/54181058/database-design-for-a-multiplayer-single-quiz-game/54278049#54278049) and I'm looking to another possible solution that could me more scalable means that if I want to add more stuff I do not need to change lot of tables. — Skizo-ozᴉʞS ツ, Jan 21 '19 at 11:18

Maksym Fedorov · Accepted Answer · 2019-01-21T12:15:36.863

11

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>

edited Jan 21 '19 at 12:15

answered Jan 20 '19 at 15:38

Maksym Fedorov

6,383
2
11
31

Thanks for answering, I'm waiting for more proposals, but I'm wondering for instance how do I get the history for rooms ? – Skizo-ozᴉʞS ツ Jan 21 '19 at 09:41
@Skizo-ozᴉʞS What data should the history contain? – Maksym Fedorov Jan 21 '19 at 10:17
Imagine that you are a teacher and then you do an exam, 10 days ago you want to check it again, so what would you want to see there? Like data, how many students participated, notes, answers for each question by every student, etc... that's what I mean, but I'm wondering if this is something about joins and stuff that database design.... – Skizo-ozᴉʞS ツ Jan 21 '19 at 11:20
@Skizo-ozᴉʞS I attached several queries to the answer – Maksym Fedorov Jan 21 '19 at 12:16
Awesome, let me take a look and go deeper to see if something is missing, thanks for your time Maxim :D – Skizo-ozᴉʞS ツ Jan 21 '19 at 17:30
what do you think with the answer proposed by TiyebM? Makes sense in your diagram? – Skizo-ozᴉʞS ツ Jan 24 '19 at 14:24
If you delete *is_correct* from the answers, and add the correct answer's key to questions, you can use referential integrity constraints to ensure every question has a correct answer. – James K. Lowden Jan 24 '19 at 15:45
@Skizo-ozᴉʞS If you need to keep information about profile and exam you can do it. – Maksym Fedorov Jan 24 '19 at 16:24
@JamesK.Lowden Yes, you are right, but one question might have many right answers. – Maksym Fedorov Jan 24 '19 at 16:26
Maxim, I'm missing something, there's a room which contains an "exam", do I have to modify the table? I mean admin can create a room so then assign to it an exam to show later on. – Skizo-ozᴉʞS ツ Jan 25 '19 at 14:38
@Skizo-ozᴉʞS Well, you should create `exam` table and add a link to an exam in `room` table – Maksym Fedorov Jan 25 '19 at 14:44
And this is a personal question, do you think is missing something in my design? I mean, the one that you did in your answer do you think there's something missing? – Skizo-ozᴉʞS ツ Jan 25 '19 at 14:44
That's the proposal for `type` in question, let's say I can have a questions for : `exam`, `study`. `whatelsemore` – Skizo-ozᴉʞS ツ Jan 25 '19 at 14:45
And now I'm wondering, if I want to do different questions like "test", "true/false", "short answer" how would do this with this design? Could you try to guide me please? (for sure I'll put the bounty on your answer, but those are some missing stuff that I'm wondering) – Skizo-ozᴉʞS ツ Jan 25 '19 at 14:46
@Skizo-ozᴉʞS If you want to do different types of questions you can set them to `type` column (for example like as ENUM) and display different input fields which depends on this type in client side – Maksym Fedorov Jan 25 '19 at 14:54
So from now is ok? I mean I can detect from client side that the question is true false answer and the next one is test one and the next one is a small answer? I mean at the time admin creates the question I can choose for exame if it's a test I can choose 4 answers and put 1 or 2 as a correct, one? right? – Skizo-ozᴉʞS ツ Jan 25 '19 at 15:02
@Skizo-ozᴉʞS yes – Maksym Fedorov Jan 25 '19 at 15:05
Thanks for yout effor man,really,onequestion if I want to add an explanation for each question do i have to put another parameter in the table? or create another table with id name etc? – Skizo-ozᴉʞS ツ Jan 26 '19 at 14:17
@Skizo-ozᴉʞS an explanation of a question might be added to question table – Maksym Fedorov Jan 28 '19 at 07:43
user_in_room and history one doesn't have primary key @MaximFedorov? I'm creating the schema for those and I do not see the primary key I have the option to put : `NOT NULL` `PRIMARY KEY` `UNIQUE` `FOREIGN KEY` and `CHECK` are they `FOREIGN KEY`, right? – Skizo-ozᴉʞS ツ Jan 28 '19 at 15:12
@Skizo-ozᴉʞS user_in_room has user_id and room_id. Those params identify a record. Those params might be the composite primary key. – Maksym Fedorov Jan 29 '19 at 13:33
Is there any way to create this ER and get the sentences SQL to create for postgresql?? Did you create the ER for the answer by the way? – Skizo-ozᴉʞS ツ Jan 29 '19 at 19:21

score 2 · Answer 2 · 2019-01-24T19:32:08.650

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.

Harder. Not too terribly hard. To implement, you'll need to make a whole other table ("Roles") and link it properly, etc. And then, every time you want know the role a user or update the role of a user, you'll have to join the tables together. It's not too hard, just and extra step every time. I'll update my answer. — , Jan 24 '19 at 19:15

TiyebM · Answer 3 · 2020-06-15T15:51:20.400

You need a Profile table with one-to-many with User table, this way if another privelege you want to apply, just add new profile entry:

User table:

Id
Username
Fullname
Profile_id
...

Profile table:

Id
Name
Description

Exam and Question tables are related with many-to-many, to break it you will have a third table Question_Exam:

Question_Exam:

 id_exam
 Id_Question
 Answer(provided)
 Id_user(taking the exam)
 IsCorrect(boolean, to avoid farther queries )
 date

Topic and Question are one-to-many

Question table:

 Id
 Name
 Answer(The correct one)
 Id_topic

The other structure is fine.

Database Design for a Multiplayer/Single Quiz game

EDIT

3 Answers3

Linked