关系数据库设计到mongoDB / mongoose设计

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:

我最近开始使用mongoDB和mongoose作为我的新node.js应用程序。在我努力适应mongoDB / noSQL思维方式(例如非规范化和缺乏外键关系)之前,只使用了关系数据库。我有这个关系数据库设计:

**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!

所以我的问题是,我如何将关系设计结构化为mongoDB / mongoose模式?谢谢!

---

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

4 个解决方案

#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.

Mongoose的设计方式使您可以相对轻松地对表进行关系建模,并根据您在模式中定义的引用来填充关系数据。问题是你需要小心填充。如果您填充太多或嵌套您的人口,您将遇到性能瓶颈。

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:

您在Edit 1中的方法基本上是正确的,但是您通常不希望基于Number填充远程ref或将模型的_id设置为Number,因为mongo使用它自己的散列机制来管理_id,这通常是隐含了_id的ObjectId。示例如下图所示:

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!

如果由于某种原因你需要为你的记录维护一个数字id,可以考虑将其称为uid或者与mongo / mongoose内部不冲突的东西。祝你好运!

#2

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.

检查Mongoose查询人口。获取相关数据非常有用。

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

#3

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.

首先,你在这里找到了一些好处。 Mongoose的优点在于,您可以轻松地将模式连接并绑定到单个集合,并在其他集合中引用它们,从而充分利用关系数据库和非关系数据库。

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

此外,你不会将_id作为你的一个属性,Mongo会为你添加它。

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

我使用mongoose.Schema.Types.ObjectId类型对您的模式进行了一些更改。

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.

这将填充相关的用户和游戏属性。

#4

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.

两种方式(我知道)。您存储一个id(已编制索引),一旦您查询第一个表,然后查询第二个表以从中获取信息,因为没有连接。这意味着如果您从一个表中获取说明用户ID,则需要对用户表进行多次查询以获取用户的数据。

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.

另一种方法是将它们全部存储在一个表中,即使它是重复的。如果您需要存储的只是一个用户的屏幕名称,那么只需将其与其他数据一起存储,即使它已经存在于用户表中。我相信其他人会知道更好/不同的方式。