[eRAZOR]

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

[r937]

neither

i prefer option C

Table item_name
----------
int item_id
char(2) lang
primary key (item_id,lang)
varchar(255) value

[RBARAER]

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

[eRAZOR]

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?