Relational database design to mongoDB/mongoose design

Question

I have recently started using mongoDB and mongoose for my new node.js application. Having only used relational databases before I am struggling to adapt to the mongoDB/noSQL way of thinking such as denormalization and lack of foreign key relationships. I have this relational database design:

**Users Table**

user_id
username
email
password

**Games Table**

game_id
game_name

**Lobbies Table**

lobby_id
game_id
lobby_name

**Scores Table**

user_id
game_id
score

So, each lobby belongs to a game, and multiple lobbies can belong to the same game. Users also have different scores for different games. So far for my user schema I have the following:

var UserSchema = new mongoose.Schema({
    username: {
        type: String,
        index: true,
        required: true,
        unique: true
    },
    email: {
        type: String,
        required: true
    },
    password: {
        type: String,
        required: true
    }
});

So my question is, how would I go about structing the relational design into mongoDB/mongoose schemas? Thanks!

---

EDIT 1

I have now tried to create all the schemas but I have no idea if this is the right approach or not.

var UserSchema = new mongoose.Schema({
    _id: Number,
    username: {
        type: String,
        index: true,
        required: true,
        unique: true
    },
    email: {
        type: String,
        required: true
    },
    password: {
        type: String,
        required: true
    },
    scores: [{ type: Schema.Types.ObjectId, ref: 'Score' }]
});

var GameSchema = new mongoose.Schema({
    _id: Number,
    name: String
});

var LobbySchema = new mongoose.Schema({
    _id: Number,
    _game: { type: Number, ref: 'Game' },
    name: String
});

var ScoreSchema = new mongoose.Schema({
    _user : { type: Number, ref: 'User' },
    _game : { type: Number, ref: 'Game' },
    score: Number
});

Answer 1

Mongoose is designed in such a way that you can model your tables relationally with relative ease and populate relational data based on the ref you defined in the schema. The gotcha is that you need to be careful with populating. If you populate too much or nest your populations you will run into performance bottle necks.

Your approach in Edit 1 is largely correct however you usually don't want to populate a remote ref based on a Number or set the _id of a model to a Number since mongo uses it's own hashing mechanism for managing the _id, this would usually be an ObjectId with _id implied. Example as shown below:

var ScoreSchema = new mongoose.Schema({
    user : { type: Schema.Types.ObjectId, ref: 'User' },
    game : { type: Schema.Types.ObjectId, ref: 'Game' },
    score: Number
});

If for some reason you need to maintain a number id for your records consider calling it uid or something that won't conflict with mongo / mongoose internals. Good luck!

Answer 2

First of all, you are hitting on some good points here. The beauty of Mongoose is that you can easily connect and bind schemas to a single collection and reference them in other collections, thus getting the best of both relational and non-relational DBs.

Also, you wouldn't have _id as one of you properties, Mongo will add it for you.

I've made some changes to your schemas using the mongoose.Schema.Types.ObjectId type.

var UserSchema = new mongoose.Schema({
    username: {
        type: String,
        index: true,
        required: true,
        unique: true
    },
    email: {
        type: String,
        required: true
    },
    password: {
        type: String,
        required: true
    },
    scores: [{ type: Schema.Types.ObjectId, ref: 'Score' }]
});

var GameSchema = new mongoose.Schema({
    name: String
});

var LobbySchema = new mongoose.Schema({
    _game: { 
       type: mongoose.Schema.Types.ObjectId, 
       ref: 'Game' 
     },
    name: String
});

var ScoreSchema = new mongoose.Schema({
    _user : { 
         type: mongoose.Schema.Types.ObjectId, 
         ref: 'User' 
       },
    _game : { 
         type: mongoose.Schema.Types.ObjectId, 
         ref: 'Game' 
       },
    score: Number
});

This will allow you to query your database and populate any referenced collections and objects.

For example:

ScoreSchema.find({_id:##userIdHere##})
           .populate('_user')
           .populate('_game')  
           .exec(function(err, foundScore){
                   if(err){
                      res.send(err)
                    } else {
                 res.send(foundScore)
   }
}

This will populate the related user and game properties.