CODEWITHSUNDEEP
databasedatabase-designnormalization
Abhijeet Vaikar
Abhijeet Vaikar

Reputation: 1656

Structuring the table of a multi-lingual dictionary database

I want to structure the table(s) for the database of a multi-lingual dictionary (English - Marathi). Marathi is a regional language in India.
The format of the dictionary is:

word | english_meaning1 | marathi_meaning1 | english_meaning2 | marathi_meaning2 ... english_meaningN | marathi_meaningN


Words can have variable number of pairs of english and marathi meanings depending upon whether it belongs to any of the lexical categories (Noun, Adverb, Verb, Adjective etc.)
Currently I have thought of an inefficient approach of creating a table like this:

Table: word


Here I am assuming a fixed number of columns (20) for english and marathi meanings for a word in English. So if a word has only a single meaning (in English & Marathi), the rest of the columns will remain empty.

Also, if it's a word for example: 'about', which in the dictionary is shown as:

about1 - meanings
about2 - meanings

Then I'm maintaining them as separate rows in the above structured table.
Isn't this approach problematic? Can this be solved by normalizing it? I have thought of a way where the tables will be:
Table: word

Table: word_english


Table: word_marathi


I am not pretty sure whether the above approach makes sense. Could anyone suggest a possible solution?? Thanks in advance!

Upvotes: 2

Views: 468

Answers (1)

Randy
Randy

Reputation: 16677

ooof. definitely normalize

word
---------
word_id
word
definition
language_id
lexical_part_id

language
-----------
language_id
name

word_word
------------
word1_id
word2_id

lexical_part
-------------
lexical_part_id
name

then fill in the word_word table with the equivalence map

Upvotes: 1

Related Questions