CODEWITHSUNDEEP
mysqlnode.jsmongodb
StackOverflowed
StackOverflowed

Reputation: 5975

MongoDB -- Sub sub children?

In an app like StackOverflow (nothing really related but it's a good example), we have Questions. Each Question in our mongodb collection has an array of Answers, and an array of Votes (either upvoting or downvoting).

In each Answer, it is possible for a User to upvote or downvote it.

So the schema would look something like this:

Question
  -> Answers []
     -> Votes []
        -> value (-1/1)
        -> username
  -> Votes []
     -> value (-1/1)
     -> username       
  -> question_text, etc.

Coming from a MySQL background this schema feels "icky" but I've been assured it's an industry practice.

Now I'm currently required to show each User what they have voted on, both in terms of Questions and Answers.

So if I have to find the Answer that a user has voted on, I would query thusly (in node):

question_collection.find(
     {'question.answers.votes.username': username}, function(e,d) {
    /* do stuff */}
 );

That query goes 4 levels deep. Is this a normal way to do it or should I introduce some normalization in the schema?

Upvotes: 0

Views: 53

Answers (1)

Joost Vunderink
Joost Vunderink

Reputation: 1236

One of the strengths of MongoDB is that you can put all relevant information about a Question inside one document, so you need only 1 database query and no joins to get all information to render the Question.

However, if you want to find everything a user has voted on, things get a bit more complicated. You can do what you do, certainly, although it won't win any performance awards.

Alternatively, you can duplicate the data in a smart way to access it in an easier way. For example, you could add two arrays to the User model:

QuestionsVoted: [{ id1: +1}, {id2: -1}, {id3: +1}],
AnswersVoted: [{ id4: +1}, {id5: -1}]

This means you need to keep this data in sync: when a user votes on a question or answer, you need to update both the question and the user. This is not that bad, because the data is written rarely and read often.

If you have other requirements that deal with votes themselves, for example statistics of votes over time, or by geographical region, you might want to create a Vote collection. And yes, you would have to keep the data in sync across 3 collections then.

It is technically possible to create foreign keys in MongoDB, but I would not recommend it in this case. You would lose some of the benefits of MongoDB, because it is not good at joins (it would require 2 separate queries).

You can read more about how to design relationships in MongoDB on their blog.

Upvotes: 1

Related Questions