Building a string translation database for multiple (in-house) projects

Question

At our company we have an existing translation ms-sql table wich stores strings like this:

Id |     Key     | Language | Value 
 1 | hello-world |  nl-BE   | Hallo Wereld
 2 | hello-world |  en-GB   | Hello World

There are 3 languages in the system and I expect this to grow to a maximum of about 10 in the future

This table is read by multiple very different projects (about 60 projects, mostly websites/web applications and some web services), that each open a database connection to the translation database, cache the translations

Feedback from the front-end devs is that our UIto input or modify translations 's biggest downside is that they cannot know what project uses what strings.

They sometimes modify strings not knowing they are breaking 7 projects with it.

Now they just have to type something like this.Translate("Hello World") and the system takes care of the rest.

I could ofcourse force them to something like this.Translate("Hello World","AwesomeApplication1") but that seems like it's going to require quite a lot of refactoring across the many many projects.

How would you go about providing this solution? How would you, as a dev, provide the "project name" to the translation? How would you store this in the database?

Important note: the translation re-use is the whole point of the centralised database, so scoping translations to one project by going

1|hello-world|nl-BE|Hallo Wereld|MyAwesomeApplicatoin1
5|hello-world|nl-BE|Hallo Wereld!|MyAwesomeApplicatoin2

is not really a wanted option.

I'd prefer something like :

1|hello-world|nl-BE|Hallo Wereld|MyAwesomeApplicatoin1,MyAwesomeApplicatoin2

or a foreign key equivalent of just putting the names in the table.

UPDATE

Based on the advise to normalize the database I have come up with something like this so far:

//this allows me to distinquish if translations where added by developer or by translator

UPDATE2: added edmx instead of text. If people are interested I could github the WCF project i'm wrapping this concept in so other people can test and use it.

Answer 1

Preliminary note #1: You are not telling us how the translations are maintained at the moment

Preliminary note #2: Your database is not normalized. Whatever the solution you're going to take, first normalize your database. You run into terrible maintenance problems later if you don't do that now

This is what I would do.

1. Rewrite your translate call so that it carries a program ID back to the server

2. The back end translator will put the string in the database table if it does not yet exist, and will tag it with the program id

3. If the string already exists, it will only be updated if the program id matches the original program id with which the string was created. If not, return a conflict notification.

Variations:

• You could use a 'developer ID/ translator ID' instead of a program ID. I consider that better because there are people that know a foreign language and those that think they know. Only the first group has modification rights.

• You might want to store the IDs of all programs that use the string in the DB, so that you know which programs conflict.

• You could extend this 'ownership' think to each individual language: one person can do English, the other Dutch.

• Once your database is normalized you add build complexity like "Program A is in Languages 1,2,3; B is in 3 and 5"

I also suggest you write a separate 'translation maintenance' program that will show you missing translations etc. I once did that with 2-level authorization: each translation has to be vetted by a second person (usually a native speaker).

Answer 2

Since they are referencing a 'this' ... you can assign the project name once to 'this' (via the constructor, for example) and the interface to the translation functions wouldnt change for the coders. Under the hood, it just adds the project name to the database query. Alternatively, you can provide 'this' a means of knowing the project name on its own. Its really going to depend on how you have your classes structured.

For storage, you can do something like:

1 ! hello-world ! nl-EN ! Hello World  ! *
2 ! hello-world ! nl-EN ! Howdy, World ! CowboyApp
3 ! hello-world ! nl-EN ! Arrgh        ! PirateApp

Use a wild card to apply a general translation to all apps, but the specific app name when you want to override a translation for a particular app. This will keep the duplications down to a minimum.

To see what program is using which translations, you now know this - if you dont want to go through and manually gathter that information, you can log the translation requests.

Answer 3

If all your projects are written in C#, and all translator calls look like this

this.Translate("hello-world")

where "hello-world" is the key in your translation table, it should not be too hard to write a small source code scanner using a regular expressions to find all translator calls and assign the keywords the corresponding project names. This way, you don't have to change any of your existing code or translator interfaces.

Depending on the real structure of your program, alternatively it may be easier to extract that information from the IL code of your assemblies. I did something very similar some time ago, also for translation purposes, using this example code to parse assemblies.