Storing translations in a database: The right way!

Categories Entity Framework , Globalisation
Posted in C# , Linq
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.

  1. You don't want to have translations shattered all over your database
  2. You don't want to edit your database in case a new language has to be supported
  3. 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).

Font end (Translated)

Lets start off with the front end object because it is the easiest.

public class MovieFrontEnd
{
    public int Id { get; set; }
    public TimeSpan Duration { get; set; }
    public string Director { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
}

​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:

public class Movie
{
    public int Id { get; set; }
    public TimeSpan Duration { get; set; }
    public string Director { get; set; }
}
public class MovieTranslatableDetails
{
    public string Language { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
}

To make sure we can reuse we can reuse this for other entities, I have created some interfaces and abstract classes.

Interfaces

First, an interface for the translatable part of our entity.

public interface ITranslatableDetails
{
    string Language { get; set; }
}

I have made sure that translatableDetails always have a Language property to define for which language these details are.

Next, the interface for our Entity (the non translatable part).

public interface IEntityWithTranslatableFields<out TTranslatedEntity, TTranslatableDetails> where TTranslatableDetails : ITranslatableDetails
{
    ICollection<TTranslatableDetails> TranslatableDetails { get; set; }
 
    TTranslatedEntity GetTranslatedEntity(string language);
}

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.

public abstract class TranslatableDetails : ITranslatableDetails
{
    public string Language { get; set; }
}

 

Next, the transatable fields. Because we don't want to implement GetTranslatedEntity every time we create a new entity, lets create an abstract class that implements the interface we just created.

public abstract class EntityWithTranslatableFields<TTranslatedEntity, TTranslatableDetails> 
    : IEntityWithTranslatableFields<TTranslatedEntity, TTranslatableDetails> 
    where TTranslatableDetails : ITranslatableDetails
{
    public ICollection<TTranslatableDetails> TranslatableDetails { get; set; }
 
    public TTranslatedEntity GetTranslatedEntity(string language)
    {
        var translatedEntity = Activator.CreateInstance<TTranslatedEntity>();
        var translatableDetails = TranslatableDetails.FirstOrDefault(t => t.Language == language);
 
        BeforeMapping(translatedEntity, translatableDetails);
 
        Mapper.Map(translatableDetails, translatedEntity);
        Mapper.Map(this, translatedEntity);
 
        AfterMapping(translatedEntity, translatableDetails);
 
        return translatedEntity;
    }
 
    protected virtual void BeforeMapping(TTranslatedEntity translatedEntity, TTranslatableDetails translatableDetails) { }
    protected virtual void AfterMapping(TTranslatedEntity translatedEntity, TTranslatableDetails translatableDetails) { }
}

In this class I have implemented GetTranslatedEntity. Here we create our translated entity in a few steps:

