Designing a Multi-Language Database
DbVisualizer
Posted on September 14, 2023
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 this tutorial
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:
- Normalization – normalization is a must for any modern database and it helps prevent unnecessary data from being stored inside of it.
- 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.
- 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:
- A decent SQL client – did we tell you that you can try the premium features of DbVisualizer for free by evaluating the tool?
- 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.
- 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:
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:
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):
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?
Take a closer look at the features yourself – aren’t you amazed? Quick, grab your free trial before it runs out!
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.
About the author
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.
Posted on September 14, 2023
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
November 30, 2024