Translate database rows

Question

I have an application written for the UK market that currently only supports English (UK), and the client wants to deploy part of the application on a non-UK site and sync the data back to the UK headquarters.

I can convert application labels and messages using resource files and local culture information, but wondered how you would convert the data associated with the database.

eg. Here is the HQ based table

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

If I were to translate the data into non-British language, I could just replace the descriptions, but it can cause some problems if the data is not synchronized?

Should I expand the table with another column for an additional language, and then change 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        ;               ;

Which method would you recommend?

thank

Phil

Answer 1

Since you have a 1: n ratio between errors 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   ; ...

Answer 2

This is definitely one approach.

Another approach I used in similar situations before was to create a "LanguageId" column.

Normally I would have something like this:

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

, 35 () , , . , - , .

.

Answer 3

Something looks more like this, in all likelihood:

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