Mongoose scheme design many-to-many

Question

I'm trying to come up with a mongoose scheme architecture/design for a task management app.

Desired functionality and models:

Models:

• User - has boards
• Boards - has lists
• Lists - has cards
• Cards

Functionality:

• User can create a board
• User can create a list in the board
• User can create a card in the list
• User can add members to the board
• User can add members to the card from the board members
• User can delete board, deleting all the lists and cards, and removing members
• User can delete list, deleting all the cards in the list
• User can delete card, removing all the members from the card
• User can update the list position (sort) in the board

• User can update cards position (sort) in the list

• Additional functionality (Optional)

  • User can add comments to the card
  • Card activity logging (On move, on edit, on comment)

I know its alot to ask, but how would go about designing a scheme for this kind of functionality?

Would child-parent references be the best solution?

Ex.

User:

{
 name: 'Bob',
 _id: '2626'
}

Board:

{
 name: 'Board name',
 _id: '123456',
 members: [
  { type: ObjectID, ref: 'user' } // '2626'
 ]
}

List:

{
 name: 'List name',
 _id: '2525',
 boardId: { type: ObjectID, ref: 'board' } // '123456'
}

Card:

{
 name: 'Card name',
 boardId: { type: ObjectID, ref: 'board' } // '123456',
 listId: { type: ObjectID, ref: 'list' } // '2525'
}

How would I go about querying this type of structure?

• 1) Get a list of boards by users id
• 2) Get a list of lists by board id
• 3) Get a list of cards by board id & listid

So for the board view I would go and grab all the lists, but then I would have to go and get all the cards for the each list, seems not really efficient.

Perhaps when entering board view I should query just the cards by the board id, but then how do I get the lists, and put each card into its own list?

How do I handle deleting the card or moving a card from one list to another?

Don't be hard on me please, I'm really new to the mongodb world, but I'm really trying my best.

Answer 1

The schema you've defined is pretty good, here's how the flow would be.

Initially, when a user signs in, you'll need to show them the list of boards. This should be easy since you'll just do a find query with the user_id on the board collection.

Board.find({members: user_id}) // where user_id is the ID of the user

Now when a user clicks on a particular board, you can get the lists with the board_id, similar to the above query.

List.find({boardId: board_id}) // where board_id is the ID of the board

Similarly, you can get cards with the help of list_id and board_id. Card.find({boardId: board_id, listId: list_id}) // where board_id is the ID of the board and listId is the Id of the list

Now, let's look at cases wherein you might need data from 2 or more collection at the same time. For example, when a user clicks on board, you not only need the lists in the board but also the cards in that board. In that case, you'll need to write an aggregation as such,

            Board.aggregate([
            // get boards which match a particular user_id
            {
                $match: {members: user_id}
            },
            // get lists that match the board_id
            {
                $lookup:
                {
                    from: 'list',
                    localField: '_id',
                    foreignField: 'boardId',
                    as: 'lists'
                }
            }
        ])

This will return the boards, and in each board, there'll be an array of lists associated with that board. If a particular board doesn't have a list, then it'll have an empty array.

Similarly, if you want to add cards to the list and board, the aggregation query will be a bot more complex, as such,

            Board.aggregate([
            // get boards which match a particular user_id
            {
                $match: {members: user_id}
            },
            // get lists that match the board_id
            {
                $lookup:
                {
                    from: 'list',
                    localField: '_id',
                    foreignField: 'boardId',
                    as: 'lists'
                }
            },
            // get cards that match the board_id
            {
                $lookup:
                {
                    from: 'card',
                    localField: '_id',
                    foreignField: 'boardId',
                    as: 'cards'
                }
            }
        ])

This will add an array of cards as well to the mix. Similarly, you can get cards of the lists as well.

Now, let's think about whether this is the best schema or not. I personally think the schema you suggested is pretty good because another way to go about it would be to store IDs in the parent collection, which will let you use populate to get the data instead of a lookup query.

For example, storing list ids in board collection. The downside to this is, whenever a new list is added, you need to add that list in the list collection and also update the board to which the list is connected to (add the list ID), which I think is too tedious.

Finally, some suggestion on the schema you've given, I think you should add user_id (creator's ID) in every collection, cause there are many cases wherein you need to show the name of the user who created that particular board or list or anything else and also since you have the feature of adding users to a particular card, etc I think you should have two fields, one is creator_id and the other should be associated_users, which will be an array (obviously you can choose better names).

You should add position field in cards and other collections which you want to sort by position. This field should be a number.

Deleting a card or moving it from one list to another should be pretty easy and self-explanatory by now.

Edit 1: Based on the comment
You don't need to assign cards to the list 'after' the aggregation, you can do this in your aggregation itself, so it'll be something like this,

Board.aggregate([
    // get boards which match a particular user_id
    {
        $match: { members: user_id }
    },
    // get lists that match the board_id
    {
        $lookup:
        {
            from: 'list',
            localField: '_id',
            foreignField: 'boardId',
            as: 'lists'
        }
    },
    // unwind lists (because it's an array at the moment)
    {
        $unwind: '$lists'
    },
    // Now you have object named lists in every board object
    // get cards that match the list_id (note that the cards contain list_id)
    {
        $lookup:
        {
            from: 'card',
            localField: '_id',
            foreignField: 'listId',
            as: 'cards'
        }
    },
    // now merge back the objects and get a simple object for each boardID
    {
        $group: {
            _id: "$_id",
            members: { $addToSet: "$members" },
            lists: { $addToSet: "$lists" }
        }
    }
])

This will give you something like this,

data = {
    '_id': '123456',
    'members': [
        {
            name: 'Bob',
            _id: '2626'
        },
        {
            name: 'Matthew',
            _id: '2627'
        }
    ],
    'lists': [
        {
            name: 'List 1',
            _id: '2525',
            boardId: '123456',
            cards: [
                {
                    name: 'Card 1',
                    boardId: '123456',
                    listId: '2525'
                },
                {
                    name: 'Card 2',
                    boardId: '123456',
                    listId: '2525'
                }
            ]
        },
        {
            name: 'List 2',
            _id: '2526',
            boardId: '123456',
            cards: [
                {
                    name: 'Card 3',
                    boardId: '123456',
                    listId: '2525'
                },
                {
                    name: 'Card 4',
                    boardId: '123456',
                    listId: '2525'
                }
            ]
        }
    ]
}

So basically, you can get the list and the cards for those list in a single query itself and it is quite efficient.

Now coming to the two queries you asked for,

1. Card moved from one list to another, just edit the listId field in the card document to the new listID (it's quite simple actually).

2. Card moved up a position in a list

As I said, if you want the position you need to add a field called position in the documents and then whenever the card is moved you need to change the value of 'position' of those cards. In the aggregation, you just need to add another stage called '$sort' and sort it according to the position value. This is going to be a bit tedious since whenever you move a card up, you need to update the position of the card above as well.