2

We currently use a translation table which looks like this:

|  id | type  | name     | lang  |  value   | 
|-----+-------+----------+-------+----------|
| 853 | text  | question |  en   | question |
| 854 | text  | question |  nl   |  vraag   |

So for each extra translation in another language we would have to add another row

Were thinking about changing it to a table which has a column for each country value (so you would just need to add 1 row).

So it would look like this:

| id  | type  | name     | lang  |  nl     |  en        |
|-----+-------+----------+-------+---------+------------+
| 853 | text  | question |  en   |  vraag  | question   |
  • Are there any downsides to doing it the second way compared to the first one?
  • How would you suggest creating a translation table like this?
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
herriekrekel
  • 571
  • 6
  • 15
  • 1
    Why not to join two tables, master one with `id,type,name` fields and nested with `id,master_id,lang,value`? – Aleksei Matiushkin Jul 17 '14 at 14:02
  • So for 1 translation i would have to add a "master translation" than add all the translations row by row? – herriekrekel Jul 17 '14 at 14:11
  • No. I need to paste some code, so let’s see my answer. – Aleksei Matiushkin Jul 17 '14 at 14:13
  • I'm going to throw in a new option: .json files. I'm not even joking. `$lang['master_key'] = 'chave mestra'` and then you just encode `$lang` with `json_encode` and `file_put_contents()` it to `pt.json` then you just have to convert it back when you want to use it; No need for sql tables and would always be in one simple place to fetch :) – MoshMage Jul 17 '14 at 14:26

3 Answers3

3

Why not to join two tables, master one with id,type,name fields and nested with id,master_id,lang,value. For the given example that will be looking like:

ID      TYPE     NAME
 1      text     question

ID      MASTER_ID      LANG        TRANSLATION
 1      1              en          question
 2      1              nl          vraag

The translation set for one language is given by SQL query:

SELECT * FROM `nested` WHERE `lang` = 'nl'
-- vraag
-- .....

The translation for the given term (e.g. question, having id=1):

SELECT * FROM `nested` WHERE `master_id` = 1 AND `lang` = 'nl' 
-- vraag
Aleksei Matiushkin
  • 119,336
  • 10
  • 100
  • 160
  • Only thing im wondering say i have a million translation in 10 languages. In this type of format i would need 10 times the rows + extra million master rows instead of the million rows in the second example wouldn't it be alot slower? – herriekrekel Jul 17 '14 at 14:20
  • Whether you have million translations (are you Jimmy Wales, aren’t you?) you’d better maintain 10 subsequent tables for 10 languages, I guess. That’s the chicken-egg question about maintainability and performance. – Aleksei Matiushkin Jul 17 '14 at 14:26
  • 2
    if you use proper indexes you should not have any problems with fetching data for your ids. – Olli Jul 17 '14 at 14:41
  • you can also add another lookup table for your languages so you would have your "master_id", a language_id and your translated text. – Olli Jul 17 '14 at 14:50
  • @TheChaos This is what I suggested talking about 10 tables for 10 languages. – Aleksei Matiushkin Jul 17 '14 at 14:53
  • @TheChaos The more i think about it the more sense this is starting to make now you mentioned the indexes just couln't quite figure it out myself :P thanks for explaining. – herriekrekel Jul 17 '14 at 14:54
  • @mudasobwa: You don't need 10 tables for 10 languages. Simply add another language to your languages table and use that id as reference in your translation table. – Olli Jul 17 '14 at 14:56
  • @mudasobwa Your right took a while to get why it would be better but it makes alot more sense now :) – herriekrekel Jul 17 '14 at 15:20
  • @herriekrekel Glad to be helpful. – Aleksei Matiushkin Jul 17 '14 at 15:24
1

The downside of the second idea is that for every new language you want to add you have to change your database structure (following code changes to reflect that structure change) whereas the first only needs new rows (which keeps the same structure).

another plus for the first idea is that you really only need the space/memory for translations you add to the database. in the second approach you could have lots of empty fields in case you won't translate all texts.

a way to do it could be (an addition to the answer above from @mudasobwa):

Master Table:
| id | type | master_name |
|----+------+-------------|
|853 | text | question    |
|854 | text | answer      |

Language Table:
| id | language_name |
|----+---------------|
| 1  | english       |
| 2  | german        |

Translation Table:
| id | master_id | language_id | translation        |
|----+-----------+-------------+--------------------|
| 1  | 853       | 1           | question           |
| 1  | 854       | 2           | Frage              |
| 2  | 853       | 1           | answer             |
| 2  | 854       | 2           | Antwort            |

So if you have another language, add it to the language table and add the translations for your master texts for that language.

Adding indexes to the ids will help speeding up queries for the texts.

Olli
  • 1,708
  • 10
  • 21
0

Second way is much better:

  • Keeps less place in Database.
  • Loads faster.
  • Easy to edit.
Ozan Kurt
  • 3,731
  • 4
  • 18
  • 32
  • 1
    this approach would only make sense if you know beforehand which languages you will have and you won't have to add/remove some. – Olli Jul 17 '14 at 14:21
  • another addition to the "less place" argument: Every column you declare, even if not filled, will need space in your database, so this is wrong: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html – Olli Jul 17 '14 at 14:27
  • @TheChaos so would you suggest the answer below than where you have a master id than for each translation you put it in the secondary table with the linked master id? – herriekrekel Jul 17 '14 at 14:45
  • @herriekrekel: Yes i would do that (in fact already did). We use the exact same type of structure for our data over here. – Olli Jul 17 '14 at 14:49