What is the best database design for multi-language data?

Question

I am designing a multi-language database for a piece of software with the following features:

• data used in every country differs from other countries
• data used in every country will be in 2 languages: English, and the native language
• there will be a central place to control updating and deleting all data in all countries

I have 2 choices for these multi-language databases:

1. Tables of each entity for each country will have a column for English and native language

   • I think that way makes it easy and fast to update every country's data, and I can separate its own tables only to be used with software there.
   • But that means if I have 15 countries, I have to repeat tables 15 times.

2. A table for all languages as a reference, linked to a table for each entity that contains data for all countries in all languages

   • It will make my database simpler, with a smaller number of tables, and simpler for retrieving data.
   • But it will be dangerous to have all data for one entity for all countries in one table. So any attack or mistake can destroy my work for all these countries.
   • Can I arrange data in the same table to make every row of the same languages together?
   • Can I isolate data required for every country from central tables to be used with software ONLY there?

Answer 1

The primary disadvantage of your first option is that you must create/maintain a similar table structure for n different languages and your application must switch between them. When a new language is added you will have to add table and modify your application. Your second option is preferable of the two. I will address some of your concerns.

• If your security relies on the separation of data into different tables, then your security needs work. Sure, separating them would provide some degree of additional security, but if you invest the time you save by not maintaining multiple tables into verifying/increasing your other security measures you will have far greater benefit.
• When you query the data you can sort by language.
• A WHERE clause on the country would retrieve data only for a particular country.

I recommend you store the primary details including the English names in simpler tables and then each table that has other language equivalents can have a language specific table containing every other language. Something like this:

Languages
   ID
   Name

Drugs
   ID
   Price
   Name
   Interactions
   Mechanism
   Uses

DrugLanguages
   DrugID
   LanguageID
   Name
   Interactions
   Mechanism
   Uses

Companies
   ID
   Name
   Address
   Activity

CompanyLanguages
   CompanyID
   LanguageID
   Name
   Address
   Activity

You should know that I have never done multilingual design, so my approach may have limitations that I do not fathom.