Mysql - multilanguage db schema?

[eluser]ch5i[/eluser]
Hello,

I'm currently writing an application that will be used in more than one language and would like to get some advice concerning a good way to handle multilanguage entries in the database schema.

See the attached images:

version1.jpg from here

and version2.jpg, my own design.

Both ways have their weaknesses/benefits...
In both approaches the language dependent data is factored out into separate tables - in version1 there is a table for every entity that holds the translations and in version2 one table that holds all strings and one that holds all translations.

Any experience with something like this?
What would you recommend?

[edit]: added v1/v2 to images

[eluser]xwero[/eluser]
For version 1 you mean the bad design image, right?

The version 2 is the way i do it. splitting up the data in language independent and language dependent tables gives you the most flexible way to maintain strings.

[eluser]ch5i[/eluser]
xwero, thanks for the fast reply

For version 1 you mean the bad design image, right?

yes... I guess

The version 2 is the way i do it. splitting up the data in language independent and language dependent tables gives you the most flexible way to maintain strings.

... so you have all translations in one single table in your schema and all other tables are just pointing to it for the strings?

[eluser]xwero[/eluser]
I'm sorry version 1 is how i do it.

I think with version two you will end up with a big table where it's going to be hard to separate which string you need. You can compare it to putting all the languages files in one file. Of course with sql statements you can extract pieces so you don't have a performance issue like you have with the language file.

If you go a little further with version 2 you can check if the string already exists and make a reference which means less content but more complex queries.

I think version 1 is more modular than version 2 but the two ideas are good.

[eluser]Référencement Google[/eluser]
Version 1 isn't expendable, what if you want to add a new language?
I am designing my DB like version 2, this is the best way, the database is made to handle many tables, even if it's end with a complex scheme, users don't use PHPmyAdmin to update their website, this is the work of the coder to make it work, even it's complex.

[eluser]xwero[/eluser]
You can add a language as easy in version 1 as in version 2. I don't see why you couldn't add another language in version 1?

[eluser]Référencement Google[/eluser]
So, once your tables are full of datas, would it be so easy to update and add your languages?

[eluser]xwero[/eluser]
Yes the strings are separated by the culture id

Code:

id  culture  string
1   en       hello
1   es       ola
1   fr       allo

The difference between the two is version 1 the strings are divided in tables so it is more clear on sight where the strings belong to. Version 2 all the strings are thrown together in one table.

[eluser]ch5i[/eluser]
xwero has that right, the only difference is where you put your translations:


V1) one translation table for each entity with lang-dependent attributes

V2) one single translation table, used by all entities

The difference between the two is version 1 the strings are divided in tables so it is more clear on sight where the strings belong to

To avoid this confusion I thought to use a 'context' field in the string master table of V2 which would contain (redundant) information on where the string is originally used (e.g. context = tablename.fieldname)

I agree with xwero that V1 is easier to read, but I've got roughly 20 entities in my schema which I would have to split that way, that's why I am a bit hesitant implementing V1 and wanted some feedback on how other people handle something like this.

V1 allows for more control on the fields, eg. field length and type but it means also that you get a lot more tables.

V2 on the other hand means adding only 2 tables, but is probaly less efficient, because it requires joins with a large translation table.
Adding languages seems easier with this approach, you just loop thought the translation table...

[eluser]xwero[/eluser]
I don't think V2 is less efficient. But i would only make one table instead of two with the fields culture, tablename, fieldname, id, string and date_modified.
You have in the tables string and lang_string a date_modified field and even in the setting and role tables you have the same field. I think the date will be the same in the first two tables and i think it's best you use the date_modified field as the date on which the record has changed even if none cultural data is changed. You will have redundant dates for the strings but i think splitting tables only to prevent redundant dates is too much normalization. The advantage will be having locale dependent modify dates.