SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Languages - In or Out of the Database?

    Hi.

    This is the first Topic I've posted at the (very cool) sitepoint forums so go easy on me.

    I'm working on a real estate web site and I have to make this site available in several languages.

    I wanted to ask you whether I should use the database for the different languages. In other words - should I use the database to keep translated strings? If I do - I may have a table that looks like this:
    property_types (property_type_id, property_type_english, property_type_spanish, property_type_german)

    This approach would probably work but I really am not sure whether this is the right way to go. It would great I manage to separate the languages from the database and the source code.

    I heard about this project called GNU gettext. I don't know details about it and whether it can be applied to a project such as the one I'm working on, but maybe I should use something like this.

    Can you please tell me how the well built sites deal with languages? What are the standard approaches? Any help would be fantastic.

    Thank you very much.

  2. #2
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The two approaches you mentioned are the standard ones. The other one is to define an array (or several arrays) with all language-specific phrases, like phpBB does, but that is quite memory inefficient.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, the "best practice" for handling languages is not to have a new column for every new language, but rather, a new row
    Code:
    create table property_types
    ( property_type_id  smallint     not null 
    , language_id       tinyint      not null
    , property_type     varchar(255) not null
    , primary key ( property_type_id, language_id )
    , foreign key ( language_id ) references languages ( id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, the "best practice" for handling languages is not to have a new column for every new language, but rather, a new row
    Code:
    create table property_types
    ( property_type_id  smallint     not null 
    , language_id       tinyint      not null
    , property_type     varchar(255) not null
    , primary key ( property_type_id, language_id )
    , foreign key ( language_id ) references languages ( id )
    );
    But, most likely, property_type_id should be a string as well, unless you want to define an integer constant for each phrase in your application.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    um, no -- maybe you didn't undertand how my table works

    let's define three property types

    1 21 Wohnung
    1 22 Residence
    1 23 Résidence
    2 21 Geschäftlich
    2 22 Commercial
    2 23 Commercial
    3 21 Schule
    3 22 School
    3 23 École

    if you haven't guessed, the values for language_id are 21=german, 22=english, 23=french

    so there are three property_type_id values, 1,2,3 -- how did you think a string would work here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys.

    I love your approach r937. It's much more elegant than mine.

    @ earl-grey: I checked the array approach of phpbb last night. The idea seems seems to me quite good - simple and clear.

    I still however am not sure whether I should store the language-specific phrases in the database or out of it (like phpbb or wordpress does). I suppose the projects that keep them out want to make it easier for more people to take part in the translation, but is this the main reason? earl-grey, I suppose this is not very smart question, but why do you think the array approach is memory inefficient?

    What I like about using the database approach is that all strings would be well organized.
    What I like about the array approach is that it separates the languages from the application. It seems more modular. You don't think about languages when you develop the application and you don't think about the application when you add languages.

    It's hard for me to decide which which approach to take. Can you give me an example of an internationalized applications that use database language strings?

    Thank you very much.

  7. #7
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    um, no -- maybe you didn't undertand how my table works

    let's define three property types

    1 21 Wohnung
    1 22 Residence
    1 23 Résidence
    2 21 Geschäftlich
    2 22 Commercial
    2 23 Commercial
    3 21 Schule
    3 22 School
    3 23 École

    if you haven't guessed, the values for language_id are 21=german, 22=english, 23=french

    so there are three property_type_id values, 1,2,3 -- how did you think a string would work here?
    Instead of 1,2,3 you would have "key phrases" in English: "School", "Residence", "Cemmercial". That's simpler to understand what literal means in application, in case it's "School" then in case it's 2.

    Quote Originally Posted by M40_ View Post
    I still however am not sure whether I should store the language-specific phrases in the database or out of it (like phpbb or wordpress does). I suppose the projects that keep them out want to make it easier for more people to take part in the translation, but is this the main reason?
    As I already said, using a database would be more efficient in most cases. You can come up with a tool to simplify the translation process for people not involded in the programming and database parts.

    Quote Originally Posted by M40_ View Post
    earl-grey, I suppose this is not very smart question, but why do you think the array approach is memory inefficient?
    Because there is always a ton of phrases loaded in memory when only few of them are used in a requested page.

    Quote Originally Posted by M40_ View Post
    Can you give me an example of an internationalized applications that use database language strings?
    Don't know any. My old custom-coded website did it that way, though. Now I'm using WordPress.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by earl-grey View Post
    Instead of 1,2,3 you would have "key phrases" in English: "School", "Residence", "Cemmercial". That's simpler to understand what literal means in application, in case it's "School" then in case it's 2.
    i don't think so

    could you give me an example of a row from the property table, showing the foreign key to the property type table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't think so

    could you give me an example of a row from the property table, showing the foreign key to the property type table
    I thought you were talking about using a single table (not counting languages table), as there's no foreign keys (not counting the key to the languages table):

    Quote Originally Posted by r937
    Code:
    create table property_types
    ( property_type_id  smallint     not null 
    , language_id       tinyint      not null
    , property_type     varchar(255) not null
    , primary key ( property_type_id, language_id )
    , foreign key ( language_id ) references languages ( id )
    );
    What I tried to point out is that rows like:
    Code:
    School-Schule-21
    School-School-22
    School-École-23
    would be easier accessible from the application, as you won't have to know the phrase id, but will just tell the database that you need translation of the word "School".

    But as I see now, adding an extra table, where you will store "phrase keys" and their ids, would be even better.


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
  •