Database Design Of A Multiplayer Game

Database Design for a Multiplayer/Single Quiz game

kendra.sauer · saved on 4 months ago

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.

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 enter image description here

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>

Posts saved by kendra.sauer from S.O.

Symfony and Doctrine: cross database relations
I have two entities Entity1 and Entity2 with a OneToMany relation, but they live in two MySQL databases. How can I implement those entities with their relation...

bturcotte · saved on 5 months ago
Doctrine2, Symfony2 - oneToOne with multiple entities?
I have a user entity, and multiple profile entities. What I'm trying to do is dynamically create a oneToOne relationship based on the user role. So picture my 3...

tkilback · saved on 6 months ago
Database Design for a Multiplayer/Single Quiz game
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 anyt...

kendra.sauer · saved on 4 months ago
How to represent multiple relation between two entity sets in ERD?
I'm trying to build ERD for an airplane company while study. I made two entity sets, #code# and #code#. As you see, #code# could be a destination of #code# and...

wfeest · saved on 3 months ago
Does This ER Schema Make Sense
here is an updated version Given the following requirements, We have three distinct types of workers: receptionists, nurses, and doctors. Any of the workers ca...

joany.ankunding · saved on 3 months ago
JPA Multiple Relationships - One Database Column
hkling · saved on 1 week ago