  1. We create a new instance of our translated entity.
  2. We search in our TranslatableDetails for a record for the supplied language.
  3. We do our mapping to the translated entity using automapper
  4. We return our translated entity

Note: We have to make sure we map the translatedDetails before we map our actual entity because otherwise the ID of our translatedDetails record will be used instead of the one from our actual entity. 

As you can see I have also created 2 virtual methods BeforeMapping and AfterMapping which we can override in our entity to implement some more logic if needed.

Back to our entity

To complete our entity we have to make sure we implement these abstractions we just created.

First our translatable part:

public class MovieTranslatableDetails : TranslatableDetails
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
}

I have inherited from the abstract class TranslatableDetails and added an ID property to make sure this table has a primary key.

Our actual entity will look like this:

public class Movie : EntityWithTranslatableFields<MovieFrontEnd, MovieTranslatableDetails>
{
    public int Id { get; set; }
    public TimeSpan Duration { get; set; }
    public string Director { get; set; }
}

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.

Create the database

Now that we've got our entities set up, lets create the database.

public class DemoDbContext : DbContext
{
    public DbSet<Movie> Movies { get; set; }
}

And I have also implemented the seed method of our configuration to make sure we have 1 movie to work with.

protected override void Seed(DAL.DemoDbContext context)
{
    context.Movies.AddOrUpdate(m => m.Id,
        new Movie
        {
            Id = 1,
            Director = "Sander Van Looveren",
            Duration = TimeSpan.FromHours(2),
            TranslatableDetails = new List<MovieTranslatableDetails>
            {
                new MovieTranslatableDetails
                {
                    Title = "Movie 1",
                    Description = "Description 1",
                    Language = "en-US"
                },
                new MovieTranslatableDetails
                {
                    Title = "Film 1",
                    Description = "Beschrijving 1",
                    Language = "nl-BE"
                }
            }
        });
}

After creating the migration and updating our datebase, the DB diagram looks like this:

Database Diagram

Nice and clean!

Creating the repository

We have created our database but we still need to write some code to create and read movies from it.

As always, lets first create an interface!

public interface IEntityWithTranslatableFieldsRepository<in TEntity, TTranslatableDetails, out TTranslatedEntity>
    where TEntity : IEntityWithTranslatableFields<TTranslatedEntity, TTranslatableDetails>
    where TTranslatableDetails : ITranslatableDetails
{
    void Add(TEntity entity);
    IEnumerable<TTranslatedEntity> GetAll(string language);
    TTranslatedEntity Get(int id, string language);
}

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.

public abstract class BaseEntityWithTranslatableFieldsRepository<TEntity, TTranslatableDetails, TTranslatedEntity> : IEntityWithTranslatableFieldsRepository<TEntity, TTranslatableDetails, TTranslatedEntity>
    where TEntity : EntityWithTranslatableFields<TTranslatedEntity, TTranslatableDetails>
    where TTranslatableDetails : TranslatableDetails
{
    public void Add(TEntity entity)
    {
        using (var context = new DemoDbContext())
        {
            context.Set<TEntity>().Add(entity);
            context.SaveChanges();
        }
    }
 
    public IEnumerable<TTranslatedEntity> GetAll(string language)
    {
        using (var context = new DemoDbContext())
        {
            return context.Set<TEntity>().Include(m => m.TranslatableDetails).ToList().Select(m => m.GetTranslatedEntity(language));
        }
    }
 
    public TTranslatedEntity Get(int id, string language)
    {
        using (var context = new DemoDbContext())
        {
            var entity = context.Set<TEntity>().Find(id);
            context.Entry(entity).Collection(e => e.TranslatableDetails).Load();
            return entity.GetTranslatedEntity(language);
        }
    }
}

This is all straight forward EF code, except for the GetTranslatedEntity we just wrote in our entity.

If you have any questions about this code, just ask me in the comment section below.

With this all set up, creating our actual MovieRepository is just 1 line of code:

public class MovieRepository : BaseEntityWithTranslatableFieldsRepository<Movie, MovieTranslatableDetails, MovieFrontEnd> { }

Testing

We've got everything setup but we haven't been able to test it. So lets find out if it actually works!

class Program
{
    static void Main(string[] args)
    {
        //Initialize mapping
        Mapper.Initialize(cfg =>
        {
            cfg.AddProfile<DemoProfile>();
        });
 
        //Setup
        var movieRepository = new MovieRepository();
        var langaugeEn = "en-US";
        var languageNl = "nl-BE";
 
        //Load our seeded movie
        var movie1En = movieRepository.Get(1, langaugeEn);
        PrintProperties(movie1En);
 
        var movie1Nl = movieRepository.Get(1, languageNl);
        PrintProperties(movie1Nl);
 
        //Load an other movie
        var movie2En = movieRepository.Get(2, langaugeEn);
        if (movie2En == null)
        {
            //Create a new movie
            movieRepository.Add(new Movie
            {
                Id = 2,
                Director = "Sander Van Looveren",
                Duration = TimeSpan.FromMinutes(90),
                TranslatableDetails = new List<MovieTranslatableDetails>
            {
                new MovieTranslatableDetails
                {
                    Title = "Movie 2",
                    Description = "Description 2",
                    Language = langaugeEn
                },
                new MovieTranslatableDetails
                {
                    Title = "Film 2",
                    Description = "Beschrijving 2",
                    Language = languageNl
                }
            }
            });
        }
 
        //Load all movies
        var moviesEn = movieRepository.GetAll(langaugeEn);
        foreach (var movie in moviesEn)
        {
            PrintProperties(movie);
        }
 
        Console.ReadLine();
    }
 
    private static void PrintProperties(object obj)
    {
        Console.WriteLine($"Displaying {obj.GetType().Name}");
        Console.WriteLine("-------------------------------------");
        foreach (var prop in obj.GetType().GetProperties().Where(p => p.CanRead))
        {
            Console.WriteLine($"{prop.Name}: {prop.GetValue(obj)}");
        }
        Console.WriteLine();
    }
}

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

  1. Read the seeded movie in English
  2. Read the seeded movie in Dutch
  3. If a second movie does not exist, create a second movie in English and Dutch
  4. Read all movies in English
  5. Wait to close the program

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

Output

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!

1. Create the front end class

public class ShirtFrontEnd
{
    public int Id { get; set; }
    public bool HasLongSleves { get; set; }
    public string WashInstructions { get; set; }
}

2. Create the translatable details

public class ShirtTranslatableDetails : TranslatableDetails
{
    public int Id { get; set; }
    public string Language { get; set; }
    public string WashInstructions { get; set; }
}

3. Create the Enitty

public class Shirt : EntityWithTranslatableFields<ShirtFrontEnd, ShirtTranslatableDetails>
{
    public int Id { get; set; }
    public bool HasLongSleves { get; set; }
}

4. Edit the database

public DbSet<Shirt> Shirts { get; set; }

context.Shirts.AddOrUpdate(s => s.Id,
    new Shirt
    {
        Id = 1,
        HasLongSleves = false,
        TranslatableDetails = new List<ShirtTranslatableDetails>
        {
            new ShirtTranslatableDetails
            {
                WashInstructions = "Do not wash above 140°F",
                Language = "en-US"
            },
            new ShirtTranslatableDetails
            {
                WashInstructions = "Niet wassen boven 60*C",
                Language = "nl-BE"
            }
        }
    });

5. Create the repository

public class ShirtRepository : BaseEntityWithTranslatableFieldsRepository<Shirt, ShirtTranslatableDetails, ShirtFrontEnd> { }

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!

comments powered by Disqus