from relation table to mongodb

Question

I'm trying to create a MongoDB design (but I'm new at mongo) based on a relation database schema.

My question is: How far do I denormalize my schema?:

-Users
--Wallets[field1,field2]
---Items[field1,field2]
----Transactions[field1,field2]
--....

Users -> linked to a lot of tables.
Wallets -> a user can have more wallets.
Items -> a wallet can have more Items(the field values of Items will not regulary change).
Transactions -> a item can have more Transactions.
My idea is to:

Split Users in a document
Group Wallets and Transactions (create a reference to User_id (at the wallet level) and Item_id (on the transaction level)) in a document
Items in a document

Will this be a right approach?

As to why MongoDB: it is because I'm creating a Meteor App.

Do wallets and transactions need to have the possibility of being linked to multiple users? Or does each wallet and transaction have one user? — Abdullah Rasheed, Oct 22 '15 at 13:40
The MongoDB Docs contains a good section on [Data Models](https://docs.mongodb.org/manual/core/data-modeling-introduction/) in MongoDB — JeremyK, Oct 22 '15 at 21:44

Abdullah Rasheed · Answer 1 · 2015-10-22T14:24:20.513

Since MongoDB has no joining facilities you will be either embedding data or referencing to it. There are trade-offs for both, and when trying to decide which to do you can look at this criteria.

Embedding is better for...

Small subdocuments
Data that does not change regularly
When eventual consistency is acceptable
Documents that grow by a small amount
Data that you’ll often need to perform a second query to fetch
Fast reads

References are better for...

Large subdocuments
Volatile data
When immediate consistency is necessary
Documents that grow a large amount
Data that you’ll often exclude from the results
Fast writes

For your scenario it looks like you would have the following

User Document - Referencing Wallets

{
   "_id" : ObjectId("512512a5d86041c7dca81914"),
   "name" : "John Doe",
   "wallets" : [
     ObjectId("512512ced86041c7dca81916"),
     ObjectId("512512ced86041c7dca81917"),
     ObjectId("512512ced86041c7dca81918"),
    ]
}

Wallets Documents - Reference Items

{
    "_id" : ObjectId("512512a5d86041c7dca81913"),
    "items": [
         ObjectId("512512ced86041c7dca81916"),
         ObjectId("512512ced86041c7dca81920"),
         ObjectId("512512ced86041c7dca81921"),
    ]
}

Items - Referencing Transactions

    {
         "_id" : ObjectId("512512a5d86041c7dca81913"),
         "transactions" : [
             ObjectId("512512ced86041c7dca81952"),
             ObjectId("512512ced86041c7dca81953"),
             ObjectId("512512ced86041c7dca81954")
          ],
          other fields..
     },

Transaction Document

{
     "_id" : ObjectId("512512a5d86041c7dca81914"),
     other fields...
}

Is this way difficult to retreive the all the data from the database: For example User x all tranactions? — Phoenix, Oct 23 '15 at 06:28
@Phoenix will wallets, items, or transactions need to be accessed outside of the user collection? — Abdullah Rasheed, Oct 23 '15 at 09:43

score 0 · Accepted Answer · edited May 23 '17 at 12:08

As an alternative to @inspired's answer (which translates your schema straight to MongoDB and models via document references), it might be easier to embed data into the user document.

If you choose to embed, it would look like:

{ name: 'username',
  id: ObjectId("512512a5d86041c7dca81914"),
  wallets: [ {
    wallet_name: 'its_name_if_required',
    field_one: 'its_content',
    field_two: 'its_content',
    items: [ {
      item_field_one: 'content_item_1',
      item_field_one: 'other_content_item_1',
      transactions: [ {
        whatever: 1,
        a_transaction: 'some string',
        contains: new Date()
      }, {
        whatever: 1,
        another_transaction: 'some string',
        contains: new Date()
      } ] // end transactions
    }, {
       // another item, omitted for brevity
    } ] // end items
  }, {
    // another wallet, omitted for brevity again
  } ] // end wallets
} // end user

This allows easy accessing of f.ex. transactions via

user.wallets[0].items[1].transactions[0].whatever

Note that not all transactions have to have the same fields. Here, transactions[1] has a field named another_transaction. Note also that JSON does not allow comments (they are for understanding better).

A limitation of this is the 16MB document maximum size of MongoDB.

If each user amasses more than 16 MB of total data, you will probably implement @inspired's answer or something in between, where you store (for example) everything up to items inside the user, together with a list of transaction_ids. But it depends on how many transactions you expect and how often they are going to change.

For the question of embedding vs referencing, see MongoDB relationships: embed or reference?.

If Items change a lot and Transactions don't really change but are added a lot. Would you go with the embedded solution? — Phoenix, Oct 23 '15 at 07:05
@Phoenix: I might go all-embedded, but would probably separate items out into a separate document. If a user's item's transactions can grow to more than 16 MB, you *have to* reference. If not, embeddings are much easier to work with (as also said in http://stackoverflow.com/questions/5373198/mongodb-relationships-embed-or-reference) — serv-inc, Oct 23 '15 at 12:49

from relation table to mongodb

2 Answers2