如何将多对多关系形式的 SQL 转换为 mongoDB 集合? [英] How to convert many-to-many relation form SQL to mongoDB colllection?

问题描述

我在 SQL 中有 3 个具有多对多关系的表.第一个表帐户"有 3 个字段(AccountId、UserName、Email).第二个表设备"有 2 个字段(DeviceId 和 DeviceCode)和表关联"有 4 个字段(AccountId、DeviceId、LastActivityDate、HasException)

I have 3 table in SQL that have many-to-many relation.The first table "account" have 3 field (AccountId,UserName,Email).The second table "device" have 2 field (DeviceId and DeviceCode) and table "Association" have 4 field (AccountId,DeviceId,LastActivityDate,HasException)

如何将它们转换为 mongoDB.根据 mongodb.org 的建议，我将集合Associaton"嵌入到设备集合和帐户集合中，但是每次我的系统有新事务时，字段LastAccessDate"总是更新.

How to convert them to mongoDB. From suggestion of mongodb.org I embed collection "Associaton" into device collection and account collection but the field "LastAccessDate" always was updated every time when my system have new transaction.

-帐号集合:{

"用户名": "xxx",电子邮件":1，

"UserName" : "xxx", "Email" : 1,

关联":[{"DeviceId" : "4f45c4b73174ec818148d5c5","LastActivityDate" : "2012 年 2 月 23 日星期四 11:44:19 GMT +07:00",有异常":假}, {"DeviceId" : ObjectId("4f45c5e93174ec818148d5c7"),"LastActivityDate" : "2012 年 2 月 23 日星期四 11:52:05 GMT +07:00",异常":空}]}

"Association" : [{ "DeviceId" : "4f45c4b73174ec818148d5c5", "LastActivityDate" : "Thu, 23 Feb 2012 11:44:19 GMT +07:00", "HasException" : false }, { "DeviceId" : ObjectId("4f45c5e93174ec818148d5c7"), "LastActivityDate" : "Thu, 23 Feb 2012 11:52:05 GMT +07:00", "Exception" : null }] }

设备集合:{

"DeviceCode" : "abcjxhcbxkjcnxkcjhdiofjdojdlkdknd",关联":[{"AccountId": "34fdgfdy5676","LastActivityDate": "02/03/2012",有异常":假}, {"AccountId": "34fdgfdy5dd676","LastActivityDate" : "02/03/2012",HasException":真}],

"DeviceCode" : "abcjxhcbxkjcnxkcjhdiofjdojdlkdknd", "Associations" : [{ "AccountId" : "34fdgfdy5676", "LastActivityDate" : "02/03/2012", "HasException" : false }, { "AccountId" : "34fdgfdy5dd676", "LastActivityDate" : "02/03/2012", "HasException" : true }],

}

推荐答案

对于您的问题，我看到了多种解决方案:

I see multiple solutions for your problem:

为了避免连接，必须在客户端在 NoSQL 数据库中完成，将所有内容放入一个集合中.如果你不经常更新，你可以这样做

To avoid joins, that have to be done client-side in NoSQL databases, put everything into one collection. If you do not update very often, you could do

{
   _id,
   LastAccessDate,
   HasException,
   Account : {
        AccountID,
        UserName,
        Email },
   Device : {
        DeviceID,
        DeviceCode }
}

这会导致数据重复，但如果性能(无连接)优先于内存效率，那么它可能是适合您的解决方案.由于数据被多次存储，更新对服务器的要求更高.IE.更新电子邮件地址看起来像

This results in duplicate data, but if performance (no joins) comes before memory efficiency, it's probably a suitable solution for you. Since the data is stored multiple times, updates are more demanding for the server. I.e. updating the Email address would look like

db.myColl.update({"Account.UserName" : "User ToUpdate"}, {"$set" : {"Account.Email" : "[e-mail]"} });

由于您不再需要 AccountID 和 DeviceID 来加入，您也可以删除这两个字段.

As you don't need the AccountID and DeviceID to join anymore, you can also drop these two fields.

当你有频繁的更新，而不是嵌入时，你可以使用引用.我认为您必须在客户端解决它们.然后，您将拥有三个集合:

When you have frequent updates, instead of embedding, you could use References. I think you have to resolve them client-side. You then have three collections:

帐户:

{
  _id,
  UserName,
  Email
}

设备:

{
  _id,
  DeviceCode
}

协会:

{
  _id,
  account : {
        "$ref" : "Account",
        "$id" : ... }
  device : {
        "$ref" : "Device",
        "$id" : ... }
}

这种方式你没有任何重复，但你必须处理引用.

You don't have any duplication this way, but you have to deal with references.

这篇关于如何将多对多关系形式的 SQL 转换为 mongoDB 集合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！