Translation - Multilingual/Multilanguage Database Design

Hi there! Today i wanna share something very useful to you while developing a multilingual / multilanguage database (DB). While outthere is a lot of code and approches, none of them take the time to point out the details you need to know, to really understand the concept and how you may optimize it. In this post i'm trying to explain it and to show my solution to you in a very simple, grafical way. It may exists even better approches out there, but I think this is a good one.

Understanding the concept before otimization

first of all lets understand the concept. The image below is the best way to visualize my thoughts. so lets take a look of it:

Ok, let's point out what ist good and not so good in this approach.
+ you have all translations files in one place (better maintanance)
+ with every new language you may have or need, you don't need to change your entity tables (flexible, extenpandable)
- not very readable for the DB user and queries/inserts are not trivial
- I do not know the ranges of each table (for example, if i want to print out a specific table for the translator in my DB it may be very difficult at first sight)

How can i optimize this approach?

Well there is a way to do that. For example one of it is to define an insert schema. It will turn this approach into a very readable and enables everybody to search for a specific table range without big efforts. The second image below is the best way to explain what i mean. So lets take a look of it:

Have you figured out the trick? The idea here is to work with conventions.  The insert schema could be something like:

IdRowToTranslate_TableName_ColumnPropertyName_IdRowTranslationReference_TableName

in this concrete example:
1_PRODUCT_NAME_1_TRANSLATION_REFERENCE 
1_PRODUCT_DESCRIPTION_2_TRANSLATION_REFERENCE  

2_PRODUCT_NAME_3_TRANSLATION_REFERENCE 
2_PRODUCT_DESCRIPTION_4_TRANSLATION_REFERENCE
and so on...
 
Once defined the insert and its respective query commands, it turns the model into a much more readable and understandable approach. This way we are now also able to seach by ranges.

Update: 

Hi there! a few days ago,  i had an interesting contatct with Alessandro. A Software Engineer looking also for a multilingual / multilanguage database solution. He had a very good approach. We have discussed PROS and CONS and because i think, that his solution is even more elegant than mine, i asked him to post it here to complement this post and share his solution with us. Here are both solutions (mine and alessandro's solution), so you can directly compare and have an ideia how to do it. Thanks Alessandro for sharing it with us.

That's all. hope you like it.

😱👇 PROMOTIONAL DISCOUNT: BOOKS AND IPODS PRO 😱👇

Be sure to read, it will change your life!

Show your work by Austin Kleon: https://amzn.to/34NVmwx

This book is a must read - it will put you in another level! (Expert)

Agile Software Development, Principles, Patterns, and Practices: https://amzn.to/30WQSm2

Write cleaner code and stand out!

Clean Code - A Handbook of Agile Software Craftsmanship: https://amzn.to/33RvaSv

This book is very practical, straightforward and to the point! Worth every penny!

Kotlin for Android App Development (Developer's Library): https://amzn.to/33VZ6gp

Needless to say, these are top right?

Apple AirPods Pro: https://amzn.to/2GOICxy

😱👆 PROMOTIONAL DISCOUNT: BOOKS AND IPODS PRO 😱👆