CODEWITHSUNDEEP
javascriptnode.jsmongodbdatabase-designmongoose
RMK147
RMK147

Reputation: 191

Relational database design to mongoDB/mongoose design

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
});

Upvotes: 7

Views: 11635

Answers (4)

Lev Chlumov
Lev Chlumov

Reputation: 126

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.

Upvotes: 5

Kyle Campbell
Kyle Campbell

Reputation: 186

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!

Upvotes: 13

yitsushi
yitsushi

Reputation: 1211

As you edited the post, I think it would be good. At least not bad :)

Check Mongoose Query Population. It's very useful to get related data.

var mongoose = require('mongoose'),
    ObjectId = mongoose.Schema.Types.ObjectId

// code, code, code

function something(req, res) {
  var id = req.params.id
  // test id
  return Lobby.findOne({_id: new ObjectId(id)})
    .populate('_game')
    .exec(function(error, lobby) {
       console.log(lobby._game.name);
     });
}

Upvotes: 4

Chris
Chris

Reputation: 1611

Two ways (that I know of). You store an id (that is indexed) and once you query the first table, you then query the second table to grab info from that, as there are no joins. This means that if you grab say, user id's from one table, you will then need to make multiple queries to the user table to get the user's data.

The other way is to store it all in one table, even if it's repetitive. If all you need to store is for example, a user's screen name with something else, then just store it with the other data, even if it's already in the user table. I'm sure others will know of better/different ways.

Upvotes: 0

Related Questions