Geek Answers Handbook

How to save language proficiency in a database

I think I face a problem when many of you were before. I have a registration form in which the user can select any language on the planet, and then select their skill level for the corresponding language from selectbox.

So for example:

Language1: German
Skill: Fluent
Language2: English
Skill: Basic

I think the best way to store these values ​​in a MySQL database.

I thought of two ways. The first way: creating a column for each language and assigning it a skill value.

--------------------------------------------------
| UserID |   language_en    |  language_ge       |
--------------------------------------------------
|   22   |       1          |         4          |
--------------------------------------------------
|   23   |       3          |         4          |
--------------------------------------------------

Thus, the language is always the name of the column, and the number represents the skill level (1. Basic, 2. Average ...)

, , . 50 . , 50 , script , .

: . :

----------------------------------
| UserID |       languages       |  
----------------------------------
|   22   | "ge"=>"4", "en"=>"1"  |   
----------------------------------

, ID 22 4 1 . , 50 ( ), . , , 1 2 , - , , .

, ​​. , - , ?

.

+4
1

:

Table: Language
+------------+-------------------+--------------+
| LanguageID | LanguageNameShort | LanguageName |
+------------+-------------------+--------------+
|          1 | en                | English      |
|          2 | de                | German       |
+------------+-------------------+--------------+

, :

Table: LanguageLink
+--------+------------+--------------+
| UserID | LanguageID | SkillLevelID |
+--------+------------+--------------+
|     22 |          1 |            1 |
|     22 |          2 |            4 |
|     23 |          1 |            3 |
|     23 |          2 |            4 |
+--------+------------+--------------+

. , , .

. lanugage, :

SELECT
    LanguageLink.UserID,
    LanguageLink.SkillLevelID,
    Language.LanguageNameShort
FROM
    LanguageLink,
    Language
WHERE
    LanguageLink.UserID = 22
    AND LanguageLink.LanguageID = Language.LanguageID

, :

Table: Skill
+--------------+-----------+
| SkillLevelID | SkillName |
+--------------+-----------+
|            1 | bad       |
|            2 | mediocre  |
|            3 | good      |
|            4 | perfect   |
+--------------+-----------+

, , . , .

+8

More articles:


All Articles