Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2002
    Posts
    3

    database design for multi-lingual websites

    Hi there,

    Last few weeks I've been searching te web for information on how to design a database for the use in a multilingual website.
    The database will be used to present webpages in English, German, French, Spanish for starters. Other languages will be added in the future. The web user must be able to switch languages at any time.
    I can't find any information on this subject. I'm I looking in the wrong places?

    I've quite some experience designing and programming Coldfusion + and PHP+MySQL internet sites and intranet sites, so I'm not looking for 'newbie' information. I'm looking for best practices, good books or whatever help is available on the subject.

    TIA

  2. #2
    Join Date
    Dec 2001
    Location
    Delhi/Bangalore, India
    Posts
    74
    I'm not sure if this info is what you would like to see...(this can't be classified as newbie afaik), but when I searched Google, I got 2 links:

    http://cslu.cse.ogi.edu/HLTsurvey/ch8node7.html

    http://www.hum.uva.nl/~ewn/

    You already might have seen and discarded these links, but if you haven't take a look.

  3. #3
    Join Date
    Mar 2002
    Posts
    3
    Thanks for your quick response Manish,

    Yes, I already found these projects, but they are focussing (like many other projects) on the translation of a word in one language into another language, using some sort of common index for all languages.
    For my project I'm interested in presenting English text on the English part of the website, Spanish text on the Spanish part and so on. So my texts are stored in the database (the translations are done already) and additions/modifications for each text will be made by different editors using a content managment system for the database.

    Maybe I'm trying to make it more complicated than it is and just make a similar set of tables for each language the website uses?

  4. #4
    Join Date
    Feb 2002
    Posts
    20
    roel,

    Here is what I have done to incorporate multi languages on the sites I work on. I create separate language files for each language and then include the language file according to which language the user chooses. In the files I just have one language array.

    For example,

    file: english.php
    <?
    $lang['About us'] = 'About us';
    $lang['History'] = 'History';
    $lang['Clients'] = 'Clients';
    ?>

    file: dutch.php
    <?
    $lang['About us'] = 'Over ons';
    $lang['History'] = 'Historie';
    $lang['Clients'] = 'Klanten';
    ?>


    A sample php page may look like this: ($userinfo is an array from the database, the data is the users profile info where one of the colums in the table is language)

    <?
    if ($userinfo['language'])
    {
    // The user has a chosen language
    include ($userinfo['language'] .".php");
    }
    else
    {
    // The user has no language chosen so use the default
    include ("english.php");
    }

    print $lang['About us'];
    print $lang['History'];
    print $lang['Clients'];

    ?>

    That is really. It works very fast and is much easier than using a db. I guess the only difficult part is getting someone to interpret your text into other languages.

    Hope that helps you.

    Piet
    Last edited by piet; 04-07-03 at 18:04.

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I have never made multiple-language sites, nor do I know how your site is structured, but if I kept content like articles in the DB I'd do something like this:

    Code:
    CREATE TABLE language(
      language_id      CHAR( 2   ) NOT NULL,
      language_name VARCHAR( 100 ) NOT NULL,
      PRIMARY KEY( language_id )
    )
    
    -- content would be language independant stuff if you had it
    -- this table is optional... was trying to think that something like
    -- the 'head' of the table would have a lot of the same information (hidden HTML)
    -- and maybe only the page title would change based on language
    CREATE TABLE content_header(
      content_name VARCHAR( 100 ) NOT NULL,
      content      TEXT,
      PRIMARY KEY( content_name )
    )
    
    CREATE TABLE content(
      content_name VARCHAR( 100 ) NOT NULL REFERENCES content_header( content_name ),
      content   TEXT NOT NULL,
      some columns...
      language_id     CHAR( 2   ) NOT NULL REFERENCES language( language_id )
    )
    Then when you want the article you just do:
    Code:
    SELECT content
      FROM content
     WHERE content_name = 'intro_paragraph'
       AND language_id 'EN'
    Thanks,

    Matt

  6. #6
    Join Date
    Feb 2002
    Posts
    20
    MattR,

    Good approach! I guess since roel originally requested a database solution your answer is more befitting. I have read many of your posts and have always liked what you have had to say. On that note, let me ask you:

    1. For static content on the site would it really be quicker to implement a db solution? For example, on www.fanhome.com in the navigation bar on the left the links forums, chat, mailbox, fanshop, affiliates, about us, and contact us would never really change so why would one want the overhead of retrieving info from the db.

    2. In addition, would putting all your content in the db be more work than neccessary. Creating forms to input the data and such seems like a lot of work.

    What do you think?

    Piet
    Last edited by piet; 04-07-03 at 18:04.

  7. #7
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Well, we've found that keeping it in the DB is adventagious because it allows us to add web servers without worrying about figuring out how to get the HTML to the boxes (make a change in one location and all servers get the new data) and allows us to segregate the HTML and code. Since our pages already require DB use for pretty much everything there is no extra network overhead or anything for opening a new socket. I'm not convinced, and our tests don't show a noticeable change, in overhead of requesting some HTML from the database. If we had to do it all over I'd probably continue to use the database since I don't have to worry about FTP'ing templates to multiple boxes, etc. Plus I have database reporting and auditing tools in which to monitor who does what to the HTML for a little bit more security (perhaps).

    I think it probably shows a return in the long run due to the initial overhead of developing forms and such (which actually doesn't take long at all).
    Thanks,

    Matt

  8. #8
    Join Date
    Mar 2002
    Posts
    3
    Thanks MattR en Piet for your responses.

    Meanwhile, I had to go on and I took about the same approach as MattR.
    For the tables which hold content in different languages, I finally made the primary key on two columns: an _ID column (INT) and a Language_ID column (TINYINT). It's very easy now to retreive the same 'content row' in a different language, as they have the same _ID, but different Language_ID's.
    Although I won't expect users from the website will switch languages all the time, it's merely intended for the content management system for this site.

    Thanks all for your response.

  9. #9
    Join Date
    Aug 2003
    Location
    San Fran
    Posts
    1
    Can you post your final design schema and SQL Code?

Posting Permissions

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