storing translations of records

[SunWuKung]

Hello all,

I need to store names and descriptions of records (like ModelName, TestName, NormName, NormDescription etc.) stored in different tables on different languages and enable users to translate them into their own langueges.

What is the usual method for this - do I create a separate language table for each such table (and there would be many tables to which I would need to attach a language table), or should I rather create a single LanguageStrings table with a structure like this:

TableName (varchar)
ColumnName (varchar)
UniqueIDinTable (int)
LanguageID (int)
ColumnValue (text)

I feel that it would be probably easier to maintain a single LanguageString table - (eg. to write a translation tool interface, or importing translated items into a single table than if I tried doing this through separate tables) on the other hand this table structure looks strange for me and I am unsure about it.

Thanks for your advice.
SWK

 

[FoFa]

Here are my thoughts, right, wrong, indifferent. Store everything in one common language. Have a single table with the common language word as a key along with a primary key (autonumber?). Store different language translations in a seperate table that ties back to the common language table via the primary key. Have a user table that stores the language selection for that user, and do all your translation on the fly. The problems as I see them, this is not designed to translate documents (grammer is not accounted for, this is a word to word translation). New words will have to be detected and setup some how. Application will be slower as translation takes place. Common language users have to be detected and translation not take place. Forms can not be BOUND to a table as translation has to take place in between the form and the tables. Foriegn words may not be in the same character set depending on the OS, etc used. On the up side, everything could be translated, labels, data, titles, whatever.
I would be interested to know what you do decide on and how it works out for you.

 

[SunWuKung]

Thanks for the reply FoFa,
If I understood it correctly the method you are suggesting is very similar to the one I was thinking about. The common language table would look like this:

UniqueID (int)
TableName (varchar)
ColumnName (varchar)
UniqueIDinTable (int)
LanguageID (int) -- eg. English for all records
ColumnValue (text)

The point you are making that a table like this will be very difficult to use as the recordsource for forms or dropdowns is very valid. If the ColumnValue is of type (memo in Access, text in SQLserver) I can't think of any way of doing it - but that suggest to me that I should use separate tables. I will think about this some more.

Thanks
SWK

 

[FoFa]

Maybe Kinda Sorta, the assumption I made by your table def. is that you are trying to tie a translation to a table/column by storing the table and column name in the table. What I suggest is a lookup function that does not care about the table/column, but the value of the data. So for instance if you have a label NAME, the caption value would access a function, feed it the value "NAME", and the function would return the translated value for the language selected. Same with data fields, the only issue like I said is the form could not be BOUND to the table as this function to do any translation would have to be called in between accessing the data and displaying the data. Actually the form could be bound, but those controls that would need translating could not.