Design
Language

Designing a Multi-Language Database

intro

Multiple languages within applications are not a new thing – designing a database that supports multiple languages isn‘t a piece of cake though. Don‘t worry – we‘ll explain everything you need to know in this blog!

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Preface

If we would ask you how many applications within multiple languages have you seen in your lifetime, we could bet that the answer could be something along the lines of “I’ve definitely seen more than ten.” Multiple languages within an app help increase brand visibility – building an application supporting more than one language isn’t the easiest thing to do though. We need to consider what languages are we going to support, which parts of the application to translate and how, and finally, how to build out our database in such a way that supports those languages.

The Role of Databases

Applications supporting multiple languages are a difficult thing – not only because the developer must be aware of the languages being supported, but also because the database must be designed in such a way that supports changes inside of the application. That means that developers must consider a couple of things:

  1. Normalization – normalization is a must for any modern database and it helps prevent unnecessary data from being stored inside of it.
  2. Languages supported – one must evaluate their audience very carefully and build languages in accordance to visitor location. If the majority of the users using an application are Japanese, supporting Japanese language makes sense, however, if the majority of the users are from the Netherlands and our application only “speaks” Finnish, we’re going to have issues.
  3. Users using the application – supporting multiple languages is usually only necessary because of the users using the app, so we need to carefully consider whether users even need a way to switch between languages.

After all of those things out of the way, we can start designing our database! We will need:

  1. A decent SQL client – did we tell you that you can try the premium features of DbVisualizer for free by evaluating the tool?
  2. A set of languages we’re going to support – we recommend selecting not more than 3: the more languages exist, the harder it is to implement support for them.
  3. Knowledge around our DBMS of choice – while there is no specific requirement for a database management system, we should choose the one DBMS we’re most comfortable working with. For some it may be PostgreSQL, some may elect to use SQL Server, some may build on MySQL or its flavors like MariaDB or Percona Server.. Whatever’s the case, knowledge around our chosen DBMS is essential.

Let’s start designing!

Designing the Database

For this example, we’re going to use MySQL. In most cases translation would run through tables, so to start, we need to create a table inside of our database:

A Table for Translations.
A Table for Translations.

You probably see where we’re going with this and the concept is really simple – we need to have a string to translate, then translate it into three languages – Finnish, Swedish, and Norwegian. Pretty self-explanatory, right? The ID will increment automatically, so we don’t need to insert anything into that field. Our queries will look like this most of the time:

Inserting a Language.
Inserting a Language.

Or like this (do note the NULL value – for a column to increment automatically, we need to specify NULL, otherwise we’ll get an error):

Inserting a Language - No Parameters.
Inserting a Language - No Parameters.

However we’d write the query, one thing remains clear – it’s more easy than we could think of, isn’t it? Now quick, write a script that lets you insert values provided by the user into the database, sanitize the input, and insert them for automatization and you’ll be good to go! Well, almost...

SQL Clients

Remember how we said that in order to design a multi-language database you need to select the database you’re most familiar with? That’s not without a reason – and the reason is simple: the more familiar you are with the database, the more easy it is for you to work with it. Part of the reason concerns SQL clients as well – most SQL clients can only help if you at least partly know your way around the database. But even if you don’t, with DbVisualizer you’re in good hands – from letting you observe tables from inside by providing references to providing security when executing queries (look closely at one of the images above – do you see an alert that says the mode is Development? One can deny query execution based on rules that can be set. How cool is that?) Did we mention that DbVisualizer can connect to any database you could think of and that it’s used by the biggest companies on the globe?

Table references provided by DbVisualizer.
Table references provided by DbVisualizer.

Outro

Designing a multi-language database is not exactly eating a piece of cake, but with that being said, it’s not rocket science either. We need to know what kind of a database management system we’re most familiar with, what languages we need to support, and a couple of basic SQL queries – that’s it, really.

Oh, and SQL clients help too – from helping build our SQL queries to visualizing our database, they have it all – not all SQL clients are built the same, however, those that are good, are used by the biggest companies on the planet. They’re a good choice!

Stick around, follow us on Twitter for updates, and until next time.

FAQs

Is it hard to design a multi-language database?

No, not at all! You just have to know what kind of languages your application needs support for, know your way around a database management system or two, and you should be good to go.

How many multiple languages should my app support?

The exact number is up to you to choose, but we suggest your application supports not more than 3 due to time involved.

Can I automate the process of inserting multiple languages? How?

Yes, you can – that’s very easy. Simply build your table within the database, and then build an INSERT query on top of the table structure as shown above. As easy as that!

Should I use additional tools to help me?

The final yes or no depends on you, but we suggest you use SQL clients like DbVisualizer to help you manage your database infrastructure and ease your life as a developer or a DBA.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

SQL EXCEPT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 8 min 2024-04-18
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

Unlocking the Power of CTEs in SQL

author Ochuko Onojakpor tags 9 min 2024-03-21

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