1

I've search all over and can't find advice of a good database structure for a bilingual dictionary, how would you define the structure for an English-Spanish language bilingual dictionary?

4

In a document store:

{
    {
        "language": "English",
        "word": "cat"
    },
    {
        "language": "Spanish",
        "word": "gato"
    }
}

Of course you'll have to choose a working language for the keys.

In a graph DB, add each word from each language as a node. Label the nodes with the language. Alternatively have a separate node for the language itself and connect the word-node to the language-node. Then connect word-nodes in one language to the corresponding word-node in another language. Note that English has a lot of words imported from other languages so decide whether you have one word-node connected to several languages or separate word-nodes.

In a relational database I would have a table for the languages, one for word lists and a third for equivalences

table: Languages
    Language_name   primary key

table: Words
    language_name
    word

Both columns together make a composite primary key

table: Transaltions
    first_language_name
    first_word
    second_language_name
    second_word

The primary key is all four columns. During lookup should you compare the given word to first_word or second_word? Either maintain a conventions and stick with it (Spanish = first_, English = second_) or query both and deal with the few (but non-zero) collisions).

Some concepts do not translate word-for-word. This could be handled by renaming column "word" to "idea" or "phrase".

For multi-lingual (rather than just bilingual) lists having an over-arching concept which has word-forms in each language would be one way to go:

Table: Concepts
    concept_id

Table: Translations
    concept_id
    language_name
    word

To go from "cat" (English), find the corresponding row in Translations. From that find the concept_id. Then read the concept_id, language_name row in Spanish. Homonyms make this difficult, however.

| improve this answer | |

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.