Database Design for Multilingual Applications

Developing an application or website to support multiple languages poses a number of challenges.  One of the challenges is coming up with a design for the underlying database.  There are a nunber of approaches available, each with it's own pros and cons.

The approach I decided to use was to split each table containing multilingual data fields in to two separate tables. 

The first table stores the primary key and any non language dependenant data fields such as telephone numbers, post codes, balances, etc.

The second table stores the primary key from the first table along with a field indicating the language which together make the primary key in the second table.  All the language dependant data fields then follow.  This results in a one to many relationship between the first and second tables.

tblSupplier

1. intSupplierID (auto_increment, primary_key)
2. dtmCreated
3. strPhoneNumber
4. ...other language independant fields....

tblSupplierLng

1. intSupplierID (foreign key and part of primary key in this table)
2. strLanguage (2 digit code to refer to language easily - EN,JP,CN,DE etc, part of primary_key)
3. strSupplierName
4. ...other language dependant fields....

We can then obtain all the supplier details in Japanese for instance with a simple join -

1. select  (our fields here...)
2. from tblSupplier s inner join tblSupplierLng l
3. on s.intSupplierID=l.intSupplierID and strLanguage='JP'

Using Views

This model is fine when we work with data from one type of entity (Supplier), but say now we want to get a list of products for a given supplier in a specific language, where the products may be grouped in to categories (with each category translated as well). Our tables in our joins can start to add up quickly.

In this instance we can create a view which joins the two tables for us and by using the views in the queries our joins are halved and almost resemble the single language scenario.

1. create view Supplier as 
2. select ,
3. from tblSupplier s inner join tblSupplierLng l on s.intSupplierID=l.intSupplierID

The one drawback with this approach is to create a view the field names must be unique so unfortunately we can't just do select * from for our view, and instead have to manually specify each name. Each time we add/modify a field in either table we need to manually modify the view.

There is however an easy way to create the sql we need for the view join, and that's using the Information Schema and a prepared statement.

Creating our View SQL Using the Information Schema

With a few lines of sql we can have mySQL do the hard work of creating all the field names for our join.  Following the same example above we issu the following -

1. set @sql=concat('create view Supplier as
2. select s.*,',(select replace(group_concat(COLUMN_NAME),'intSupplierID,','') from
3.  information_schema.columns where TABLE_NAME='tblSupplierLng'),
4. ' from tblSupplier s inner join tblSupplierLng l on s.intSupplierID=l.intSupplierID');
5. prepare stmt from @sql;
6. execute stmt;

We could then round it off nicely by creating a procedure which takes the view and table names as the paramaters and does it for us.

Using a Procedure to Build the Views

We will declare the procedure with 3 parameters - the name of the veiw we want to add/replace, the name of the main table (language independant) and the join table we need to get the language dependant data.

We will assume that the main table only ever uses a single column as the primary key and that all other field names in both tables are unique.

So our proecedure declaration would start -

1. PROCEDURE `BuildView`(IN `viewName` VARCHAR(100), IN `mainTable` VARCHAR(100), IN `joinTable` VARCHAR(100))

The proecure body would then be implemented as follows -

1. BEGIN
2.   declare keyColumn text;
3.   set @qry=concat('drop view if exists ',viewName);
4.   prepare stmt from @qry;
5.   execute stmt;
6.   select COLUMN_NAME into keyColumn from information_schema.columns
7.   where TABLE_NAME=mainTable and COLUMN_KEY='PRI';
8.   set @qry=concat('create view ',viewName,' as select m.*,',
9.   (select replace(group_concat(COLUMN_NAME),concat(keyColumn,','),'') from information_schema.columns
10.   where TABLE_NAME=joinTable), ' from ',mainTable,' m inner join ',joinTable,
11.   ' j on m.',keyColumn,'=j.',keyColumn); 
12.   prepare stmt from @qry;
13.   execute stmt;
14. END

First we declare a variable to hold the name of of primary key column (intSupplierID in the example).
Next we prepare the SQL for a drop statement using our view name and then execute that to drop the view if it exists already.
Now we get the name of the primary key field in the main table and store it in our keyColumn variable.
Next we start to prepare the sql for the create view.
We do this by using the concat function so we can join literal strings with variables and query results.
We use a subquery to find all column names in the join table and then we replace the keyColumn name with nothing so it won't appear in the string that gets concatenated.
Finally we prepare and execute the query.