database design for dictionary application

Question

Currently I'd like to develop dictionary application for mobile device. The dictionary itself use offline file/database to translate the word. it just translates for two languages, for example english - spanish dictionary. I've a simple design in my mind. it would be two tables: English Table and Spanish Table. for each table contain of:

• word_id = the id which would be a foreign key for other table
• word = the word
• word_description
• correspond_trans_id = the id of other table which is the translation for this word to other language.

and also because of this is for mobile application, the database use SQLite.

The definition data for each table has been provided order by field 'word' on the table. However I'm still thinking the problem if there is addition for the data definition. Because the table would be order by field 'word', is there any method to put (insert) the new record still in order by word ? or any idea to make it more efficient ?

Answer 1

At least it for each translation there are a few translation possibilities depending on the context. if you like to do a bidirectional dictionary for two languages you need at least three tables:

ENGLISH
ID | WORD
1  | 'dictionary'

GERMAN
ID | WORD
1  | 'lexikon'
2  | 'wörterbuch'

TRANSLATION_EN_DE
ID_EN | ID_DE
1     | 1
1     | 2

The first two tables are containing all the words that are known in that language and the bidirectional mapping is done by the 3rd mapping table. this is a common n:n mapping case. with two more tables you're always able to add a new language into you're dicitionary. If you're doing it with one table you'll have multiple definitions for a single word thus no normalized db.

you can also merge your language tables into a single table defining the words language by another column (referencing a language table). in that case you'll need a 2-column index for the language and the word itsself.

Answer 2

What do you intend to do when a word in language 1 can be translated by more than one word in language 2? I think you have to use something like wursT's design to handle that.

RE inserting records in alphabetical order: You do not normally worry about the physical ordering of records in a database. You use an ORDER BY clause to retrieve them in any desired order, and an index to make it efficient. There is nothing in the SQL standard to control physical ordering. Umm, I recall coming across something about forcing a physical ordering on some database I worked with, I think it was MySQL, but most will not give you any control of this. I haven't worked with SQLite so I can't say if it provides a way.

Answer 3

Surely the relationship between words and their possible translations is one-to-many or many-to-many. I'm not clear how you will represent this in your model. Seems like you may need at least one more table.