Storing translations in a database: The right way!

When you are creating a website (or a webshop) targeting multiple countries and languages, you want to provide your visitors with localized content. For example in a webshop where you want to manage your products, some properties of the products are language invariant but others have to be translated. Because you keep all these products in a database you want to find an efficient way to store this information. Of course there are lots of ways to do this. There are several things we need to think about when storing translations.

You don't want to have translations shattered all over your database

You don't want to edit your database in case a new language has to be supported

You want an efficient way to get the translated objects out of the database

Keeping these rules in mind, I decided to find a way to keep track of localized content. Lets see how I did this!

The setup

In this example I won't be creating a website to illustrate the issue. Instead I have just created a console application which will add translated content to the database and retrieve the localized information. Using this example you will get the idea of writing and reading localized content to and from the database, and you will be able to implement it anywhere you like!

I will start off with storing movies in a database and providing the localized content for user of different languages (think IMDB but very very basic!).

Defining our object

Actually we will have to define multiple objects to represent our front end (translated), and back end (with multiple translations).

​I have created a simple movie class which has an Id, Duration, Director, Title and Description. Now if we take a closer look at these properties, we can see that some of them are translatable like Title and Description, but others are not (Id, Duration & Director). So when we want to save this object to the database we will have to separate the translatable and non translatable properties.

Back end (Translatable)

Well have to split up our object into translatable properties and non translatable properties. Something like this:

This Entity will always have a list of translatable details. One for each language in which the entity should be available. I have also defined a method GetTranslatedEntity that has to be implemented by each entity.

Abstractions

I have created an abstract class for our ITranslatableDetails because we will need this in our repository later.

Our entity will derive from the abstract class EntityWithTranslatableFields and I have supplied MovieFrontEnd as our translated Entity and the MovieTranslatableDetails as our translatable details for this entity.

I have made this interface generic so that we can reuse it later. All we have to do is specify for which entity and translated entity we want this repository to work for. Apart from that I have created a few methods to create and read items from the database.

Now that we got our interface set up, lets create the actual repository. Again, I first created an abstract class as our base repository so that we can reuse this later.

I won't go over this code line by line because it is easy to understand. Basically this is what happens:

Read the seeded movie in English

Read the seeded movie in Dutch

If a second movie does not exist, create a second movie in English and Dutch

Read all movies in English

Wait to close the program

This is the output of the program to confirm that it actually works:

It works, now what?

You might say yes it works, but it was a lot of work to get here. And you're 100% right! It was a lot of work to get to this point, but from now on it is really easy to create new entities with translatable fields! Lets do the test!

Implementing a new entity

Lets use something completely different class like "Shirt" to illustrate how easy and flexible this approach really is!

5. Create the repository

6. Profit!

That's it, Finished! Go and enjoy a well earned beer with the time you saved!

Wrapping up

As you can see keeping translations in the database can be hard at first glance. But once you create a clean structure around it, and you make sure the whole thing is reusable, it's easy as pie! Using this approach you can safe yourself lots of time in implementing a multilingual website or any other application!

I hope you have enjoyed reading this, as much as I did writing it! And if you have any questions or suggestions please feel free to comment below!