不同语言测验的数据库设计 [英] database design for quiz with different languages
问题描述
我正在尝试进行一次支持多种语言的测验.我已经为测验和用户结果制作了数据库架构，但是不确定如何以良好的方式实现对不同语言的支持.
这是现在的样子(随意指出设计错误):
* User:
- id PK
- name
- email
- password
* Quiz
- id PK
- slug
- title
* Question
- id PK
- quiz_id FK
- question
- image
- message
* Option
- id PK
- question_id FK
- option
- is_right
Relation between user and quiz to store results:
* Result
- id PK
- user_id FK
- quiz_id FK
* Result Details
- id PK
- result_id FK
- question_id FK
- option_id FK (The option user choose)
- is_right
数据库设计相同，但表之间的连接清晰明了:
我的第一个想法是在测验表中创建一个母语表(语言表有许多测验):
* Language:
- id PK
- language_title
* Quiz
- id PK
- language_id FK
- slug
- title
但这只会创建一个语言类别，管理员将不得不为另一种语言创建一个全新的测验，而不仅仅是添加新的问题文本和选项文本.
我如何设计多种语言的测验数据库，以便只有带有文本(标题，消息，问题和选项)的列才需要获得新的记录，而不必创建全新的测验?
数据库设计应遵循更通用的 信息设计模型 >概念信息模型 ，最好采用UML类图的形式(由于其表现力).以下是针对您的问题的概念性信息模型:
为了获得信息设计模型，仍然必须用适当的标准标识符属性和数据类型来丰富这种模型.通过消除关联和组成(将它们替换为参考属性)，我们获得了以下OO类模型，该模型可用作编码Java/C#/PHP/etc的基础.课程:
注意，我们在此OO类模型中添加了对多语言测验的支持，方法是添加一个IsoLanguageCode枚举和一个TextItem类，该类具有由文本项ID和语言代码(由测验组成)组成的两部分主键，问题和答案选项使用文本项ID来引用用作标题，问题文本和答案文本的文本项.还请注意，Quiz类具有一个派生属性\availableLanguages，可以在查询的帮助下计算派生属性\availableLanguages，对于所有语言而言，所有测验问题的文本项及其所有答案选项均可用. >
可以通过将引用属性替换为相应的外键属性来从此类OO类模型派生SQL数据库设计模型:
I'm trying to make a quiz that supports multiple languages. I have made the database schema for the quiz and user results but not sure how to implement support for different languages in a good way.
This what it looks like right now (feel free to point out design mistakes):
* User:
- id PK
- name
- email
- password
* Quiz
- id PK
- slug
- title
* Question
- id PK
- quiz_id FK
- question
- image
- message
* Option
- id PK
- question_id FK
- option
- is_right
Relation between user and quiz to store results:
* Result
- id PK
- user_id FK
- quiz_id FK
* Result Details
- id PK
- result_id FK
- question_id FK
- option_id FK (The option user choose)
- is_right
Same database design but clear connections between tables:
My first thought was to make a parent language table to the quiz table (language table has many quizzes):
* Language:
- id PK
- language_title
* Quiz
- id PK
- language_id FK
- slug
- title
But this would only create a language category and the administrator would have to create a whole new quiz for a different language and not just add new question text and option text.
How do I design the quiz database for multiple languages so only the columns with text (title, message, question and option) has to get a new record instead of having to create a whole new quiz?
Database design should follow from a more general information design model derived from a conceptual information model, preferably in the form of UML Class Diagrams (because of their expressivity). The following is a conceptual information model for your problem:
Such a model still has to be enriched with suitable standard identifier attributes and data types for obtaining an information design model. By eliminating the associations and compositions (replacing them with reference properties), we obtain the following OO class model, which can be used as the basis for coding Java/C#/PHP/etc. classes:
Notice that we have added support for multilingual quizzes in this OO class model by adding an IsoLanguageCode enumeration and a TextItem class with a two-part primary key consisting of a text item ID and a language code such that quizzes, questions and answer options use a text item ID for referencing the text items used as their title, question text and answer text. Notice also that the Quiz class has a derived property \availableLanguages that can be computed with the help of a query retrieving all languages for which text items for all questions of a quiz, and all their answer options, are available.
An SQL database design model can be derived from such an OO class model by replacing the reference properties with corresponding foreign key attributes:
这篇关于不同语言测验的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
