9

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.

  • Ty for normalization. voted q and a. I believe, a spring java github would help. – tgkprog Dec 25 '14 at 16:20
5

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).

  • 1
    I updated my question based on your ideas – Mvision Feb 28 '13 at 17:12
5

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.

  • The idea of moving it to the 'this' class seems good to me, I think i'm going to move the project name to the web/app.config file and read it from there. Do you think that the wild card/project-names idea is scalable to something like 4000 translations? – Mvision Feb 28 '13 at 16:58
  • 3
    As an enhancement, a trigger could be added to the database or the DBAL that updates "subscribed programs" column whenever a translated word is read. – user53019 Feb 28 '13 at 17:08
  • How could the database trigger ever know the project name? (We're using entity framework 4) – Mvision Feb 28 '13 at 17:14
  • @Mvision I dont see why not - volume shouldnt be much of an issue. If a lot of the app-specific translations are simply inserting the app name, you can use constants to cut down on dups as well. – GrandmasterB Feb 28 '13 at 17:19
  • nvm I misunderstood. yeah a trigger could do, and wouldspeed things up compared to checking and updating that stuff trough EF on read.... good call – Mvision Feb 28 '13 at 17:31
1

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.

  • I used to like this kind of solution. But it would require all 60 projects (some not even mine) to be fully tested before going to production with this new system. The suggested solution to move the project name into the calling code works fine and will cause a lot less hassle, thanks anyway! – Mvision Feb 28 '13 at 18:07
  • 1
    @Mvision: I think the opposite is true. If you determine the project name for the translations at run time, you have to make sure every web page with every translation for all your 60 projects has to be called once to make sure you logged them all. Statically scanning the source or the assemblies, however, gives you a complete list without the need of any tests or running the whole system. – Doc Brown Feb 28 '13 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.