MongoDB Relational Data Structures with array of _

Question

We have been using MongoDB for some time now and there is one thing I just cant wrap my head around. Lets say I have a a collection of Users that have a Watch List or Favorite Items List like this:

usersCollection = [
    {
        _id: 1,
        name: "Rob",
        itemWatchList:[
            "111111",
            "222222",
            "333333"
        ]

    }
];

and a separate Collection of Items

itemsCollection = [
    {
        _id:"111111",
        name: "Laptop",
        price:1000.00
    },
    {
        _id:"222222",
        name: "Bike",
        price:123.00
    },
    {
        _id:"333333",
        name: "House",
        price:500000.00
    }
];

Obviously we would not want to insert the whole item obj inside the itemWatchList array because the items data could change i.e. price.

Lets say we pull that user to the GUI and want to diplay a grid of the user itemWatchList. We cant because all we have is a list of ID's. Is the only option to do a second collection.find([itemWatchList]) and then in the results callback manipulate the user record to display the current items? The problem with that is what if I return an array of multiple Users each with an array of itemWatchList's, that would be a callback nightmare to try and keep the results straight. I know Map Reduce or Aggregation framework cant traverse multiple collections.

What is the best practice here and is there a better data structure that should be used to avoid this issue all together?

Answer 1

You have 3 different options with how to display relational data. None of them are perfect, but the one you've chosen may not be the best option for your use case.

Option 1 - Reference the IDs This is the option you've chosen. Keep a list of Ids, generally in an array of the objects you want to reference. Later to display them, you do a second round-trip with an $in query.

Option 2 - Subdocuments This is probably a bad solution for your situation. It means putting the entire array of documents that are stored in the items collection into your user collection as a sub-document. This is great if only one user can own an item at a time. (For example, different shipping and billing addresses.)

Option 3 - A combination This may be the best option for you, but it'll mean changing your schema. For example, lets say that your items have 20 properties, but you really only care about the name and price for the majority of your screens. You then have a schema like this:

usersCollection = [
    {
        _id: 1,
        name: "Rob",
        itemWatchList:[
            {
                _id:"111111",
                name: "Laptop",
                price:1000.00
            },
            {
                _id:"222222",
                name: "Bike",
                price:123.00
            },
            {
                _id:"333333",
                name: "House",
                price:500000.00
            }
        ]
    }
];

itemsCollection = [
    {
        _id:"111111",
        name: "Laptop",
        price:1000.00,
        otherAttributes: ...
    },
    {
        _id:"222222",
        name: "Bike",
        price:123.00
        otherAttributes: ...
    },
    {
        _id:"333333",
        name: "House",
        price:500000.00,
        otherAttributes: ...
    }
];

The difficulty is that you then have to keep these items in sync with each other. (This is what is meant by eventual consistency.) If you have a low-stakes application (not banking, health care etc) this isn't a big deal. You can have the two update queries happen successively, updating the users that have that item to the new price. You'll notice this sort of latency on some websites if you pay attention. Ebay for example often has different prices on the search results pages than the actual price once you open the actual page, even if you return and refresh the search results.

Good luck!