Mysql - multilanguage db schema?

[eluser]ch5i[/eluser]

i would only make one table instead of two

yes, that's how I wanted to do it... but how?

So in that table you would have a composite primary key made up of string_id and lang_id - the string_id has to be the same for all the strings of one 'set' of translations because you can store only one id per record and field (eg. role.string_id).

Is there a way to use auto_increment for this?

Code:

--------------------
role
--------------------
*role_id | string_id
       3           1
       5           2
--------------------



------------------------------
lang_string
------------------------------
*string_id | *lang_id | string
         1          1   guest
         1          2   gast
         2          1   cook
         2          2   koch
------------------------------

[eluser]xwero[/eluser]
I would do it

Code:

--------------------
role
--------------------
*role_id
3  
5  
--------------------

------------------------------
lang_string
------------------------------
table | field  | table_id | lang_id | string
role     title   3         1         guest
role     title   3         2         gast
role     title   5         1         cook
role     title   5         2         koch
------------------------------

sql example

Code:

select table_id, field, string from lang_string where table="role" and table_id=3 and lang_id=1 and field in  ("title");
// which gives you
3   title    guest

I would not add a key to the lang_string field because it would have to be a composite of table, field, table_id and lang_id.

[eluser]ch5i[/eluser]
Thanks a lot, xwero!

clean and smooth - I think I'll proceed with your solution.

br
Thomas

[eluser]Bui Duc Long[/eluser]
could anyone upload the exported .sql file of the final schema?

[eluser]ch5i[/eluser]
Hello,

went back to V1 after all.

I ran into two problems with xweros approach:

1) the schema does not contain all information about the fields anymore (ie. the role table contains no longer an explicit title field)

2) enforcing constraints is a lot harder, ie. I need to ensure that in every language every role title is unique, but there may be the same role names used in different languages. Easy to do with V1, but rather hard in something along V2.

I can try to upload part of the schema (sorry, proprietary code) a bit later.