Structuring mogodb schema

Question

I'm playing around with MongoDB and was wondering what best practices are for how a SQL-ish schema may correspond to MongoDB. Here are the tables/data I have so far:

• user
  • id
  • email
  • name
• answer
  • user_id (FK user.id)
  • tag
  • upvotes
• repo
  • id
  • owner
  • name
  • description
  • stars
• repo_tag
  • repo_id (FK to repo.id)
  • tag
  • is_language
  • percentage
• repo_contrib
  • repo_id (FK to repo.id)
  • user_id (FK to user.id)
  • lines_of_code

The structure goes something like this:

• user
  • answer (left outer)
  • repo_contrib (left outer)
    • repo
      • repo_tag

Note: All users will have at least one answer or one repo, but does not necessarily have to have both.

How might I put this into a mongo schema? Would this be one 'collection' ? Or would this be two collections: one for user, and one for repo; or more?

My queries will be something like: "Grab all users with an Answer with tay [Python] with more than 2 upvotes or a repo with the [Python] tag with more than two stars.

Answer 1

Let me divide this to couple of steps:

STEP 1 - MONGODB and MONGOOSE

MongoDB is a document based database. Each record in a collection is a document, and every document should be self-contained (it should contain all information that you need inside it).

Since MongoDB is a no-relation database, you can not create relations between collections, but you can store a reference of one collection document as a property of another collection document. To help you manage all of this, there is a great package called Mongoose, which will allow you to create a Model for each Collection. After you define Models, Mongoose will allow you to easily make queries to database.

STEP 2 - DEFINING MODELS

As we said, documents should be self-contained, so they should have all information that you need inside them. We can have 2 approaches based on your example:

APPROACH 1:

Create one collection for each table that you have in your relational database. This is the best practice when you have documents with a lot of data, because it is scalable.

APPROACH 2:

Create 3 Collections - USERS, ANSWERS and REPOS. Because repo_contrib does not have a lot of data, you can store all user's contributions in a USERS document. That way, when you fetch a User document, you will have everything that you need in one place. The same goes for repo_tag - we can store all repo's tags in a REPOS document.

APPROACH 3:

Create 2 Collections - USERS and REPOS. The same as APPROACH 2, but you can also add all user's answers to the USERS document.

RECOMMENDATION:

I would go with APPROACH 2 in this case, since repo_contrib and repo_tag does not store big data and can easily be stored in USERS and REPOS documents with no problem. Also, if we go with this approach, it will make querying database a lot easier. The reason why I didn't choose option 3 is because theoretically user can have thousands or tens of thousands of answers, and it would not scale well.

STEP 3 - IMPLEMENTATION

NOTE: MongoDB will automatically assign _id to each document, so you don't have to define id property when implementing Models.

Tables from your relational database example can be mapped to collections like this (This implementation is for APPROACH 2):

USERS Collection:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

var schema = new Schema({
    email: { type: String, required: true, unique: true },
    name: { type: String, required: true, unique: false },
    contributions: [{
      repo_id: { type: mongoose.Schema.Types.ObjectId, ref: 'REPOS' },
      lines_of_code: { type: Numeric, ref: 'REPOS' }
    }]
});
const Users = mongoose.model('USERS', schema);
module.exports = Users;

ANSWERS Collection:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

var schema = new Schema({
    user_id: { type: mongoose.Schema.Types.ObjectId, ref: 'USERS', required: true },
    tag: { type: String, required: true, unique: false },
    upvotes:{ type: Number, default: 0, unique: false }
});
const Answers = mongoose.model('ANSWERS', schema);
module.exports = Answers;

REPOS Collection:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

var schema = new Schema({
    owner: { type: mongoose.Schema.Types.ObjectId, ref: 'USERS', required: true },
    name: { type: String, required: true, unique: false },
    description: { type: String, required: false, unique: false },
    stars:{ type: Number, default: 0, unique: false },
    tags: [{
      name:  { type: String, required: true, unique: false },
      is_language: {type: Boolean, required: true, unique: false},
      percentage:{ type: Number, default: 0, unique: false }
    }]
});
const Repos = mongoose.model('REPOS', schema);
module.exports = Repos ;

STEP 4 - POPULATION AND DATABASE QUERIES

One of the best features of the Mongoose is called population. If you store a reference of one collection document as a property of another collection document, when performing querying of the database, Mongoose will replace references with the actual documents.

Example 1:

Let us first take as an example the first query that you suggested: Find all users with an Answer with tag [Python] with more than 2 upvotes. Since we stored user_id in ANSWERS Collection as a reference to the document from the USERS collection, that means that we can just query the ANSWERS Collection, and when returning the final result Mongoose will go to USERS collection and replace the references with the actual User documents. The database query that will perform this looks like this:

const ANSWERS = require('../models/answers');

ANSWERS.find({
  "tag": "Python",
  "upvotes": {
    "$gt": 2
  }
}).populate('user_id');

Example 2:

Second query that you have suggested is: Find all repos with the [Python] tag with more than two stars. Since we are storing all repo's tags in one array, we just need to check if that array contains an item with the name field that is equal to Python, and that the repo's stars fields is greater than 2. The database query that will perform this looks like this:

const REPOS = require('../models/repos');

REPOS.find({
  "tags.name": "Python",
  "stars": {
    "$gt": 2
  }
})

Here is also the working example: https://mongoplayground.net/p/rgBtVVDgPzG