CODEWITHSUNDEEP
phpmysqlsqltranslation
herriekrekel
herriekrekel

Reputation: 571

Correct way making a translation table?

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   |

Upvotes: 2

Views: 6595

Answers (3)

Olli
Olli

Reputation: 1738

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.

Upvotes: 1

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121020

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

Upvotes: 3

Ozan Kurt
Ozan Kurt

Reputation: 3859

Second way is much better:

  • Keeps less place in Database.
  • Loads faster.
  • Easy to edit.

Upvotes: 0

Related Questions