Menu
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.
There's the flow of the game if you need more details and if I'm explainted wrong .
Admin flow
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.
358
Then massive multiplayer online (MMO) games emerged and changed everything. Thousands of players found themselves in the same game universes, competing over resources, negotiating, trading and fighting. To make such games possible, a database structure was needed that could store all the relevant information.
To make such games possible, a database structure was needed that could store all the relevant information. In this article, we will design a model that incorporates the most common elements found in MMO games. We will discuss how to use it, its limitations, and its possible improvements.
It contains the most important game features: locations, structures, resources, research, and units. It also relates them, lets us define prerequisites in the database, and stores most of the game logic in the database as well. After these tables are populated, most of the game logic is defined and we wouldn’t expect new values to be added.
The game is played via website, while the database is used to store information about players’ actions and success rates. Our data model should cover all four previously-mentioned board games. It would also be nice if it was flexible enough to cover additional games. Our model has to be able to handle many board games.
In fact, your system has three logical parts (modules):
Database design of those modules can look as follows
USER MODULE:
role - contains user roles in the system
user - contains users and information about roles were assigned to them
QUESTIONNAIRES MODULE:
topic - contains question themes
question - contains questions
answer - contains all answers of questions
room - contains information about rooms
user_in_room - contains information about users which were joined to the room
HISTORY MODULE:
user_question_history - contains information about questions which were answered by the user
user_answer_history - contains information about answers which were chosen by 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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
