I want to build a system which uses dictionary of many different language.

There are 2 options: Build 1 central dictionary Table & each language is identified by langType (ex 1 for German & 0 for English):

word - meaning - langType
car  - ....    - 0
auto - ....    - 1

or 2 separated Tables

word - meaning
car  - .... 

word - meaning
auto - ....

If the option is 2, then when do the coding

String DictTable="";
String locale=getLocale();
if ("gm".locale){
else if("en".locale){

Then when we query from

String sql="select * from "+DictTable;
//access data from table here

If option 1 is selected,

    int langType=0;
    String locale=getLocale();
    if ("gm".locale){
    else if("en".locale){
    String sql="select * from CentralDict where langType="+langType;
    //access data from table here

I am guessing the 2 option is more elegant, but not sure I am right?

With option 2 we can create a 1 to 1 relationship between 2 languages easily.

Note: It is not the internationalization issue as my system can handle internationalization very well. While internalization deals with text of the Gui like the text of the button or the message info, it does not deal with the data that was pulled out from DB.

My Dictionary System helps user to see the explanation terms such as "car", "phone".... for teaching purpose.

Currently, it works fine in English language, but I want to support German & many be French or any other languages in the future. I don't know whether I should create a complete separated table for each language or I just need 1 centralized dictionary for all languages & has a langType field for identifying the language that need to be used based on the user requirement. Ex: WHen user enters mydomain.com/locale=de then the system will show the exact Gui ( as in English) but all the text of the Gui will be in German. Also when user searches for data , it should pull out the German data from DB. not English data.

  • 4
    Your SQL scares me. Please read Bobby Tables – Dan Pichelman Jul 27 '14 at 2:29
  • 1
    1) you're unlikely to store words, as words aren't 1:1 across languages. 2) you're unlikely to go straight to SQL every time you want a string. – Telastyn Jul 27 '14 at 3:40
  • 1
    I have to agree there with the storage and retrieval. All the multi-language pieces I've worked on work in whole phrases, and those are referred to by some identifier. Whenever the current language changes, a full set of strings is loaded for that language. The runtime (always a GUI for me) simply says something like "use phrase #12 here" and it pulls the entire string. – Patrick Hughes Jul 27 '14 at 3:58
  • Option 1; dynamically generating SQL at runtime is poor practice. Your example code for option 1 should be re-written to use a parameter rather than dynamic SQL. Also your table will need another column to indicate that "car(English)" and "auto(German)" are the same concept if your objective is as you say in the comment below. – Michael Green Jul 27 '14 at 11:19
  • @ Michael, the server did the checking if (locale.equals("de"), then return 1" how can it got SQL injection? there only a few option – Tum Jul 27 '14 at 14:37

What is the actual issue you want to solve? What is your application doing? On which operating system?

If it is a matter of internationalization, e.g. adapting your software to give messages in various human languages, most operating systems provide a framework for that. On Linux and Posix systems, you can use the locale(7) infrastructure.

If you goal is natural language processing (e.g. adapting your software to somehow "understand" various human languages inputs) there are many techniques (and libraries) for that (and NLP is a complex and difficult field, so start reading many books and/or follow some university courses). AFAIK the techniques might depend on the actual human language, because of the diversity of written human languages : some are syllabic, some are alphabetic, some use pictographs, some have cases and genders, and some languages don't even have the notion of verb as in Indo-European languages (English, French, Russian being the one I know ...).

  • it's not a internationalization issue, my system can handle internationalization very well, the dictionary for my Dict application. I want to have a dictionary that have many languages & and each of the words and meaning in the dictionary will be used for teaching purpose – Tum Jul 27 '14 at 10:35
  • You should edit your question to explain that better. – Basile Starynkevitch Jul 27 '14 at 10:38
  • I done, please read the Note: part – Tum Jul 27 '14 at 14:33

If you know what language you want before the lookup, it's going to be faster to search one small language-specific table than a huge universal table with lots of languages.

Option 2 gets my vote!

  • I'm not sure of that. And it is better to have few tables. – Basile Starynkevitch Jul 27 '14 at 8:04
  • It may be an option to cluster and/or partition by language, depending on the software used, minimising the large-table effect. Table count is not a constraint for any widely-used DBMS. – Michael Green Jul 27 '14 at 11:13
  • 1
    My point was just that having hundreds of tables is often the symptom of bad design. – Basile Starynkevitch Jul 27 '14 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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