Soluling home   Document home

Database Localization and Internationalization

Your database tables may contain text or image data that should be localized. For example, you might have a product table that has a description field containing English text. If you use the database with a German application, the description text should be translated into German. This is where you use database localization. Soluling makes database localization very easy. It can localize the most commonly used databases such as Access, SQL Server, MySQL, SQLite, Firebird, PostgreSQL. Soluling localization tool and service support several database localization methods. You can localize fields, rows, tables, or complete database files. You can choose a method that suits best for your database.

• Localization method
• Internationalization
• Select fields to be localized
• Context value
• How data is updated
• Unicode data
• Data types
• Data structure
• Supported databases
• Samples
• Configuring sources

The end of the document contains links to get the full source code of the samples. After reading this document, we recommend reading a tutorial about how to use Soluling.

Localization method

There is no single superior way to localize a database. Instead, there are several different methods. You have to choose the method that suits you best. Soluling supports the following database localization methods.

	Method	Description
	File localization	Soluling creates localized database files that have identical structure and data as the original database except for the values of the selected fields that have been translated.
	Row localization	Soluling adds the language variant rows. As a result, each row is copied once for each language.
	Field localization	Soluling populates the language-specific fields.
	Table localization	Soluling populates the fields of the language-specific tables.

Soluling never changes the table structure. It only updates the values in the localized fields and tables, adds localized rows, or creates a localized database file. You have to modify the database structure to prepare it for localization.

File localization

In this method, Soluling creates a localized database file for each language. The advantage of this method is that it does not require changing the table structure at all. The disadvantage is that it works on local databases that store databases into a single file, such as Access, SQL Server Compact, and SQLite.

Let's have a sample. We have an original Sport table that looks like this:

As you can see, it contains English text. The German version of the database has the same structure, but the text in the Name field has been translated into German. German Sport table looks like this:

Japanese Sport table looks like this:

Row localization

If it is not possible to create a localized database file or you want to have the localized values in the same table as the original, you can use row localization. The best way to look at it is by example. Our sample Sport table looks like this:

The SQL of the table is:

CREATE TABLE Sport
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  FieldPlayers INTEGER NOT NULL,
  PRIMARY KEY(Id)
);

To make it possible to store each row many times, once for each language, we have to add a language field. The field will be part of the primary index together with the id field. The internationalized Sport table looks like this:

The SQL of the internationalized table is:

CREATE TABLE Sport
(
  Id INTEGER NOT NULL,
  Language VARCHAR(10) NOT NULL,
  Name VARCHAR(50) NOT NULL,
  FieldPlayers INTEGER NOT NULL,
  PRIMARY KEY(Id, Language)
);

The table can contain multiple rows with the same id as long as the Language field has a different value. A localized Sport table looks like this after the localized rows have been added:

We recommend using IETF language tags (wikipedia) in language fields. However, you can also use Windows language and/or locale ids. The internationalized Sport table looks like this:

The SQL of the internationalized table is:

CREATE TABLE Sport
(
  Id INTEGER NOT NULL,
  Language INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  FieldPlayers INTEGER NOT NULL,
  PRIMARY KEY(Id, Language)
);

The localized table is:

If you don't want to have a combined primary key, you can use a slightly modified structure. Here the RowId field contains a unique value and is also the primary key. In addition, we have two other fields: Id and Language that specify the row and language of the row. The Id value is the same as in the original table's Id field. The localized Sport table looks like this after Soluling has updated the localized field values:

The SQL of the table is:

CREATE TABLE Country
(
  Id INTEGER NOT NULL,
  ItemId INTEGER NOT NULL,
  Language VARCHAR(10) NOT NULL,
  Name VARCHAR(50) NOT NULL,
  FieldPlayers INTEGER NOT NULL,
  PRIMARY KEY(Id)
);

The advantage of the row localization is that you do not have to change the structure if you add a new language. The disadvantage is the everything is multiplied, and the size of the database is directly related to the number of languages. Also, if you update the database on the run time, you have to do the same updates for each language row. This makes the modification of the table more difficult. However, if you only read the table, then row localization is a very good solution.

Field localization

If you don't want to have multiple rows but want to have one single row for each item, you can use field localization. Here you add language field variants.

If you want to localize the Sport table to German and Japanese, add localized Name fields for both languages.

