Building User Group and Group Hierarchy in MongoDB

Question

We're currently looking to migrate our legacy monolith User Groups implementation to our new Microservices platform. The current implementation is rather bloated and complicated, so we're looking to stream line things a bit and hopefully make it the system a bit easier to use and maintain. The User Groups also double up as our permissions table.

The Monolith is all MySQL and has the following tables.

groups - Defines the groups, contains the groups_hierarchy_fk if the Group is part of a Hierarchy
groups_hierarchy - Defines the Group Hierarchy
groups_hierarchy_flat - It's not really a flat table, but is basically a linking table for the parent group and child group. It's meant to speed up searching the hierarchies, not the best structure tbh.
groups_users A linking table between Groups and Users, which shows which Users are in which Groups. Currently, the most amount of users we have in a single Group is over 1 million, while the most Groups a single user belongs to is in the mid 30s

As part of the move to Microservices, we evaluate if the current data source (MySQL) makes sense or not. A decision has already been made to move out Users table from MySQL to MongoDB, as the schema-less design is something we really need for our users, as each client can define their user fields. We also no longer have a need for the permisisons part of the Legacy Groups implementation, as that is handled with a different implementation in our Microservices environment.

I was thinking about moving our Groups implementation to MongoDB too, as I think it'll work much better for Tree Structures and searching them. I have two potential designs, but since no-one of the team is really an expert in MongoDB, I figured I'd crowdsource some opinions of the design to see if it's any good.

First Option

This option basically has the full Group Hierarchy defined in a single document, which makes it much easier to see the structure when looking directly at the data and possibly faster for searching too (I'm yet to actually do a POC).

Groups Rather simple document structure, simple define Group names with an id.

{
    _id: ObjectId("631f927fdcec0ea55c5a51f0"),
    name: "Default Users"
}

{
    _id: ObjectId("631f9290baeb487ed61cb00d"),
    name: "BE"
}

{
    _id: ObjectId("631f92ae9fa0b625cf9aaf20"),
    name: "Product"
}

{
    _id: ObjectId("631f92b7d7f7d457d32936bf"),
    name: "Finance"
}

{
    _id: ObjectId("631f962cb605dc6ff44cad35"),
    name: "Architecture"
}

{
    _id: ObjectId("631f96db145359142fa99a6c"),
    name: "Honey Badger"
}

{
    _id: ObjectId("631f96eb8a9e751d6893a5f5"),
    name: "Hatchery"
}

{
    _id: ObjectId("631f96ffd16b8cf34fb54771"),
    name: "Framework"
}

{
    _id: ObjectId("631f971bf25da6fcf5724e83"),
    name: "Vitality"
}

{
    _id: ObjectId("631fa2f8c600e66b37d07c78"),
    name: "Social Soccer Team",
}

Users

So instead of having a linking table between Groups and Users, we would simple embed the list of Groups directly into the Users document.

{
    _id: ObjectId("631f92f0670a5688a2ed3432"),
    username: "first.last@domain.com",
    first_name: "First",
    last_name: "Last",
    // Other fields...
    groups: [
        ObjectId("631f927fdcec0ea55c5a51f0"),   // Default User Group
        ObjectId("631f971bf25da6fcf5724e83"),   // Vitality
        ObjectId("631fa2f8c600e66b37d07c78")    // Social Soccer Team
    ]
}

Group Hierarchy

In this document, we define the name of the Hierarchy, as well as include the actual Hierarchy structure it's self. While I like the idea of having the full hierarchy structure in a single document, I wonder if it'll actually be better, since indexing it might end up be harder and therefore, the searching would be slower.

{
    _id: ObjectId("631f9c588d7aace74e8781e4")
    name: "Default Organization Chart"
    groups: [
        {
            group_id: ObjectId("631f9290baeb487ed61cb00d"), // BE
            groups: [
                {
                    group_id: ObjectId("631f962cb605dc6ff44cad35"), // Architecture
                    groups: [
                        {
                            group_id: ObjectId("631f96ffd16b8cf34fb54771") // Framework
                        }, {
                            group_id: ObjectId("631f971bf25da6fcf5724e83") // Vitality
                        }
                    ]
                },{
                    group_id: ObjectId("631f96db145359142fa99a6c") // Honey Badger
                },{
                    group_id: ObjectId("631f96eb8a9e751d6893a5f5") // Hatchery
                }
            ]
        }, {
            group_id: ObjectId("631f92ae9fa0b625cf9aaf20") // Product
        }, {
            group_id: ObjectId("631f92b7d7f7d457d32936bf") // Finance
        }
    ]
}

Option 2

This option for the most part was preferred by the team, as it would be easier to index and would make searching possibly faster. It's also inline with the tree structures on the MongoDB website, which means we can also start using the MongoDB Graph Lookup functionality

Group Hierarchy

We start with this document structure first this time, as it's the most simple one, as we're simply defining the name of a Group Hierarchy with an id.

{
    _id: ObjectId("631f9c588d7aace74e8781e4")
    name: "Default Organization Chart"
}

Groups

In the Groups document, we define a field to contain the id of the Group Hierarchy it belongs to (if any), as well as what the parent Group is via the id. If it's the top most level of the Group, then the id will be null.

{
    _id: ObjectId("631f927fdcec0ea55c5a51f0"),
    name: "Default Users",  
    group_heirarchy: null,
    parent_id: null
}

{
    _id: ObjectId("631f9290baeb487ed61cb00d"),
    name: "BE",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: null
}

{
    _id: ObjectId("631f92ae9fa0b625cf9aaf20"),
    name: "Product",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: null
}

{
    _id: ObjectId("631f92b7d7f7d457d32936bf"),
    name: "Finance",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: null
}

{
    _id: ObjectId("631f962cb605dc6ff44cad35"),
    name: "Architecture",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: ObjectId("631f9290baeb487ed61cb00d") // BE
}

{
    _id: ObjectId("631f96db145359142fa99a6c"),
    name: "Honey Badger",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: ObjectId("631f9290baeb487ed61cb00d") // BE 
}

{
    _id: ObjectId("631f96eb8a9e751d6893a5f5"),
    name: "Hatchery",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: ObjectId("631f9290baeb487ed61cb00d") // BE 
}

{
    _id: ObjectId("631f96ffd16b8cf34fb54771"),
    name: "Framework",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: ObjectId("631f962cb605dc6ff44cad35") // Architecture
}

{
    _id: ObjectId("631f971bf25da6fcf5724e83"),
    name: "Vitality",
    group_heirarchy: ObjectId("631f9c588d7aace74e8781e4"),
    parent_id: ObjectId("631f962cb605dc6ff44cad35") // Architecture
}

{
    _id: ObjectId("631fa2f8c600e66b37d07c78"),
    name: "Social Soccer Team",
    group_heirarchy: null,
    parent_id: null
}

Users

The document structure for users would be the same as the previous option.

Thoughts

I think overall Option 2 is more than likely the best solution of the two, but are there any other designs that might work better?

Building User Group and Group Hierarchy in MongoDB

First Option

Option 2

Thoughts

0 Answers