I need to convert my website into multilingual, and on the MySQL end am looking on going for a schema like (stripped down/simplified):
tbl_languages
language_id
language_name (English, French, etc.)
language_code (en, fr)
tbl_products
product_id
product_creation_date
product_cat_id
product_img_url
other_fields
tbl_product_details
detail_id
product_id (ref tbl_products)
language_id (ref tbl_languages)
product_title
product_desc
other_textual_lang-specific_fields
I guess firstly, what are the thoughts on that setup? There is some “master data” (category names, etc.) but MOST of the data is actually third-party provided and may or may not have the data in multiple languages
Secondly, what about on the frontend? I am currently using smarty and there are various modifiers, etc. Simply replacing a string with another depending on language is easy enough, but I worry about cases like:
“Found 24 results for your search of ‘ipod’”
results should be result if there is only 1 item, and though I am not as familiar with French, I believe multiple words are likely to change suffix/prefix depending on singular/plural. How can cases like this be addressed?
Thanks!