Trello like data modeling

Working with Data
1

Hi,

I am new at using mongodb and I am working on a Trello like app, where you can create boards with columns and tickets in it.

I have started to model the DB like this.
Boards collection

{
_id: 1,
columns: [
        {
            canAddTicket: true,
            display: true,
            isDeleteable: false,
            id: 1,
            name: 'Todo',
            nameSanitized: 'todo',
            position: 1,
            tasks: [{
                  title: 'My task'
                  archived: false
                  done: false
                  number: 1
                  id: 1
                },
                {
                  title: 'My task 2'
                  archived: false
                  done: false
                  number: 2
                  id: 2
                }
            ]
        },
        {
            canAddTicket: true,
            display: true,
            isDeleteable: false,
            id: '0828e54f-9d65-4451-8740-3a333b868265',
            name: 'Doing',
            nameSanitized: 'doing',
            position: 2,
            tasks: []
        }
    ]
}

Tasks collection

{
    _id: 1
    done: false,
    title: 'My task',
    description: 'My big desc',
    members: [
    ],
    comments: [
    ],
    columnId: 1,
    boardId: 1
}

The idea is when I create tasks, I store the entire version with big description, comments, history and every info in it.
Then I add a small version in a column.
This way, I can display my board and the tickets with one query by querying the Boards document
If I need to get the detail, I will query the Tasks document.

I also need to change columns position, for now I have planned to use $pull and $push to change position.

I came here to humbly ask the community if this solution looks good to you, or will it cause performance issue to update columns position ? I am also worried about concurrency, if columns got moved when people move ticket from a column to another one ? Maybe you will think of another problem I cannot think about.

Thanks a lot in advance,

2

Hi Again,

Thanks to jason_tran who edited my first post (I couldn’t find the code embedding …).

I have thought about 2 others solutions wich are the following.

Boards collection

{
  _id: 1,
}

Columns collection

{
  _id: 1,
  name: 'Test',
  boardId: 1,
  position: 1
}

Tasks collection

{
  _id: 1,
  title: 'Test',
  description: 'super long description',
  columnId: 1
}

This way I handle everything with code and multiple queries. I don’t think there will be concurrency issues (as we functionnaly block task editing at the same time on the frontend with websocket).

The other solution is the following (relation and use of lookup).
Boards collection

{
  _id: 1,
  columns: [
    {_id: 1, position: 2},
    {_id: 2, position: 1}
  ]
}

Columns collection

{
  _id: 1,
  name: 'Test',
  tasks: [1, 2]
}

Tasks collection

{
  _id: 1,
  title: 'Test',
  description: 'super long description'
}

I am waiting for the validation / suggestions of mongodb masters :slight_smile:
Have a nice day.
Thanks,

1 Like
3

Hi @Christopher_N_A2 ,

I think that using an extended reference as you initially started is an approach I would go to.

Meaning a standard dashboard is potentially a single document with all the needed information to store the dashboard.

If the user clicks on a specific column/task there is an _id query to the bigger document storing all the extra information. Frequently editable information should exist in one place to avoid many updates due to single value change
…

Now you can also utelise the outlier pattern for dashboards with extreme large amount of tasks or columns. So when you cross for example the 100 tasks per board you will store each next task in an overflow document/s.

One last suggestion is to think on avoiding $pull and $push for array position change as it has pretty significant overhead on concurrency. I would recommend switching array elements on application side and storing the entire array to replace the old one.

Alternatively maybe use arrayFilter updates to switch the data between elements.

Thanks
Pavel

1 Like
4

Hi Pavel,

Thanks a lot for the fast, succinct but extremely precise and informative answer !
You went far from what I expected with these tips and patterns !

Me and my team really appreciate it, we feel relieved and can sleep peacefully :slight_smile:

Mongodb feels great to use, keep it up !
Have a nice day,

2 Likes
5

@Christopher_N_A2 ,

Thanks so much for your kind words.

Keep building amazing things with MongoDB !

Thanks
Pavel

2 Likes
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.