"how to design a database for translation dictionary?" Code Answer

with separate table for each language, you'd need a large number of junction tables to cover all the possible translation combinations. on top of that, adding a new language would require adding more tables, rewriting the queries, client code etc.

it's better to do it in a more generalized way, similar to this:

regarding the translation table, i propose to also create a check (word_id1 < word_id2) and create an index {word_id2, word_id1} (the opposite "direction" from the pk), and represent the both directions of the translation with only one row.

consider clustering the translation table if your dbms supports that.

also need it alphabetically all time

the query...

select * from word where language_id = :lid order by word_text

...can use the index underneath the unique constraint {language_id, word_text}.

By Sgotenks on May 24 2022

Answers related to “how to design a database for translation dictionary?”

Only authorized users can answer the Search term. Please sign in first, or register a free account.

Browse Other Code Languages