SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to develop multilingual website?

    I would like to have the experts to share about developing multilingual websites.

    AFAIK, Multilingual Websites generally consists of two parts:
    1> Static (Form labels, Header titles, Messages etc., Generally file based)
    2> Dynamic (Db Contents like Categories, news posts etc, Obviously db based)

    Managing Static contents is easy which can be easily done as:
    Code:
    en
    - form_label.php
    - messages.php
    :
    etc
    nl
    - form_lable.php
    - messages.php
    :
    :
    language files generally consists of associative arrays or defined as constants for the static content translation.


    But the complexity part comes with the Dynamic contents translation.
    I would like to know how you develop the multilingual website (esp for dynamic contents).

    Thanks for the ideas/suggestions in advance.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Try search this forum and the Php Application Design Forum for:

    internationalisation ( or i18n )
    localisation ( or l10n )

    Its a big topic, and has been aired fairly often over the years, the ZF seems to have an awful lot of modules dealing with i18n.

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For db design for multilingual website, i have come up with following solutions
    and don't know which one is perfect also want to know if there is any other good options

    Obviously firstly we need language table as:
    ----------------
    |languages
    ----------------
    |id
    |code
    |flag
    |is_active
    ----------------
    Possible solutions
    1> using one table(suppose we have 'news' table for example)
    ------------
    |news
    -------------
    |id
    |language_id
    |title
    |body
    |created_by
    |created_date
    |is_active
    :
    :
    2> Using two tables
    ------------
    |news
    -------------
    |id
    |created_by
    |created_date
    |is_active
    :
    :
    ---------------
    |news_contents
    ---------------
    |id
    |news_id
    |language_id
    |title
    |body
    ----------------
    3> Using one master table for all tables which needs translation
    --------------
    |news
    --------------
    |id
    |title
    |body
    |created_by
    |created_date
    |is_active
    ---------------
    ------------
    |categories
    -------------
    |id
    |title
    |description
    |is_active
    --------------
    master table:
    ---------------
    |translation
    ---------------
    |id
    |language_id
    |table_name
    |table_field
    |translated_value
    ----------------
    I have found all these structure so far, dont know which one is better as i havent used such in my multilingual website.
    I would like to know the pros n cons for these structures, best db structure.

    Note: Any no of languages can be added ranging from 1 -> N

    Thanks for the help again in advance.

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are few ways to translate, besides array you can also use Gettext. I'm not very experienced in this area but check out Zend_Translate.

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by risoknop View Post
    There are few ways to translate, besides array you can also use Gettext. I'm not very experienced in this area but check out Zend_Translate.
    I am not talking about arrays and Zend_Translate. I am talking about the good structure for multilingual website.

    Thanks

  6. #6
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I this post should in mysql thread.
    Any db experts that can give analysis on the above structures
    Thanks

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Well hypothetically speaking you would need to have a separate language translation for every single piece of content. Whether it be a blog post, article, comment, etc. So for example, you would need one table with all the languages and a separate table for each piece of content holding every translation.

    blogs
    -- blogs_id
    ...

    languages
    -- languages_id

    blog_translations
    -- blogs_id
    -- languages_id

    Just as an example. So that every blog can have a different translation.Then you would need to do the same with say comments, articles, etc.

    comments
    -- comments_id

    articles
    -- articles_id

    comments_translations
    -- comments_id
    -- languages_id

    articles_translations
    -- articles_id
    -- languages_id

    That is where I would start. That is if you weren't using a automated system and needed different *custom* translations for every piece of content.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Simple example of how articles could be set up:

    languages
    -- languages_id
    primary key(languages_id)

    articles
    -- articles_id
    -- created_ts
    -- updated_ts
    primary key(articles_id)

    articles_content
    -- articles_id
    -- languages_id
    -- title
    -- content
    primary key(articles_id,languages_id)

    This way every article can have a different title and content per language.

    You have the right idea with your 2 table example.

    While the master table example will result in less tables it is much less flexible. Using two tables results in a much less rigid and more solidified solution.

  9. #9
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Mr. oddz
    But using third structure may lead to complex query structure and performance issues may arise when we have lots of rows to be translated.

  10. #10
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks mr. oddz.

    OK I will go for structure no 2.

    Now I have some few questions
    1> How to present the field to be translated
    a> Atricle Title (En) ...input field goes here...
    Atricle Title (Nl) ...input field goes here...
    ......
    b> Or using the tabs
    c> How to make effective inserts/updates
    2> Suppose initially we have two languages and there are datas in articles_content for the related languages.

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    norway
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHPycho View Post
    Thanks mr. oddz.

    OK I will go for structure no 2.

    Now I have some few questions
    1> How to present the field to be translated
    a> Atricle Title (En) ...input field goes here...
    Atricle Title (Nl) ...input field goes here...
    ......
    b> Or using the tabs
    c> How to make effective inserts/updates
    2> Suppose initially we have two languages and there are datas in articles_content for the related languages.
    I would say the language choice should be in a dropdownlist ..so avoid using one input field per Atricle Title ..

    Off topic, I know that the translation of text on webpages is not your problem here...
    "1> Static (Form labels, Header titles, Messages etc., Generally file based)"

    But I found a database solution most convient in a recent project


    Code MySQL:
     
    // webtext object ..Could be a specific page or main menu or anything of choice
     
    CREATE TABLE webtext (
      webtext_id smallint(5) unsigned NOT NULL auto_increment,
      webtext_name varchar(45) NOT NULL default '',
      webtext_description varchar(255) NOT NULL default '',
      webtext_url varchar(45) NOT NULL default '''''',
      PRIMARY KEY  (webtext_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
     
    // webtext objects has one or more "textobj "
     
    CREATE TABLE textobj (
      textobj_id mediumint(8) unsigned NOT NULL auto_increment,
      text_name varchar(45) NOT NULL default '',
      webtext_id smallint(5) unsigned NOT NULL default '0',
      long_text tinyint(1) unsigned NOT NULL default '0',
      PRIMARY KEY  (textobj_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
     
     
    // for each "textobj " there is one or more translation rows 
     
    CREATE TABLE translation (
      translation_id int(10) unsigned NOT NULL auto_increment,
      textobj_id mediumint(5) unsigned NOT NULL default '0',
      language_id tinyint(3) unsigned NOT NULL default '0',
      translation text NOT NULL,
      PRIMARY KEY  (translation_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    That way an admin can change the texts easily..

  12. #12
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the example you provided is of structure no 3 which is the generalised form.
    Obviously this deals with the lots of complex query operations.
    Any further discussion / analysis are warmly welcome.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •