Language Dependant Database Design

Hi,

This is very similar to the idea of designing a table. Say you have a table with columns Value_EN and Value_FR, what happens when you need german language, requires a new column Value_GR, and then a new language another new column Value_VN, and so on. Then when you want to create a query that combines EN, FR and VN values. I'm sure your familar with this idea of normalisation where instead of the multiple similar columns you have instead, in this example, a Value column and a Language column.

Rather than.....

ID Value_EN Value_FR Value_GR Value_VN

1 10 null null null

2 15 null 29 null

You end up with...

ID Value Language

1 10 EN

2 15 EN

3 29 GR

I think your solution abstracts the inital problem from the column level up to the table level but it's still the same problem. When a new language is added you need a new table. Eventually the database will be large and will always need changing when a new language is added. Instead I would just go with this.

tblCountry

{

ID: PRIMARY KEY

VALUE: Language Independent Code

DISPLAYVALUE: Language Dependant Value Displayed To The User

}

It will result in a much larger table and all your languages are combined but you can create views to basically re-create the individual tables you originally wanted. i.e. A view called fr-FR for example, the culture code used by your application. You can use that to determine which view to use. Or you create stored procedures that take the culture code as a parameter. The advantage is the stored procedure would be simple as it only need to query on one table.

These are just my thoughts, hopefully someone else will post there thoughts also, and some correspondance can happen to get you the best approach.

DerekSmyth at 2007-9-26 >

By specifying the language-specific table in your query you are effectively passing the culture to the database. You have to compare the pros-cons of the two solutions :

• Manageability: The multi-table solution is harder to code and manage. You need separate queries for each language. Should you create a UI to manage the values, it will need to know at some point which table to use.
• Extensibility: The multi-table solution just isn't very extensible. Adding a new language will require a lot of changes.
• Performance: The multi-table solution requires seeking two indexes and a join. The single-table solution requires a single index seek. The multi-table solution also prevents you from using a single stored procedure or parameterized query to load the values. Either you use dynamic SQL or you create one procedure/query per language

PanagiotisKanavos at 2007-9-26 >

Udi is correct, and I should have mentioned the "Why store it in the database?" question in the first place. That's what comes from answering questions after 2am!

Indeed, the database is a very bad place to store static strings like control labels text and user messages. It is far better to use .NET's localization support to localize forms and messages. For one thing, the satellite dlls don't disappear if the connection to the database is lost. For another, in-memory access is quite a bit faster that database access over the network. Plus, it requires no code to work.

There are some cases where you need to store localized strings in the database. In countries with multiple official languages it is customary to require some text data to be kept in all the official languages, e.g. product names. In this case you could store each translation in a language specific field in the product table, create a "satellite" table for each language or use one table with all product translations. The best strategy depends on the flexibility you need (would you support a new language in the future?), performance considerations (joining with other tables could cause more blocking) and how you intend to manage the translations.

PanagiotisKanavos at 2007-9-26 >