Relational database design to mongoDB/mongoose design

database-design, javascript, mongodb, mongoose, node.js

Solution

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!

Problem

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

Original source