CODEWITHSUNDEEP
node.jsjsonmongodbdatabase-design
Personal Information
Personal Information

Reputation: 581

Database design that requires two searches

I have played around with mongodb in the past, but this is the first time I am working on a project.

I am designing a Spanish dictionary. However I am having trouble with the design. This is the first design. These are the objects that get stored in mongodb.

Dictionary collection

{
    word:"comer",
    type:"infinitive",
   Definition:"A definiition for the word hablar",
    conjugation: {
        present: ["como", "comes", "come", "comemos", "coméis", "comen"],
        preterite:["comí", "comiste", "comió", "comimos", "comisteis", "comieron"],
    }
},
// Lets pretend that this is another verb with different conjugation forms.
{
    word:"hablar",
    type:"infinitive",
   Definition:"A definiition for the word hablar",
    conjugation: {
        present: ["como", "comes", "come", "comemos", "coméis", "comen"],
        preterite:["comí", "comiste", "comió", "comimos", "comisteis", "comieron"],
    }
}

With this design I can search for a word by comparing the word:"a word" property. The problem is that with this design searching for "como" isn't posible or at least not efficient.

The second design has two collections Words and verbs

Words

{
    word:"como",
    infinitive:"comer",
},
{
    word:"comer",
    infinitive:"comer",
},
{
    word:"comemos",
    infinitive:"comer",
},
{
    word:"habla",
    infinitive:"hablar",
}

Verbs

{
    word:"comer",
    Definition:"A definiition for the word comer",
    conjugation: {
        present: ["como", "comes", "come", "comemos", "coméis", "comen"],
        preterite:["comí", "comiste", "comió", "comimos", "comisteis", "comieron"],
    }
},
{
    word:"hablar",
    Definition:"A definiition for the word hablar",
    conjugation: {
        present: ["como", "comes", "come", "comemos", "coméis", "comen"],
        preterite:["comí", "comiste", "comió", "comimos", "comisteis", "comieron"],
    }
}

With this design any word can be easily searched. Then the infinitive property can be used to search the Verbs collection.

The problem now is that two searches are required to find a word. What is worse is that to find an infinitive like the word comer you first search the words Collection to find the same word comer before looking it up in the Verbs collection.

The questions

Is it unusual to have to perform two searches to find a word?

Is this a bad design or can it be improved?

Can I design this such that only one search is required.

Note:

I understand that data can be stored in multiple collections and as such multiple searches may be required. But this is different since one search must be done before searching multiple collections to get the information we require.

Upvotes: 0

Views: 74

Answers (2)

SuleymanSah
SuleymanSah

Reputation: 17888

You can go with your first design by using the Text Search feature of the MongoDB.

First we create a compound index on the fields we want to search:

db.dictionaries.createIndex(
   {
     "word": "text",
     "conjugation.present": "text",
     "conjugation.preterite": "text"
   }
 )

Then we can use $text query operator like this:

db.dictionaries.find(
    { $text: { $search: "comen" } },
    { score: { $meta: "textScore" } }
  ).sort({ score: { $meta: "textScore" } });

$text will tokenize the search string using whitespace and most punctuation as delimiters, and perform a logical OR of all such tokens in the search string.

Let's assume you have those documents in the collection:

[
  {
    "_id": ObjectId("5df8bb67646b812758d7e6c5"),
    "conjugation": {
      "present": [ "como", "comes", "come", "comemos", "coméis", "comen" ],
      "preterite": [ "comí", "comiste", "comió", "comimos", "comisteis", "comieron" ]
    },
    "word": "comer",
    "type": "infinitive",
    "definition": "A definition for the word comer"
  },
  {
    "_id": ObjectId("5df8bb9f646b812758d7e6c6"),
    "conjugation": {
      "present": [ "como", "comes", "come", "comemos", "coméis", "comen" ],
      "preterite": [ "comí", "comiste", "comió", "comimos", "comisteis", "comieron" ]
    },
    "word": "hablar",
    "type": "infinitive",
    "definition": "A definition for the word hablar"
  }
]

When we search by "comer", only the first document will be found.

When we search by "hablar", only the second document will be found.

When we search by "como" both documents will be found.

You can even specify a language for Text Index, and Spanish is one of the supported languages.

Upvotes: 1

Debu
Debu

Reputation: 21

Please have a look at the link below to know how you can query an array in MongoDB https://docs.mongodb.com/manual/tutorial/query-arrays/.

Upvotes: 0

Related Questions