How to save language skill levels correctly in a database

Question

I think I am before a problem where many of you were before. I have a registration form where a user can pick any language of the planet and then pick his skill level for the respective language from a selectbox.

So, for example:

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

I'm thinking what's the best way to store these values in a MySQL database.

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

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

So the language is always the column's name and the number represents the skill level (1. Basic, 2. Average ... )

I believe this is a nice way to work with these things and it is also pretty fast. The problem starts when there are 50 languages or more. It doesn't sound like a good idea to make 50 columns where the script always have to check them all if a user have any skill in that language.

Second way: inserting an array in one of the table's column. The table will look like this:

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

This way the user with ID 22 has skill level 4 for Germany and skill level 1 for English. This is fine because we don't need to check 50 additional columns (or even more) but it's not the right way in my eyes anyway. We have to parse a lot of results and find a user with, for example, has level 1 for Germany and level 2 for Spanish without looking for the English skill level - it will take the server's a longer time and when bigger data comes we are in trouble.

I bet many of you have experienced this kind of issue. Please, can someone advise me how to sort this out?

Thanks a lot.

Answer 1

I'd advise you to have a separate table with all the languages:

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

And another table to link the users to the languages:

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

This is the normalised way to represent that kind of relations in a DB. All data is easily searchable and you don't have to change the DB scheme if you add a language.

To render a user's languages you could use a query like that. It will give you a row per lanugage a user speaks:

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

If you want to go further, you could create another table fo the skill level:

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

What I've done here is called Database normalization. I'd recommend reading about it, it may help you design further databases.