Multilanguage Database Design in MySQL

   PHP/MYSQL |   13-06-2016 |   747

This tutorial describes most popular techniques of creating database for multilanguage web sites.
Provided by Leumas Naypoka / www.apphp.com

Building a multilanguage website is not a trivial task and you will encounter many problems on this way, and one of them is how you’re planning to store the content of the site in the database for each language.

You may perform a small research on the Web and find enough resources about it, but there is no a magic solution, you have to understand this – each solution depends on your personal requirements, size of the database, complexity of your site, etc. So we’ll discuss only major techniques. If you want to learn more, you may find additional information with a Google search.

Ok, so… there are more or less 4 popular databases schemas for multilanguage website.

  • Column Approach
  • Multirow Approach
  • Single Translation Table Approach
  • Additional Translation Table Approach

1. Column Approach

This solution is the simplest one and basically it creates an additional column for each text (each language) that needs to be translated (there is may be a number of such columns in your table, like: title, name, description etc.). Below the example for such table in MySQL:

 

Now, the way you would query it is also simple enough. You may do it by automatically selecting the right columns according to the chosen language:

Advantages:

  • Simplicity – easy to implement
  • Easy querying – no JOINs required
  • No duplicates – doesn’t have duplicate content (there is only one row for each record and only the language columns are duplicated)

Disadvantages:

  • Hard to maintain – works in easy way for 2-3 languages, but it becomes a really hard when you have a lot of columns or a lot of languages
  • Hard to add a new language – adding new language requires schema changes (and special access rights for db user) for each table with multilanguage content
  • Store empty space – if not all translations are required (e.g. at some places default language should always be used) it may cause redundant data or empty db fields
  • Need to build the watch – what column you are working with depending on the language

2. Multirow Approach

This solution is similar to the one above, but instead of duplicating the content in columns it does it in rows. Below the example for such table in MySQL:

 

Lets check now, how we could query it. Generally the idea is to do it by automatically selecting the right rows according to the chosen language:

Advantages:

  • Simplicity – easy to implement
  • Easy querying – no JOINs required

Disadvantages:

  • Hard to maintain – every column that is not translated must be changed in all rows for each language. e.g changing the price for single product requires repeating of this operation for all languages
  • Hard to add a new language – requires repeating insertion operation for each language (cloning the record for default language)
  • Duplicate content – you will have a lot of duplicate content for all the columns that are not translated

3. Single Translation Table Approach

This solution seems to be the cleanest one from database structure perspective. You store all texts that need to be translated in a single translation table. It is more suited for dynamic websites and which have a large number of languages or which intend to add a new language in the future and want to do it with ease. Below the example for such database schema in MySQL:

Now lets check how we could query it.

Advantages:

  • Proper normalization – seems like clean, relational approach
  • Ease in adding a new language – doesn’t require schema changes
  • All translations in one place – readable/maintainable database

Disadvantages:

  • Complex querying – multiple joins required to retrieve correct product description
  • Hard to maintain – overcomplicated querying on all operations: insertion, removing and updating
  • All translations in one place – one missing table leads to global problems

4. Additional Translation Table Approach

This is a variation of the above approach and it seems to be easier to maintain and work with. Let’s check why: for each table that stores information that may need to be translated an additional table is created. The original table stores only language insensitive data and the new one all translated info. Below the example for such database schema in MySQL:

Here the example of how we could query it.

Advantages:

  • Proper normalization – seems like clean, relational approach
  • Ease in adding a new language – doesn’t require schema changes
  • Columns keep there names – doesn’t require “_lang” suffixes or something else
  • Easy to query – relatively simple querying (only one JOIN is required)

 Disadvantages:

  • May double the amount of tables – You have to create translation tables for all your tables that have columns that need to be translated

NGUỒN : http://www.apphp.com/tutorials/index.php?page=multilanguage-database-design-in-mysql

Multilanguage Database Design in MySQL
4.5 (90%) 8 votes
Từ khóa : ,