Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Unanswered: Localization Question

    Quick question, which of the following two designs would be better in terms of performance (especially when planning to use mysql full text search) for localizing certain columns of a table:

    Table item (contains language neutral data)
    ------------------------------------------
    int item_id
    int some_value_a
    float some_value_b


    ** Solution A **

    Table item_name
    ----------
    int item_id
    varchar(255) value_enGB
    varchar(255) value_deDE
    varchar(255) value_frFR
    varchar(255) value_esES


    ** Solution B **

    Table item_name_enGB
    ----------
    int item_id
    varchar(255) value


    Table item_name_deDE
    ----------
    int item_id
    varchar(255) value


    Table item_name_frFR
    ----------
    int item_id
    varchar(255) value


    Table item_name_esES
    ----------
    int item_id
    varchar(255) value

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    neither

    i prefer option C

    Table item_name
    ----------
    int item_id
    char(2) lang
    primary key (item_id,lang)
    varchar(255) value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I would recommend solution C :

    Table item_name
    ----------
    int item_id
    varchar2(4) language
    varchar(255) value

    Or D if you want to split the code (I assume there is a language code and a territory code, but you can adapt) :

    Table item_name
    ----------
    int item_id
    varchar2(2) language
    varchar2(2) territory
    varchar(255) value

    And then a lookup table for the language codes (plus one for the territory codes in solution D).

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Oct 2007
    Posts
    2
    But what about the collation? Using C means you need an universal setting for all languages. Furthermore, using C you would end up with a single fulltext index over possible cultures. But correct me if I'm wrong, cross culture searches in web applications are extremely rare. So wouldn't that slow down the FT index unnecessarily?
    Last edited by eRAZOR; 10-26-07 at 04:53.

Posting Permissions

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