CREATE TABLE Sport
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Name_de VARCHAR(50) NOT NULL,
  Name_ja VARCHAR(50) NOT NULL,
  FieldPlayers INTEGER NOT NULL,
  PRIMARY KEY(Id)
);

The advantage of the field localization is that you only have one row for each item. It is easier to modify the table. The disadvantage is that you have to modify the structure (e.g., add language variant field) and also modify the SQL you use to access data.

Table localization

The final method is similar to field localization, but instead of adding the language variant fields in the original table, we add them into a language variant table. If you want to localize the table to German and Japanese, add a localized Sport table for each language. These tables do not contain all fields but only those fields that you want to localize (e.g., Name).

CREATE TABLE Sport_de
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY(Id)
);


CREATE TABLE Sport_ja
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY(Id)
);

The localized German Sport table looks like this after Soluling has updated the localized field values:

The localized Japanese Sport table looks like this after Soluling has updated the localized field values:

The advantage of the table localization is that you do not have to modify the original table. The disadvantage is that you have to add a new table every time you add a new language. Also accessing of the data get more complicated because you have to read data from both original and localized table.

Comparing methods

Each method has its advantages and disadvantages. You must choose the method that best suits your needs. The following table compares different localization methods.

Internationalization

Databases do not have a resource concept as software projects do. This means that localized data must be placed in the standard database structure, such as fields, tables, or rows. Soluling supports several database localization methods. No matter what method you use, you have to either change your database structure or your code or both.

The following C# code creates a connection for the Access database and gets Sport table.

using (var connection = new OleDbConnection(​"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sport.accdb"))
{
  connection.Open();
  string sql = String.Format("SELECT Name, FieldPlayers, Goalie, Description, Origin FROM Sport");
  OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
}

This code works for one language. If we localize the database, we need to modify the code of how we access the database, and we have to modify the database structure too.

Create localized database files

If you select this method, you don't have to modify the structure of your database. However, you have to change the code you access the database. This is because instead of one database, you now have several databases, one for each language. Depending on your active language, you select the right database.

using (var connection = new OleDbConnection(String.Format(​"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}\\Sport.accdb", Resources.GetCulture())))
{
  ...
}

We added the "{0}\\" part into the database name to use a language-specific database that locates on a subdirectory. Resources.GetCulture() returns to the active language code. All your original SQL can be used with a localized database too. For example, if the active language is German, the data source will be

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=de\\Sport.accdb

Row localization

When you use this method, you have only one database, but rows have been localized, so there are several versions of each row. This is why we have to modify the SELECT statement to give only those rows that match our language.

using (var connection = new OleDbConnection(​"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sport.accdb"))
{
  connection.Open();
  string sql = String.Format("SELECT Name, FieldPlayers, Goalie, Description, Origin FROM Sport WHERE Lang='{0}'", Resources.GetCulture());
  OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
}

We added the "WHERE Lang='{0}'" part to the select statement to get only those rows that match the language. For example, if the active language is German, the SQL will be

SELECT Name, FieldPlayers, Goalie, Description, Origin FROM Sport WHERE Lang='de'

Field localization

When you use this method, you have only one database, but tables contain localized fields, one for each language. This is why we have to modify the SELECT statement to give only those fields that match our language.

using (var connection = new OleDbConnection(​"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sport.accdb"))
{
  connection.Open();
  
  string lang = Resources.GetCulture();

  if (lang != "")
    lang = '_' + lang;   

  string sql = String.Format("SELECT Name{0}, FieldPlayers, Goalie, Description{0}, Origin{0} FROM Sport", lang);
  OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
}

We added the "{0}" part into each field that has been localized to get localized the field instead of the original one. For example, if the active language is German, the SQL will be

SELECT Name_de, FieldPlayers, Goalie, Description_de, Origin_de FROM Sport

Table localization

When you use this method, you have only one database, but each table contains localized sub tables that contain the localized fields. This is why we have to modify the SELECT statement to select localized fields from the localized table instead of the main table.

