[samaa]

hello guys .. I need your advice for my current project.
Q for programmers and DB Administrators .. if you planning to Build a multilingual website, what is the best databases schemas to use?
1- duplicate the column name(field). example : (title_en , title_ar)
2- duplicate the row for each record & using a field to specify the langue (content_lang)
* I know there is another ways for multilingual website like Translation table but I don't want to uses these becuse I will have just 2 languages.

thanx (f)

[dgreenhouse]

That's a really good question...

A possible method would be to have a separate table for the translations and then to issue a query with a coalesce function in the where clause.

i.e.
possible structure for language_table: (could have better naming)

1- id, text_field_name, field_text, lang
2- id, text_field_id, field_text, lang

Queries:

1- select field_text from language_table where text_field_name = "the_field_name" and lang = coalesce("target_lang","default_lang")

2- select field_text from language_table where text_field_id = the_id and lang = coalesce("target_lang","default_lang")

An added benefit is that you can add any number of languages with little effort.

For these queries to work, YOU SHOULD have a text entry for the default language or the query will return a null record if you forget to add translation text for the “non-default” language you’re trying to retrieve.

Query option 2 above will be faster, but you'll incur additional overhead in determining the id of the translation text. This overhead will be incurred both when you add new text and when retrieving existing text. Possibly obviated by having a (field_name <==> field_id) joining table but still causing more complexity.

Of course with either option the columns should be properly indexed.

Finally; and back to what you're trying to avoid , the fasted method would be to have separate language tables in case there's lots of translation text.

i.e.

Code:

  // base lang = us-en
   
  $lang = 'fr-ch'; // desired language Swiss French
  $field_name = 'site-title'; // Get the site title
   
  $query =
     sprint(
       'select field_text from %stranslation_table '
     . 'where text_field_name = "%s"',$lang,$field_name
     );