Database structure to manage dynamic language data

0
votes

I suppose this problem has a standard solution but i cannot find it..

I have some tables that store user data. The columns that contains text data could be converted into differents languages.

I suppose i have to create a table containing the languages ids like "fr", "en".. and another PER user's content table that store the translation, the associated language and a foreign key to the line of the user content table.

The problem is that this solution force adding a languageTranslation table for each user's content table (because a foreign key column cannot point on different table).

Is there a simpler solution ?

1
multilingual
Describe your software stack. Maybe there are some existing libraries which can ease the pain. – Colin 't Hart
it is for a web application which will by made in PHP/MYSQL with Symfony 2 – Leto

1 Answers

0
votes

You have to decide what will be translatable and what not.

It will help if you can decide on "keys" or "tokens" which will be replaced with the string in the appropriate language.

It may help you to construct keys of the form schema.table_name.column_name.value so you have some structure (a bit like Windows registry paths in a way).

But yes, it is a lot of work and there aren't really any shortcuts.

--

Since you've told us you're using Symfony 2, maybe the following links will help you?

http://symfony.com/doc/2.0/book/translation.html

http://api.symfony.com/2.0/Symfony/Component/Translation/Loader/LoaderInterface.html