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.
We can then obtain all the supplier details in Japanese for instance with a simple join -
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.
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.
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 -
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.
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 -
The proecure body would then be implemented as follows -
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.