Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 enter image description here

    • 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 enter image description here

    • 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?
share|improve this question
2  
but it will be dangerous to have all data of one entity for all countries in one table ...so any attack or mistake can destroy my work for all these countries You should have backup for any database holding critical data. This is a must! Not a best practice, or a recommendation, but a must! –  ppeterka Oct 16 '12 at 11:45
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Can I isolate data required for every country from central tables to be used with software ONLY there? –  Hatem Ghazy Oct 17 '12 at 11:35
    
What do you mean? It sounds like you are asking if you can query the database to get only the data relevant to one country. The answer is of course yes, so there must be more to your question than that. –  Leigh Riffel Oct 17 '12 at 12:32
    
for example , can i separate french data from the main tables into empty tables to be used by software only in france?? i dont want to load all data in all languages in every country... i want to isolate every country data to be used only in this country –  Hatem Ghazy Oct 17 '12 at 13:36
1  
Thank you for the clarification. You most certainly can separate the data by country into separate tables or a separate database. How you should do this though would require a lot more details to answer and is probably beyond the scope of this question or even this site. –  Leigh Riffel Oct 17 '12 at 14:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.