多人/单测验游戏的数据库设计 [英] Database Design for a Multiplayer/Single Quiz game

问题描述

我在这里看到了很多问题，但是没有人适合我的问题。我正在尝试创建可扩展的ER模型，并且如果我想添加更多数据，则几乎不会破坏任何东西，所以我试图创建的是：

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 :

有两种类型的用户，例如Admin和Worker，它们具有不同的角色。

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

Admin可以对问题进行CRUD，也可以在其中创建一个房间用户可以加入一起玩耍（这只是一个名字，就像Kahoot！一样），但是在其中创建更多属性可能是个好主意，例如，WHO在这个房间里玩，每个人都要点，但是之后再说吧

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 :

表用户其中包含：

_id
username
password
date_creation

这是默认值，但是我想知道如何定义角色（如果是管理员或工人），例如isAdmin：true，然后检查此Bool？还是我可以创建另一个角色表并将其连接到用户表？

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?

但是也许我必须为两个表创建一个表，我的意思是有一个Admin密码，以及一些功能，然后是有另一个密码和其他功能的用户Worker。

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.

然后，我想在Question表中包含：

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

然后房间表应包含：

_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?

注意：我在服务器端使用Spring，在前端使用Angular，在Android应用中使用Android，因此我可以更改任何内容以更快/更好地使用此数据库

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 .

管理流程

1. 创建问题（具有不同的答案，例如正确/错误，带有检查框（单答案和多答案），文本等...）


2. 创建一个游戏，工人可以加入该游戏（这主要是编程方面的东西），但应该是一个具有属性的房间，例如房间的ID，maxNumber，类型（考试）和存储历史记录，还有一种游戏类型（例如图像，视频等）。


3. 查看有关Workers的统计信息，这意味着查看他们回答了正确，失败的答案数量，请参阅每个主题（这就像联接和填充，但是设计必须做得很好）


4. 使用所有信息（参与者，分数，时间，填充）查看他之前参加的考试的历史记录

1. Create questions (with different kinds of answers like True/false, with a checkbos (single and multianswer), text, etc...)
2. 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..)
3. 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)
4. See historic of the exams that he did before with all of the information (participant, score, time, stuff)

工人流是

他可以练习意味着他正在回答随机或按主题提问（应保存每个答案以供统计，并避免重复回答正确的问题），他也可以进行考试（不是多人游戏），只是管理员可以检查问题是否属于考试或

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.

然后是房间的东西，他可以加入ID。

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):

• 用户模块，其中包含用户数据并实现身份验证和用户操作授权


• 调查问卷模块，其中包括对问题和答案的管理


• 调查问卷历史记录模块，其中包含每个用户的历史记录

这些模块的数据库设计如下所示

Database design of those modules can look as follows

用户模块：

角色-包含系统中的用户角色

role - contains user roles in the system

• id-角色的唯一标识符


• 名称-角色名称，例如admin，worker等。

用户-包含用户和有关角色的信息向他们致敬

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

• id-用户的唯一标识符


• 用户名


• 密码


• role_id-角色已分配给用户的标识符

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

问题模块：

主题-包含问题主题

• id-主题的唯一标识符


• name-主题的名称

问题-包含问题

• id-问题的唯一标识符


• topic_id-问题的主题标识符


• 文本-问题的内容


• is_exam_question-是否考试题


• 类型-答案类型（布尔值，复选框等）


• 难度

• 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-的唯一标识符答案


• question_id-包含答案的问题的标识符


• 文本-问题的内容


• is_correct-表示答案是对还是错的标志

• 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-朗姆酒的唯一标识符


• 名称-朗姆酒的名称


• 能力-可容纳的最大工人人数可以加入房间


• 类型-房间类型：团体，独奏等。


• learing_type-房间类型：考试，练习等。

• 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 -包含有关加入会议室的用户的信息

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

• user_id-加入会议室的用户的标识符


• room_id-会议室的标识符


• 得分-房间中用户的当前得分

历史记录模块：

user_question_history -包含有关用户回答的问题的信息

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

• user_id-用户的标识符


• room_id-用户回答问题的房间的标识符


• question_id-用户回答的问题的标识符


• 得分-按问题的用户评分

• 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 -包含有关用户选择的答案的信息

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

• user_id-用户标识符


• room_id-用户回答问题的房间的标识符


• question_id-用户回答的问题的标识符


• answer_id-被用户选择的答案的标识符

• 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>

这篇关于多人/单测验游戏的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！