Multi- Language translation

Question

I am designing a database for multi language translator. A particular word can have more than one word in another language(like synonyms of a word).

For example, word a(in language1) -> word b & c(in language 2). Both are correct. I need to store all the words in the database. I researched ann found Schema for a multilanguage database.

But the problem i am facing is there is many to many mapping in my words. I also referred Good database and structure to store synonyms. Currently this is my database design

CREATE TABLE Language1 (
  Word_number  int,
  Word int,
  other char(10)
)
CREATE TABLE Language2 (
  Word_number  int,
  Word int
  other char(10)
)
CREATE TABLE Language3 (
  Word_number int,
  Word int,
 other char(10)
)
CREATE TABLE Linkwords (
  Language1_Word_number int,
  Language2_Word_number int,
  Language3_Word_number int
)

Although language tables looks neat. The link table is messy.
For example if language3 has 3 words for same word in language 1&2 entries in table looks like

1 1 1,1 1 2, 1 1 3,...

Could anyone please suggest a better design?

score 1 · Accepted Answer · answered Feb 04 '15 at 13:19

The way I would design this is as follows:

Words
id  // primary key
word  // the word itself

Languages
id   // primary key
langname

LangWords
word_id  // joint primary key
lang_id  // joint primary key
langword

The languages table would have entries like this

+----+-------------+
| id | langname    |
+----+-------------+
|  1 | French      |
|  2 | Italian     |
|  3 | German      |
+----+-------------+

Let's say that we want to store the word 'red'. In table 'words', id=1, word = 'red'.

There would be three entries in the 'langwords' table

+---------+---------+-----------------+
| word_id | lang_id | langword        |
+---------+---------+-----------------+
| 1       | 1       | rouge           |
| 1       | 2       | aldo            |
| 1       | 3       | rot             |
+---------+---------+-----------------+

The number of records in 'langwords' should be the number of words times the number of languages. The above structure allows easy access to either all the words in a given language or the same word in every language.

Thanks for your reply. I am not sure how will your database design solve the many to many relationship problem in my question. For example, i have another word for red in italian say 'xyz'.How will I enter that in the current 'langwords' table. An row like 1,2,xyz will not satisfy primary key value. Could you please help me on that — user987743, Feb 04 '15 at 21:17
@user987743: if there is more than one translation corresponding to the original word, then you'll have to add another key field to the 'langwords' table (here called 'level'). So you could have in 'langwords' the tuple (1,2,1,'aldo') and (1,2,2,'rosso'). I always thought that 'aldo' in Italian meant 'red' in English but Google Translate says not. — No'am Newman, Feb 05 '15 at 04:15

Multi- Language translation

1 Answers1