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 :
or a foreign key equivalent of just putting the names in the table.
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.