[JonQCoder]

In the next month or two i'll be part of a book translation, helping to get a book translated from english into multiple other languages. We use MySQL to store all the data that will be translated and will be using it to store the translated versions(Unless there's a better way?). We will have people who speak english and the desired language translating it and typing it in to store in the DB. I'm not even sure where to begin for something like this, or what I should search for to get information about it. My experience with databases is not really as deep as I would like at the moment, mostly just surface things.

My main concern is the special characters(accents and such) for other languages. I don't want those to be stripped away or displayed incorrectly. They'll be translating it from wherever they are, so they'll be using the keyboard layouts for the language. It's only one field per language that will hold the translation. So if the english version of the field is 'clinical' then the translated version will be stored in 'clinicalgerman' or 'clinicalspanish'. They're only translating one field. If possible i'd like to keep it all in one database(Less work generating the merge file or CSV for InDesign). The database table I want to use right now is MyISAM, utf8_general_ci.

I kind of feel like i'm worrying about it to much and it's simpler than I think. Like it's just another field that stores data, they enter it, it stores and that's that, nothing fancy or special needs to be done. Are there resources available to help with this? A Book I could buy or websites? I'm sure someone somewhere has done something similar, lol. If anything is unclear, let me know!

[JonQCoder]

Just thought I would post a little update on what i'm thinking. So far 3 different solutions have come to mind.
Solution 1: I could add fields for each language to the one table.

Quote

item
id
name
german_name
spanish_name
french_name

This seems like updates and adding a new language would be tedious and a lot of work. I also don't think there would be a way to distinguish between the symbols used in each language. Translating other fields would also mean adding a new field which would make the already large table gigantic.

Solution 2: A second table to hold the translated field for each language.

Quote

item
id
name

Quote

item_lang
id
item_id
german_name
spanish_name
french_name

Where item_id is the foreign key. This seems better than the first solution, but again, if things change and more than one field needs translated, it would be tedious to add one for every different language and each additional field per language. Also only getting a certain language may be hard(resource wise) if there's a lot of languages and fields. It does reduce the risk of messing up the parent table though.

Solution 3: This is the solution i'm leaning towards most. I would create a table for each language with foreign keys to the parent table. Adding a language would be as easy and safe as creating another table. It would also only grant the translators access to the specific language needed. If one language needed another field translated I could do it for just that language without issue. I could also store the data specifically for each language so it handles the symbols correctly? And by symbols I mean things like: à, è, ì, ò, ù - À, È, Ì, Ò, Ù and ã, ñ, õ Ã, Ñ, Õ and ä, ë, ï, ö, ü, ÿ Ä, Ë, Ï, Ö, Ü, Ÿ. In addition, accessing only one of the languages to generate the merge or csv file for book printing would be a lot easier. I'm assuming the symbol issue has something to do with Collation, but I don't understand it well enough to be certain.

Any additional information/advice is greatly appreciated! This site is really great and I hope to contribute and help when i'm more experienced.

[Martyr2]

Well the trick here is not exactly the database storage itself but how you put it in and take it out. You are correct in choosing Unicode UTF for the character set. This set will handle many different characters and will handle all the accents etc just fine.

Now how do you plan on putting the data into the database and display it? Are you going to use a web language like PHP? Just make sure whatever the language is, it is also handling the input from the user in the correct character set. Earlier versions of some PHP functions expected data to be in the General Latin character set and if you don't tell PHP to deal with Unicode, it will lose the accents etc prior to inserting into MySQL. Same with display, it will read the Unicode and try to display it as Latin unless you tell PHP to deal with the Unicode set.

So without knowing more about the languages or methods you are inserting/extracting from MySQL it is hard to go into further detail.




Edit: As for the way you are storing it, I have done it similar before and it worked out fine. Have one field for english and then a field for each language translation. Just make sure that if you are storing large amounts of data you use text data type.

This post has been edited by Martyr2: 06 April 2012 - 09:55 AM

[JonQCoder]

Martyr2, on 06 April 2012 - 09:51 AM, said:

Well the trick here is not exactly the database storage itself but how you put it in and take it out. You are correct in choosing Unicode UTF for the character set. This set will handle many different characters and will handle all the accents etc just fine.

Now how do you plan on putting the data into the database and display it? Are you going to use a web language like PHP? Just make sure whatever the language is, it is also handling the input from the user in the correct character set. Earlier versions of some PHP functions expected data to be in the General Latin character set and if you don't tell PHP to deal with Unicode, it will lose the accents etc prior to inserting into MySQL. Same with display, it will read the Unicode and try to display it as Latin unless you tell PHP to deal with the Unicode set.

So without knowing more about the languages or methods you are inserting/extracting from MySQL it is hard to go into further detail.




Edit: As for the way you are storing it, I have done it similar before and it worked out fine. Have one field for english and then a field for each language translation. Just make sure that if you are storing large amounts of data you use text data type.

Well, all of the english data currently stored/displayed uses perl(Different programmer set this up). But I will be using cakePHP or PHP; more familiar with that than PERL. Though it might be easier to just be consistent and use perl, since the only changes between the screens are pretty small(just removing admin/delete options and adding a field to accept input). But I don't know enough about PERL to say for sure, it looks similar to PHP.

Happen to know off hand how to tell php to use a certain character set? Thanks for the reply by the way. Was about to go crazy and do all sorts of testing, for each solution, this narrows it down a lot so i'm testing less.