using (OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sport.accdb"))
{
  connection.Open();

  string lang = Resources.GetCulture();

  if (lang != "")
    lang = '_' + lang;

  string sql = String.Format("SELECT Sport{0}.Name, Sport.FieldPlayers, Sport.Goalie, Sport{0}.Description, Sport{0}.Origin FROM Sport, Sport{0}", lang);
  OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
}

We added the "{0}" part into each field that has been localized to get localized the field instead of the original one. For example, if the active language is German, the SQL will be

SELECT Sport_de.Name, Sport.FieldPlayers, Sport.Goalie, Sport_de.Description, Sport_de.Origin FROM Sport, Sport_de

Editing tables

Most multilingual tables are read-only tables. This means that your application only reads data from tables but does not change or add new data. If you need to change or add data, you have to make sure that the same changes get performed for all language rows, tables, or tables. If you do not perform the same changes for all language elements, your database might become inconsistent.

Select fields to be localized

When you create a project that contains a database, you first select the database. After that, the Project Wizard shows the Properties sheet that is used to select the tables and fields that you want to localize. How selection is made depends on the localization method your table uses. Soluling supports four localization methods. They are:

Create localized database files

This is the easiest way to localize database. You don't have to prepare your database at all. Just select the database and double click the field nodes that you want to localize.

The Sport table contains three string fields. This table does not contain language variant fields or language fields, so we can only use the Create localized database files method. Double click the Name, Origin, and Description fields. Soluling tries to detect the localization method and automatically select the default fields. This is why when you start Project Wizard with Sport.accdb, you will automatically get selections shown in the "After" image.

This method can only be used with local databases that use a file to store the database. Such databases are Access, SQLite, and SQL Server Compact.

Row localization

To use this localization method, a table has to contain a language field that contains language codes.

The Sport table contains four string fields. The Language field contains the language code. Other fields contain strings that need to be localized.

Double click the Name, Origin, and Description fields. Soluling tries to detect the localization method and automatically select the default fields. This is why when you start Project Wizard with Sport.accdb, you will automatically get selections shown in the "After" image.

Field localization

Your table contains the original value field and one or more language variants of each field. For example, if the original value is in the Name field, the German value is in the Name_de field. The names of the language variant fields can be anything. However, if you add a language code into the end of the field name (Name -> Name_de or NameDe) NewtTool will automatically detect the field as a language variant and moves it to a sub-node of the original field. If the language variant fields are not automatically detected, you have to drag them into a sub-node of the original field.

The Sport table contains three original string fields: Name, Origin, and Description. Each of them contains three language variants. Soluling tries to detect the localization method and automatically select the default fields. This is why when you start Project Wizard with Sport.accdb, you will automatically get selections shown in the "After" image.

Table localization

This is like field localization, but instead of having language variant fields, you have language variant tables. Drag the language table into the sub-nodes of the original table.

Context value

When Soluling extracts an item from a database to be localized, it assigns an id into that. The id is a part of the context value. The value depends on the value of the primary key or resource field of the database. In many cases, this is a number or some other value that does not give any information for the translator. Let's have a sample. We have a Sport table that looks like this:

The Id field specifies the unique id that Soluling uses when assigning id for rows.

As you can see, the context part contains numeric ids. If this is all right for you, then you a good to go. However, numeric ids do not provide much information for localization. If the database contains a relation that provides a textual description for the id, it could be used as a context value instead of the numeric id. Let's add the following Resource table into the database.

Here the Id field has a foreign key for the Id field in the Sport table. We can easily specify Soluling to use the Resource table as an id lookup table. Open the source dialog and select the Items sheet. Right-click the Sport table and select Add id name lookup.

The following dialog appears. Select the table, fields, and click OK.

Now Soluling prompts to scan the project. Accept it, and after a rescan, the context values will change.

Instead of "Sport.Name.0" you will now see "Sport.Name.Soccer", which gives the translator much more information about the item.

How database fields and rows are updated

When Soluling performs a build, it writes translations into your database tables. If a value has a translation, then Soluling uses it. If it does not have a value, then what is written depends on the source settings. When using field or table localization, the source settings specify what value to write. Possible options are:

When using row localization, there is an option that controls if localized rows are added to the table. Possible values are:

Unicode data

Most databases can store Unicode string data. Unfortunately, most databases can also store Ansi string data. Even Soluling can perfectly handle both strings data types; it is recommended that you use Unicode data type whenever your tables contain strings data in more than one script. For example, if you only use English, German, and French that all use the same Western Ansi code page, Unicode is not that important. However, if you add Japanese fields or rows that use Shift-JIS Ansi encoding, then you are dealing with multiple Ansi encodings. This is something that most databases can not handle. For example, if a database table is created to store Western Ansi code page strings, it can not store Japanese code page strings. We recommend that you always use Unicode when creating tables that should be localized.

The following table shows Unicode support of supported databases.

create database 'employee.fdb' default character set utf8;

varchar(50) character set utf8 not null,

Data types

Soluling can localize almost all data in your database. In most cases, it is string and image fields that you want to localize. However, you can also localize integer, floating-point, boolean, and binary fields. If a string or binary field contains structured data such as XML or JSON, Soluling can localize them too.

When you create a project or add a new source, Project Wizard shows the Properties sheet that lets you select the fields that you want to localize. There is Visible field types group box at the bottom of the dialog. The group box contains checkboxes for all field types that can be localized.

Check those field types that you want to be shown on the above tree. Then check those fields in the tree that you want to localize. Both text and binary fields can contain several different data formats. In most cases, Soluling automatically detects the data format. It can detect image, audio, video, XML, HTML and ZIP data. If it can not detect the format, you have to specify it. Right-click the field in the database tree and choose the right format.

Data structure

Soluling's project file is structural. It contains one or more sources. Each source contains rows and nodes. Each node can contain more rows and nodes. This makes the project structure as a tree. When we scan a database table, it is possible to create three different structures:

It is totally up to you what structure you use. The best structure depends on your table and your needs. If you want to view data as rows, select By rows. If you want to view data as fields, select By fields. You can change the structure at any time without losing any translations.

• By fields
• By rows
• Flat
• By groups

Let's have an example. We have a Sport table that contains information about team sports. It contains three rows: soccer, ice hockey, and basketball. Each row contains three fields that we want to localize: Name, Origin, and Description. Here is the table data.

By fields

When we create a Soluling project, the default structure will be by fields. Soluling desktop looks like this:

As you can see, the table node (Sport) contains five sub-nodes, one for each selected field. If you select the Name node, the grid shows only those items that came from the Name field. This structure makes it easy to view all data in the same field.

By rows

We can change the structure. Right-click Sport.accdb and choose Properties. The source dialog appears. Select the Options sheet to edit the structure. The Options sheet contains the Data structure radio-group.

Click By rows and click OK to close the dialog. Now Soluling changes the structure of the database data. The desktop looks now like this:

The field nodes are now gone, and instead, there are row nodes (Soccer, Ice hockey, and Basketball). If you select the Soccer node, the grid shows all data from all fields of that row. This structure makes it easy to view all data of the same row.

Flat

Another structure is the flat structure, where all data is stored in the table node, and there are no sub-nodes.

If your database is very small, this structure could be handy, but a flat structure makes it difficult to navigate the database items if the database gets larger.

By groups

If your table has a composite primary key, you can use the fourth structure called by groups. When using it, you can select one of the primary key fields to be a group id and Soluling groups all rows having the same value in that field beneath the same group node. The following screenshot contains items of a table that uses composite keys. The key contains two fields: GroupId and ItemId. We will use GroupId as the group.

Choose the Options sheet and set the data structure to By groups.

Now the project has been structured by the GroupId field. The project looks like this. You can see two groups, country and sport in the project tree under the Composite table.

See the sample in <data-dir>\Samples\SQLite\Composite directory.

Supported databases

Soluling can read, write, and localize many kinds of databases. Supported databases are:

If you need support for a database not listed in the above table, please contact us.

Samples

Soluling contains several database localization samples. Each supported database has its own samples on a database-specific subdirectory. They are:

If you use a server database, use SQL files (.sql) in the sample directories to create the sample database table. If you use a file database that is older than the provided sample database file, use SQL files (.sql) in the sample directories to create a compatible database file.

Each sample directory contains the following samples:

Depending on the database type, some of the samples may not exist. Some databases have more samples. Server-based databases do not have Copy samples because file localization is not supported by server databases. Access and SQLite contain most samples.

In addition, we have a sample web application that uses database localization.

Configuring SQL Database Localization

You can configure how to localize your database by selecting the item in the project tree, right-clicking, and choosing the Options menu. A source dialog appears that lets you edit the options. This source uses the following option sheets.

• Language
• Database
• Items
• Options
• Encodings
• Multilingual options
• Output