1
\$\begingroup\$

I created a dictionary web app for my language in PHP. At the beginning of the project I wanted to create not a dictionary but a simple web application that would correct the words that were entered in the wrong format by the user. But with time, the circumstances became shaky and I found good books on my topic and I decided to create everything in one. That is, a web application that corrects and translates a word. Well, of course it's just a translation by words and not like a clever translator like Google Translate.

Now I have words, a description of the words and an Arabic translation of these words. I have not yet decided how to create a structure for the database. Therefore I would like to advise on the structure of the database with you.

I myself came up with such a structure for the database of my project:

Database: "Translator":

Tables:

  • Words
  • Words translation
  • Words description
  • Synonyms of words
  • Antonyms of words

Other tables are not created, that is, not yet invented.

Tables structure:

"Words":

  • id
  • word

"words_translation":

  • id
  • word_id
  • arabic
  • english
  • russian
  • france

"words_description":

  • id
  • word_id
  • description

"synonyms_of_words":

  • id
  • word_id
  • word_synonyms

"antonyms_of_words":

  • id
  • words_id
  • word_antonyms

I'm about to show my words so that you understand and appreciate the structure of the base for efficiency. Is it suitable for my requirements or not?

In the first step I add words (only words) to the database. The list of words can be updated at any time. Because all existing words in my language can not be found in one time and added to the database.

Example of my words in the array:

$array_words = array
(
    'тоҷик',
    'ӯзбек',
    'падар',
    'модар',
    'китоб',
    'қалам',
    'ручка',
);

Table "words" after adding an array of words in this form:

  --------------
  [id] | [word]
  --------------
  [11] | [тоҷик]
  [12] | [ӯзбек]
  [13] | [падар]
  [14] | [модар]
  [15] | [китоб]
  [16] | [қалам]
  [17] | [ручка]
  --------------

After adding the words, I will individually add their description to the table with descriptions of the words:

Example of description words in an array:

$array_words_description = array
(
    'тоҷик' => 'Миллати давлати Ҷумҳурии Тоҷикистон. Забони давлати мазкур.',
    'ӯзбек' => 'Ӯзбекистон давлати дар Осиёи маркази ҷойгиршуда. Забони давлатиашон забони ӯзбеки мебошад.',
    'падар' => 'Шахсе ки сарвари оила мебошад ва оилаи худро бо меҳру муҳаббат ва бо хӯрду - хӯрок таъмин менамояд.',
    'модар' => 'Инсоне мобошанд, ки мову шуморо ба дунё оварда ба воя мерасонанд. Бузургтарин шахс дар олам ин модар аст.',
    'китоб' => 'Китоб - ин предмете, ки ба ҳамаи мо илму маърифатро, донишу сабақро меомӯзонад.',
    'қалам' => 'Предмети барои нашитаҷот пешбинишуда.',
    'ручка' => 'Предмет барои навистан пешбини шудааст. Бисёртар ин предметро донишомӯзандагон истифода мебаранд.',
);

Table "words_description" after adding an array of words description in this form:

  ---------------------------------------
  [id] | [word_id] | [description]
  ---------------------------------------
  [1]  | [11]      | [Миллати давлати...]
  [2]  | [12]      | [Ӯзбекистон давл...]
  [3]  | [13]      | [Шахсе ки сарвар...]
  [4]  | [14]      | [Инсоне мобошанд...]
  [5]  | [15]      | [Китоб - ин пред...]
  [6]  | [16]      | [Предмети барои ...]
  [7]  | [17]      | [Предмет барои н...]
  ---------------------------------------

My tactic is the addition of a description of the words, as you understand by the key of the array. For example, first I find words (if there is such a word in the database, otherwise I first add it from the array key and then continue adding the description) and adding the identifier of that word to the field word_id.

Synonyms and antonyms table, too, do not necessarily now describe. And now look at the translation table.

An example array for adding to the table with translations (arabic tr.):

$array_words_translation = array
(
    'тоҷик' => 'تاژيك',
    'ӯзбек' => 'اوزبيك',
    'падар' => 'پدر',
    'модар' => 'پدرماد',
    'китоб' => 'الكتاب',
    'қалам' => 'آبيار',
    'ручка' => 'آبزن',
);

Table "words_translation" after adding an array of translation words in this form:

  --------------------------------------------------------------
  [id] | [word_id] | [arabic] | [english] | [russian] | [france]
  --------------------------------------------------------------
  [1]  | [11]      | [تاژيك]  | [NULL]    | [NULL]    | [NULL]
  [2]  | [12]      | [اوزبيك] | [NULL]    | [NULL]    | [NULL]
  [3]  | [13]      | [پدر]    | [NULL]    | [NULL]    | [NULL]
  [4]  | [14]      | [پدرماد] | [NULL]    | [NULL]    | [NULL]
  [5]  | [15]      | [الكتاب] | [NULL]    | [NULL]    | [NULL]
  [6]  | [16]      | [آبيار]  | [NULL]    | [NULL]    | [NULL]
  [7]  | [17]      | [آبزن]   | [NULL]    | [NULL]    | [NULL]
  ---------------------------------------------------------------

My web-app translate from one language into several languages. But here you can translate from Arabic to Russian, for example, or from Russian to French. After all, the identifier of the word belongs only to one single word. Well of course if that's the corresponding translation is!

I have not yet created this whole structure practical and it's just so far just a structure created in a paper sheet. The construction of SQL queries is also not planned. Can I create good, fast and optimal queries in this my database structure for adding and extracting words from the database?

What is your opinion about the structure of the database and about adding words to the database?

\$\endgroup\$
  • \$\begingroup\$ You might consider removing PHP and performance tags from your question, as they don't seem relevant to this question. (There is no PHP code to review or any information here that would be relevant to performance optimization). \$\endgroup\$ – Mike Brant Oct 23 '17 at 16:36
1
\$\begingroup\$

I think you database schema has some areas for improvement.

  • Why have a word_description table at all? You don't seem to be implying anything other than a 1-to-1 relation between a word and it's description, so it is perhaps best to move description column to main word table and get rid of this table.
  • Your antonym and synonym tables seem odd. Would these simply not be tables that relate primary key id's from word table together?

Example schema:

id - primary key (perhaps not even needed)
word_id - foreign key to word.id
antonym_id (or synonym_id) - foreign key to another word.id record

You would likely want a unique index across word_id and antonym/synonym_id, or perhaps just treat this as compound primary key and drop the autoincrement primary key altogether (not sure what your lookup use cases are here to make this decision, or if you are using some sort of framework that always expects records to have autoincrement primary key).

  • In the translation table, I don't like the approach of adding a column for each language. This will potentially make it more painful to add new languages down the line, and I am guessing there is not really a use cases to get an optimized lookup of all localizations for a word at once.

I would suggest a schema like:

id - primary key
word_id - foreign key to word.id
locale - enum or similar field that indicattes localization for the record
translation - the translated term

Again, you would likely want unique key across word_id and locale, which could also serve as primary key if you wanted to drop the autoincrement primary key

  • I think that when working with localization, your approach is a little naive. In most cases, you want to localize phrases, sentences, paragraphs, etc. and not just individual words. If you are only thinking about word-by-word localization, you will probably not ever get the sort of localization results you want.
\$\endgroup\$

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.