Link to home
Start Free TrialLog in
Avatar of pele_it
pele_it

asked on

Database Localization

Hi,
I am trying to find out the best way to localize my Schema.

I have a database schema with large number off  tables that need to be localized to unknown number off languages.

The problem is, that i want the localization to be, simple to managed and have the best performance has possible.

The way i look at it i have two ways:

1) Localized each table separately. 1 Localized table for each data table -  good for performance but difficult to managed.

2) Localized each column separately.   1 Localized table for all data table good to managed But poor on performance.

Does any one have some tips for the right approach, or knows of a good Artical or "Best practice" for localization?
--
Thank''''s
Avatar of gplana
gplana
Flag of Spain image

What do you want to localize? The contents of the tables or the names of the tables ?

If you want to localize de names, best option is to create synonyms, or views if you want to localize the names of the columns too.

If you want to localize contents, I think best option is to localize every table.
You need to localize database collation, tables and text fields. This is the most safe strategy, because it would be hard to convert field contents if you are used a wrong charset. You may need also to use SET NAMES <charset> when you are connecting to mysql to tell it about the encoding you are using if mysql is not set to use your encoding by default.
For more information about internationalization and localization please log to: http://dev.mysql.com/doc/refman/5.0/en/internationalization-localization.html
Avatar of pele_it
pele_it

ASKER

gplana,
I want to localized the content of the tables like- Product Name & Description ...
But I have abount 50 columns from 15 diffirent tables to localized.
I prefer to use one big table for all localizations (from managebility point of view), but than i need to join every column (i want to select) to the localization table by the "Column Data ID" & "language Code".
That's means that if i want to select 10 Columns, i need to join 10 times to the localization table. This could hurt in preformance.
Ithink you shuld add a new table for every 15 tables to be localized (so, that is, 15 new tables).
 
For example, for every table named xxxxxxx, please add a new table called xxxxxxxx_localized. This table should have the localized fields, id of the xxxxxx table and id of the language. Primary key should be composed by id of the table and id of the language. id of the table shuld reference primary key of the xxxxxx table.
Pease let me know it that is clear.
Regards.
You are making things complex, this will affect the overall performance. I think the best thing is to clone the database for each language, use UTF-8 as a charset. You can name your databases using your language prefix, for example en_mydb, fr_mydb, sp_mydb and so forth. You may under estimate the complexity you are going to run into.
But this way you will have redundance. Performance shold not be very poor as you should filter yoyr queries by the indexed fields (language and id, which are the primary key on the localized tables).
 
Joins makes queries slower as everyone knows, you do it when it is a must on normalized databases, however, if you want to take the both, logic and performance, I think you should consider run the system on separate database.
That's correct, but if you run on separate databases you ill have some a lot of duplicated informatio, and this is dagerous as your data could became incoherent.
For example, imagine you have a countries table. You can localize some fields such a name and description, but some other fields such as population are not localized. So with your proposed solution you will have population duplicated, and if you updated one, you should remember to update the population on all your databases.
However, a join is reasonably fast if the internal operations are using indexes. This is my advice. However, this is a design desition, every option has its advantatges and its dsadvantatges
In my opinion my proposed solution s the best "balanced" solutio, but it depends onyour needs.
Avatar of pele_it

ASKER

Thank's
I agree with gplana, that the solution should be in one db.
I just not sure if to do it in one main localized table or for each table (that need to be localized) to add a "Localized Table".
gplana, i understand that you support  diffirent "Localizes table" for each table.
ASKER CERTIFIED SOLUTION
Avatar of gplana
gplana
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think points shold be splitted, as we all offered different solutions.