在MySQL DB中存储翻译的最佳方法

Question

I'm working on multi-language website.

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

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)

Answer 1

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.