0

I need advice on how to build entity-relationship diagram (ERD) for Language Dictionary. Since I did not build ERD before, I will try to explain and show the ultimate goal of this project below.

Requirements

This will be an application on Android and Ios smartphones, which has the ability to search for the word (local databases are not planned; ie, the search for the word will drag data from the server) and therefore the request must be processed fairly quickly. The object returned from the server will have the following values:

{
  "word": {
    "core": {
      "id": 1,
      "word": "lorem",
      "description": "Blabla lorem blab blablab blab blablabblab blablab",
      "grammar": {
        "noun_class": "v-class",
        "morphology": {
          "root": "lor",
          "affixes" {
            "prefixes": [
              {"first": ""},
              {"second": ""},
              {"third": ""}
            ],
            "suffixes": [
              {"first": ""},
              {"second": ""},
              {"third": ""}
            ]
          },
          "endings": [
            {"first": ""},
            {"second": ""},
            {"third": ""}
          ]
        },
        "declension": {
          "singular": {
            "declension_1": "lorem",
            "declension_2": "loremo",
            "declension_3": "loremon",
            "declension_4": "loremi",
            "declension_5": "loremino",
          },
          "plural": {
            "declension_1": "lorem",
            "declension_2": "loremo",
            "declension_3": "loremon",
            "declension_4": "loremi",
            "declension_5": "loremino",
          }
        },
        "part_of_speech": "noun",
        "omonims": ["lorem", "lorem", "lorem", "lorem"],
        "synonyms": ["lorem", "lorem", "lorem", "lorem"],
        "antonyms": ["lorem", "lorem", "lorem", "lorem"],
        "hyperonyms": ["lorem", "lorem", "lorem", "lorem"],
        "hyponyms": ["lorem", "lorem", "lorem", "lorem"],
        "etymology": "Blabla lorem blab blablab blab blablabblab blablabBlabla lorem blabla lorem blab blablab blab blablabblab blablab",
        "transcription": "[lorem]",
        "pronunciation": {
          "id": 1,
          "audio": "https://s3.amazon.com/voice.waw"
        },
      }
    },
    "english": {
      "translate": "lorem",
      "in_text": "Blabla lorem blab blablab"
    },
    "german": {
      "translate": "lorem",
      "in_text": "Blabla lorem blab blablab"
    }
  }
}

Considerations

If you imagine that there can be more than 100 thousand words, then when searching for a word it is very important that the query on the database is happening pretty quickly.

Therefore I thought to construct tables on each letter of the alphabet separately i.e. one table on all words beginning on letter A, and so on. And it is important for me to know if this will improve the search?

And the grammatical meaning of the word, is it better to put them in a separate table and create relationships (M-M, 1-M, 1-1) to the word or should I still create them inside the same table as the word itself?

My current ERD

I created the ERD that follows based on my experience, which is very poor:

enter image description here

Perhaps I am missing something and the advice of experienced developers who have worked a lot with databases would help me a lot.


Note: I don’t know if it’s important, but I’m going to build a database using Python Django as Web-client and PostgreSQL.

23.06.2019 - I've preliminarily committed my "first" version of django models, I would be very grateful if anyone could confirm that I did everything right. https://github.com/khashashin/language-dictionary-model/blob/master/formatting.py

  • 3
    “Therefore I thought to construct tables on each letter of the alphabet separately i.e. one table on all words beginning on letter A, and so on. And it is important for me to know if this will improve the search?” no no no no no! A billion times no. Properly index your data – Philᵀᴹ Apr 28 at 15:28
  • @Philᵀᴹ Thanks for pointing on that I found great explanation for data indexing here stackoverflow.com/questions/1108/… – khashashin Apr 28 at 15:32
  • Should I create whole grammar object separately? How should I create ( which is the best way) english and germany objects? – khashashin Apr 29 at 21:59
  • 1
    Rather than focusing on the JSON response your API sends out, focus on the the data you want to store and how you want to store it. Converting it to JSON (or presenting us a JSON response) is something you should do much later in your data modelling process. It's unclear to where you want to search the word the user enters. Where does the searched data come from? How does the searched data look like? Did you consider Postgres' built-in full text search? – a_horse_with_no_name Apr 30 at 6:16
  • I did mistake here, two yellow tables are actually choosers, in Django (Python web framework) you have the opportunity to define a tuple of some values and use it as a chooser – khashashin Apr 30 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.