How to Set Up a Website and Database to Support Multiple Languages

In today’s global economy, as more and more business gets done across international borders, many organizations are finding the need to support multiple languages on their websites. And here, I’m not talking about programming languages, I’m talking about languages such as English, Spanish, and Chinese.

Today, I’m going to be giving you a quick tutorial on how to support multiple languages on your website using a relational database, regardless of whether you’re supporting 2 languages or 200, in a manner that easily scales up and down. There are other ways besides databases to support multiple languages, but databases are the easiest way to keep everything organized.

My Portfolio Website is Bilingual

I don’t really advertise it, but my professional portfolio website is actually bilingual and is available in both English and French. To change languages, click on the link at the very bottom of the footer that says “Français” or “English”. I’m hoping to add Spanish as well once my Spanish language skills improve to that level. I think using online translators is poor form for a professional website, as they often struggle with grammar and verb conjugations, and your business really shouldn’t be offering services in a language without having an employee who can speak the language fluently.

The Wrong Way to Set Up Your Database Schema

Setting up the proper database schema to both support multiple languages and scale easily the the key to this tutorial. If not done properly, you will get either one or the other. In its most basic form, a database table that supported only English would look something like this:

I know what you’re probably thinking right now. It must be simple to add support for a second language. We could simply add another column to the database table, like this:

Unfortunately, as simple as this seems, this is not the correct way to do it. I will admit, I made this exact mistake when I first started adding support for the French language on my website. As soon as you start running queries, you realize why this doesn’t work. You either need to select both languages to keep the query simple or write a complex query that needs to determine which column to select.

Then, there’s also the issue of scalability. While this may seem like a perfectly valid solution for supporting 2 or 3 languages, what would this look like if you were supporting 200 languages? Every time you wanted to add or remove a language, you would need to add or delete a column from every column in the database. Calling that a tedious job is an understatement at best.

Use Unique ISO Language Codes to Properly Design Your Database Schema

The International Standards for Normalization (ISO) maintains 2-letter codes for all of the world’s common languages, which fall under the ISO-639 standard. Examples of these codes include “en” for English, “fr” for French, “es” for Spanish, “it” for Italian, and “ar” for Arabic. In the real world, you probably want to use your own foreign keys with a “language” table, but for this example, I’ll use the ISO language to make it easier to understand. The correct way to set up your database table for multiple languages is:

A Properly Designed Database Scales Effortlessly

In addition to being properly normalized, this table makes queries much easier. If you want the English phrase, just add “WHERE language = ‘en'” to your query. If you want the French phrase, add “WHERE language = ‘fr'”. It also scales up and down nicely, as you just need to add and delete records (rows) to add or remove support for additional languages. This way, I can quickly add multiple languages at a time with just a single query, such as:

Use UTF-8 Encoding to Support Even More Languages

Finally, when creating a database table that supports multiple languages, you must encode the table as UTF-8, or else accented characters will not display correctly. To ensure everything displays properly, I usually encode the string into UTF-8 a second time with the programming language I’m using to extract the data from the database. I know that both PHP and Python have that functionality. UTF-8 encoding will  also work for languages that do not use the Latin alphabet, such as Greek, Russian, and Chinese.

See how nicely that scales when you have more than just 2 or 3 languages. If you write your back end code correctly, you shouldn’t need to change it at all when you add or remove support for a language. Hopefully adding support for multiple languages to your website will allow you to begin expanding your business into places you never could have imagined earlier.