Document home

Database

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 supports 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. Soluling localization tool and service support databases.

The end of the document contains links to get the full source code of the samples.

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 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 looks like this:

Id Name FieldPlayers
0 Soccer 10
1 Ice hockey 5
2 Basketball 5

As you can see, it contains the 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:

Id Name FieldPlayers
0 Fußball 10
1 Eishockey 5
2 Basketball 5

Japanese Sport table looks like this:

Id Name FieldPlayers
0 サッカー 10
1 アイスホッケー 5
2 バスケットボール 5

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:

Id Name FieldPlayers
0 Soccer 10
1 Ice hockey 5
2 Basketball 5

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:

Id Language Name FieldPlayers
0 en Soccer 10
1 en Ice hockey 5
2 en Basketball 5

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:

Id Language Name FieldPlayers
0 en Soccer 10
0 de Fußball 10
0 ja サッカー 10
1 en Ice hockey 5
1 de Eishockey 5
1 ja アイスホッケー 5
2 en Basketball 5
2 de Basketball 5
2 ja バスケットボール 5

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:

RowId Id Language Name FieldPlayers
0 0 en Soccer 10
1 1 en Ice hockey 5
2 2 en Basketball 5
3 0 de Fußball 10
4 1 de Eishockey 5
5 2 de Basketball 5
6 0 ja サッカー 10
7 1 ja アイスホッケー 5
8 2 ja バスケットボール 5

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.

Id Name Name_de Name_ja FieldPlayers
0 Soccer Fußball サッカー 10
1 Ice hockey Eishockey アイスホッケー 5
2 Basketball Basketball バスケットボール 5

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:

Id Name
0 Fußball
1 Eishockey
2 Basketball

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

Id Name
0 サッカー
1 アイスホッケー
2 バスケットボール

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.

Feature Create localized
database files
Row
locali​zation
Field
locali​zation
Table
locali​zation
Works with any database - yes yes yes
A database can be localized without changing the data structure yes - - -
Can be implemented without redundant data - - yes yes
New languages can be added without changing the data structure yes yes - -
Amount of SQL statement change you have to do in your code minimal little more more

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 databases 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 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 Namee, FieldPlayers, Goalie, Descriptione, Origine 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, you 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:

Method Description
Create localized database files Soluling creates localized database files.
Row localization Soluling adds the language variant rows.
Field localization Soluling populates the language variant fields.
Table localization Soluling populates the fields in the language variant tables.

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.

Before After

Copy before

No fields have been selected.

Copy after

Name, Origin and Description fields have been selected and will be localized.

The Sport table contains three string fields. This table does not contain language variant fields or language fields, so we can only use 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.

Before After

Row before

No fields have been selected.

Row after

Language fiels has been marked as a language field. Name, Origin and Description fields have been selected and will be localized.

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.

Before After

Row before

No fields have been selected.

Row before

Name, Origin, and Description fields have localized fields that will be localized.

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 looks like this:

Id Name Origin Description
0 Soccer England Soccer is a sport played between two teams of eleven players with a spherical ball.
1 Ice hockey Canada
Ice hockey is a team sport played on ice, in which skaters use sticks to direct a puck into the opposing team's goal.
2 Basketball United States Basketball is a team sport in which two teams of five players try to score points by throwing a ball through the top of a basketball hoop while following a set of rules.

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

Plain ids

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.

Id Name
0 Soccer
1 Ice hockey
2 Basketball

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 id lookup table. Open the source dialog and select the Items sheet. Right-click the Sport table and select Add id name lookup.

Select lookup

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

Id name lookup dialog

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

Lookup ids

Instead of "Sport.Name.0" you will now see "Sport.Name.Soccer", that 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:

Value Description
Use original value Write the original value.
Use empty value Write an empty value. This is used only with string fields. If the field type is not a string, the original value is used.
Use null value Write a null value. If the field can not be null, then write empty value.
Do not write/change item Do not write the field (default)

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

Value Description
Always Localized rows are always added even if there are no parts translated in the database row.
If item has at least one translation

Localized rows are added if there is at least one part translated in the database row (default).

For example, if a database row contains two fields that are marked to be localized, and only one of them is localized, then Soluling adds localized rows for each language. If both are untranslated localized rows are not added.

Only if item has all translations completed

