SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Oct 2004
    Location
    italy
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database or databases in a multilanguage site?

    Hello guys.

    Once again I'm in need of your help. I'm designing a site that's multilanguage (3 languages) and it uses a database to retrieve products and other stuff.
    My question is this: Would it be better to store the stuff for all 3 languages in 1 database or it would be better to store in a different database for each language.
    Mind you that I have a form that submits user data to the database so it will grow with time (at least I hope )

    Thanks in advance for your replies
    Bye

  2. #2
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How much data are talking about? I've got a lot of experice with this and trust me, things get complciated when you start using multiple databases or multiple tables within the same database.

    For smaller tables you can create ONE table for the language information, such as CategoryLanguage and in there you stuff the Category Key, the languageId, and the columns that you need to translate.

    You then take a deep breath and decide if that is good enough. You pause for a minute, have a coffee, and ask if you have to go to the next step. Once you are sure you have a decision that you want to get more complicated you go to bed and sleep on it. If you wake up in the morning and decide that you must go deeper then you can keep reading...

    The next step is to create multiple tables that are responsible for their own language. For example, you have a product table and you want to translate it. You know that you will have hundreds of thousands of rows, and adding the language column in there would multiply the total number of rows, which is unacceptable...

    What you end up with is the following:

    Product <- Non textual information
    Product_1 <- English
    Product_2 <- Spanish
    Product_3 <- French

    Where things get ugly is that now you have to create multiple copy of all stored procedures that need to access these tables because _1, _2, _3 is in the table and unless you build a query on the fly you can't use a variable for the table name.

    So you end up with Views, Stored Procs, UDFs, all in multiple copies:

    SearchProduct_1
    SearchProduct_2
    SearchProduct_3

    A real mess!!!!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Product <- Non textual information
    Product_lang <- textual info, by language

    then you can add new language to the application with no change whatsoever to the database

    when the user logs in, set a session varaible for language (e.g. 'en', 'fr', etc.)

    the query to retrieve data is basically real simple --
    Code:
    select N.nontextualcolumns
         , L.textualinfo
      from Product as N
    inner
      join Product_lang as L
        on N.id = L.product_id
       and L.language = $user_lang
    and this works no matter how many different languages you want to support
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Oct 2004
    Location
    italy
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK guys! Thank you for your replies!

    To Guimauve

    It looks like you've had your share of bad experiences with the subject

    Thank you all. Now everything is a lot clearer.

    Bye!

  5. #5
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm still dealing with it every day... Problem is that we have over 500,000 products that we support in 7 languages. That adds up to 3,500,000 rows if it were to be in a single table. Since we do keyword searched on that table the indexes aren't always used, so it scans through the table. If that happens then you want as small a table as possible.

    Charles

  6. #6
    SitePoint Member
    Join Date
    Oct 2004
    Location
    italy
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Guimauve
    I'm still dealing with it every day... Problem is that we have over 500,000 products that we support in 7 languages. That adds up to 3,500,000 rows if it were to be in a single table. Since we do keyword searched on that table the indexes aren't always used, so it scans through the table. If that happens then you want as small a table as possible.

    Charles
    3,500,000 rows???? Now I understand your suffering Then scan through the entire table, boy that's got to be SLOOOOOOW.

    Since you know your fact regarding databases I would like to ask another question hoping that this is not a problem.

    I want to be able to have an initial value of a field based on a query. Let me explain better.

    I want to be able to send an email to the clients that have accepted to receive one, once my products get updated. In order to do that I inserted a Date field that holds the date in which each client registered, an email YES/NO field that says if a person has accepted to receive a mail and a field that contains the total number of products in the database when the client had first registered.
    Hoping that my procedure is correct (any thoughts would be welcome) I'm having problems getting this total number of products in my needed field. I already made the query using Max() but in the field to be updated I have to manually select the value from a list box instead of the value being inserted automatically in the field.
    I've tried to work in the Default value property of access but I don't know what kind of expression to use.
    Hope my explanation was clear enough.
    Hope you've got some suggestions.

    Bye for now and don't let a mere ( )3,500,000 row problem get you down!


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
  •