CODEWITHSUNDEEP
.netsqlvb.nettranslationglobalization
Phil Murray
Phil Murray

Reputation: 6554

Translate database strings

I have an application written for the UK market that currently only supports English (UK) and a client wants to deploy part of the application out to a non UK site and syncronise the data back to the UK HQ.

I can convert the application labels and messages using resource files and the local culture information but was wondering how you would convert the database bound data.

E.G. Here is the HQ based table 

tblFault
ID ; Description
1 ; Not Functional
2 ; Build Fault
3 ; Leak
4 ; Aesthetics
5 ; Testing

If I was to translate the data to the non UK language I could just replace the descriptions but this would cause some problems if the data is unsyncronised?

Should I extend the table with another column for additional language and then alter the selection using the local culture?

tblFault
ID ; Description-EN ; Descrption-US ; Description-DE etc
1  ; Not Functional ;               ;
2  ; Build Fault    ;               ;
3  ; Leak           ;               ;
4  ; Aesthetics     ;               ;
5  ; Testing        ;               ;

What method would you recommend?

Thanks

Phil

Upvotes: 5

Views: 178

Answers (3)

Heinzi
Heinzi

Reputation: 172220

Since you have a 1:n relationship between faults and their descriptions, the cleanest solution would be to create a subtable:

tblFault
--------

FaultID ; some other fields
      1 ; ...
      2 ; ...
      3 ; ...
      4 ; ...
      5 ; ...


tblFault_Description
--------------------

FaultID ; lang ; Description
      1 ; en   ; Not Functional
      1 ; de   ; Funktioniert nicht
      2 ; en   ; ...

Upvotes: 5

taylonr
taylonr

Reputation: 10790

That's definitely one approach.

Another approach that I've used in situations like this before was to create a "LanguageId" column.

Typically I would have something like this:

StringId,  LanguageId,    Description
1            0              Hello
1            1              Hola
1            2              Bon Jour

This allow me to write a query that if string 35 (for example) doesn't have the language I'm looking for, I could still provide English. With the thought being that something is better than nothing.

The downside to this approach is composite primary keys, and some join logic.

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78423

Something more like so, in all likelihood:

tblFault
ID ; Lang ; Description
1  ; EN   ; Not Functional
...

Upvotes: 0

Related Questions