mySQL and languages

[Pickle]

Hi everyone

 

I am just doing some research into mysql and the languages it supports and thought it would be good to ask for some advice on this forum.

 

I am planning on building a site in the near future and I want to build it in such a way that I can easily add the choice to read the site in other languages.

 

After the research I have done, the plan is to have a product_tbl where all the common product fields will be kept. i.e. image, prod_id, postage_id etc

 

then to have a separate table for each language which links to the product table, these separate language tables will keep all the text for each product, so prod_name, prod_desc.

so for example: uktext_tbl with fields: uktext_id, prod_id, prod_name, prod_desc and the same for other languages.

 

the reason i have chosen to keep separate tables for each language is because from what i understand, you need to set each table upon creation to accept certain characters. so i cannot just have a separate field in the product table for the each language i.e. prod_nameuk, prod_namegr, prod_namede, prod_descuk, prod_descgr, prod_descde. is that correct?

 

I also wanted to keep everything on one site, i dont want to have a different site for each language and so i would need to change the charset within the html file depending on the language chosen by the visitor in order for the file to support the language being retrieved from the database.

 

So my question is, does this sound like a good way of doing this? is there an easier/better way of working this?

 

any suggestions on how to implement different languages into one website would be great. this is how i have figured it out in my head and how it would work best for what i want but obviously it would be great to have opinions from others more experienced in this type of website.

 

thanks in advance

[Pickle]

hi

 

just wondering if anyone had any thoughts on this?

 

*bump*

 

[Mchl]

You can use utf8 encoding, which covers special characters from majority of world's languages. As far as collation is concerned, you can specify which one to use in a query. This way you should be able to have just one table.

 

 

table l18n

langID, sentenceID, sentence

---------------------------------

en, 1, Sentence in english

zh, 1, 一句中文

jp, 1, 和文

[Pickle]

Hi Mchl

 

thanks for your reply. im not sure what you mean. do you mean i can specify the tables to have utf8 encoding and then they will support most languages?

 

and then for table structure i can have the following?

 

product_tbl

prod_id, prod_img, prod_price etc etc

 

 

prod_lang_tbl

prod_land_id, prod_id, lang_id, prod_name, prod_desc

 

and have all the languages for each product in my prod_lang table?

[Mchl]

Yes you can.

This forum uses utf8 encoding to store posts. As you can see in my post above it allows for storing latin, chinese and japanese characters. There are several more alphabets supported.

This will allow you to keep your database structure better organised.

[Pickle]

hi mchl

 

great!! thanks for your replies and help. i really appreciate it.