Two languages in one database, without the mess
Parallel columns or a translation table? The difference only shows up at the third language — but then it shows up loudly.
When you build a bilingual site, the first instinct is to add title_ro and title_en next to the other columns. It works. It works well, right up to the day someone asks for Hungarian too.
The two options
Parallel columns means one table, direct queries and no JOINs. The editor is simple: a form with two fields for every piece of text.
A translation table means a parent table holding everything language-independent, and a child table holding the text:
SELECT p.id, t.title, t.slug
FROM posts p
JOIN post_translations t ON t.post_id = p.id AND t.lang = :lang
WHERE p.status = 'published'Why I picked the second
Not because it is more elegant, but for three practical reasons:
- A new language is a new row, not a schema migration
- You can see what is translated and what is not with a plain
COUNT - Slugs can differ per language, which matters for search
The price
Every query carries one more JOIN, and the admin form gets a little more complex. On a personal site both are negligible. On a catalogue with tens of thousands of products, measure first.