Localized rows are added only if the database row has all parts translated.

For example, if a database row contains two fields that are marked to be localized, they both have to have a value to make Soluling add localized rows. If one or both fields are untranslated localized rows are not added.

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.

Database Unicode data types Ansi data types Notes
Access

all

-

All text is in Unicode (UTF-16).

Firebird
Interbase

all

all

Stores tables in UTF-8 if the default character of a database server is set to utf8 or you specify the character set when you create a table:

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

or you can specify the character set per table or field:

varchar(50) character set utf8 not null,
MySQL all all Stores tables in UTF-8 if default character of database server is set to utf8 or you specify the character set when you create a table (e.g., CHARSET=utf8).
SQL Server

nchar, nvarchar, ntext

char, varchar, text

Unicode is used if Nxxx data type is used.

SQLite all - All text is in Unicode (either UTF-8 or UTF-16).

Data types

Soluling can localize almost all data of 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.

Field type Description
Text

Text data can be read as

  • Plain string
  • Definition defined text
  • Regular expression defined structured string
  • XML
  • JSON
  • HTML
Binary
(BLOB)

Binary data can be read as

  • Image
  • Audio
  • Video
  • XML file
  • JSON file
  • HTML file
  • Plain text file
  • Definition defined text file
  • Definition defined binary file
  • Zip file
  • Plain binary
Integer number The integer field is read as an integer number
Floating point number The floating-point field is read as a floating-point number
Boolean The boolean field is read as a boolean value

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

Field types

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:

Value Description
Flat The table node contains all items. There are no sub-nodes except the table contains structural data such as XML or HTML.
By fields The table node contains one field node for each field that has been selected to be localized. The field node contains all items of that field. Select this if you want to view data mostly by fields.
By rows The table node contains a one-row node for each row of the table. The row node contains all the items of that row. Select this if you want to view data mostly by rows.
By groups The table node contains one group node for each group of composite id (.i.e. id that contains two or more fields). The group node contains all the items of that grow. Select this if you want to view data mostly by groups. If the table does not contain composite id, then this behaves like By rows.

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.

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.

Sport table

By fields

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

By fields

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.

Change structure

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:

By rows

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.

Flat structure

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

By groups

If your table has a composite primary key, you can use the fouth 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.

By group items

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

By group options

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.

By group project

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:

Format Description
Access Access databases (.mdb, .accdb)
Firebird Firebird databases (.fdb, .gdb)
InterBase InterBase databases (.gdb)
MySQL MySQL databases
PostgreSQL PostgreSQL database
SQL Server SQL Server databases
SQL Server Compact SQL Server Compact databases (.sdf)
SQLite SQLite databases (.db)

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 subdirectories. They are:

Database Directory Notes
Access GitHub and <data-dir>\Samples\Access Sample database files are in Access 2007 format (.accdb).
Firebird GitHub and <data-dir>\Samples\Firebird Sample database files are in Firebird 2.5 format (.fdb)
InterBase GitHub and <data-dir>\Samples\InterBase  
MySQL GitHub and <data-dir>\Samples\MySQL  
PostgreSQL GitHub and <data-dir>\Samples\PostgreSQL  
SQL Server GitHub and <data-dir>\Samples\SQLServer  
SQL Server Compact GitHub and <data-dir>\Samples\SQLCE Sample database files are in SQL Server Compact 4.0 format.
SQLite GitHub and <data-dir>\Samples\SQLite Sample database files are in SQLite 3.0 format.

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 following samples:

Directory Description
Simple Study this first. A simple localization sample. Contains following sub directories:
Copy A version that uses file localization.
Field A version that uses field localization.
Row A version that uses row localization.
Composite A sample that shows how to localize a tabel that has composite primary key.
Data A sample that shows how to localize various binary data.
Image A sample that shows how to localize image data.
Sport A sample that shows how to localize a typical table. Contains following sub directories:
Copy A version that uses file localization.
Field A version that uses field localization.
Row A version that uses row localization.
RowId A version that uses row localization with id field.
Table A version that uses table localization.
SportImage Similar to Sport/Row but contains also an image columns. In addition contains Language table that contains id, native name and flag of each languages used in Sport table.
Types A sample that shows how to localized different data types such as binary data and integers.

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, there is a complete web application that uses database localization.l

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.