Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 653
  • Last Modified:

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
0
pele_it
Asked:
pele_it
  • 6
  • 4
  • 2
2 Solutions
 
gplanaCommented:
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.
0
 
profyaCommented:
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
0
 
pele_itAuthor Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gplanaCommented:
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.
0
 
profyaCommented:
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.
0
 
gplanaCommented:
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).
 
0
 
profyaCommented:
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.
0
 
gplanaCommented:
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.
0
 
pele_itAuthor Commented:
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.
0
 
gplanaCommented:
In my oppinio, normally the best option is to add a localized table for every table, not just one only localized table for the entire database.
Also is not agood idea to have a localized table for every column to locaize. For me, best option is to put all localizable columns in a table on a new table, and repeat his process for every table creating a new localizable table.
I hope it's clear now
0
 
profyaCommented:
It is difference in point of view, however, it depends on the volume of data you are going to store the database. Although normalization is the best to do when designing a database, sometimes you de-normalize your database for performance. Users are not aware of the internal design of the database their system is built on, they want their business done faster. Again, you pele_it who can decide based on your database plan and capacity expected. Indexes not always a solution, it is a helper to accelerate row location. Regarding consistency, you need to edit each field changed using its language, whether it is in the same database or not, this action controlled by the editing and updating procedure in place.
I afraid you run into bugs more than providing your customers with the system they expect. The technical point of view is not always the only-one point of view we consider, business point of view has greater rank I think.
0
 
gplanaCommented:
I think points shold be splitted, as we all offered different solutions.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now