CODEWITHSUNDEEP
mysqltranslation
Tural Ali
Tural Ali

Reputation: 23240

The best way to store translations in MySQL DB

I'm working on multi-language website.

For example, DB table pages for 1 lang looks like that

enter image description here

Now, I have 2 ideas about translations:

  1. To use all tables in default language - English and create second table - translations use it only if needed.

  2. Or add columns like, title_ru, title_en... to the existing tables.

Which is faster, more efficient way? And please explain your idea (why you think like that)

Upvotes: 5

Views: 1603

Answers (1)

carpii
carpii

Reputation: 2001

I would choose neither of your solutions.

Creating a new table for each language is not optimal, since you then have to modify each and every query based on which language is selected. Also what should be a fairly simple task of adding a new language, would then mean adding new table schema and updating your entire codebase.

Likewise adding a new column for each language has the same problems.

Why not just have a dictionary table, with a language id as part of the PK. Write your site so select the text based on the current language id (1 = english, 2 = russian etc)

Then to switch to a different language at runtime, all thats involved is setting language id from 1 to 2 in code, and all your queries will continue to work.

Upvotes: 4

Related Questions