How to create MongoDB schema design while dealing with single User account and multiple user (Organization account with roles)

Question

I am working on a Nodejs Express API project using mongoDB with mongoose and i would like to get some advice on best practices and going about creating an efficient schema design from community

The app deals with two type of user accounts

Account type :

• Single (default)
• Organization (can switch to from settings)

Note: In organisation account there will be a admin (owner) and other invited user and each user is assigned permission level / access level .One user will always be associated with only one account, ie he cannot be invited again to another account or start a new account if he is already part of a existing account. Also billing and shipping address is specific to account rather than user in the case of an organization account (address of user switching to organization account will be the address of Organization account )

I have completed the authentication part with the help of passport.js JWT and local strategy

i tried to develop one similar to RDBMS approach ( i used to be RDBMS guy ) and failed

Models and schemas

const userSchema = new Schema({
    first_name: String,
    last_name: String,
    email: String,
    phone: String,
    avatar: String,
    password: String,
    active: Boolean
});

const User = mongoose.model('user', userSchema);

const accountSchema =  mongoose.Schema({
    account_type: { type: String, enum: ['single', 'organization'], default: 'single' },
    organization: { type: Schema.Types.ObjectId, ref: 'organization', required: false },
    billing_address: String,
    shipping_address: String,

});

const Account = mongoose.model('account', accountSchema);

const accountUserRoleSchema =  mongoose.Schema({
    user :  { type: Schema.Types.ObjectId, ref: 'user', },
    role: { type: String, enum: ['admin', 'user'], default: 'user' },
    account: { type: Schema.Types.ObjectId, ref: 'account', required: true  }
});

const AccountUserRole = mongoose.model('accountUserRole', accountUserRoleSchema);


const permissionSchema =  mongoose.Schema({
    user :  { type: Schema.Types.ObjectId, ref: 'user', required: true },
    type: {  type: Schema.Types.ObjectId, ref: 'permissionType', required: true  },
    read: { type: Boolean, default: false, required: true  },
    write: { type: Boolean, default: false, required: true },
    delete: { type: Boolean, default: false, required: true },
    accountUser : {  type: Schema.Types.ObjectId, ref: 'account',required: true }

});

const Permission = mongoose.model('permission', permissionSchema);


const permissionTypeSchema =  mongoose.Schema({
    name :  { type: String, required: true   }

});

const PermissionType = mongoose.model('permissionType', permissionTypeSchema); 


const organizationSchema =  mongoose.Schema({
    account :  { type: Schema.Types.ObjectId, ref: 'account', },
    name: {  type: String, required: true },
    logo: { type: String, required: true  }
});


const Organization = mongoose.model('organization', organizationSchema);

Now i am developing Authorisation part where the user need to be restricted access to the resource by checking the permission he or she is assigned with .

The solution i found was to develop a Authorisation middleware which run after the authentication middleware which check for the access permissions assigned

But the problem appeared while i tried to access account data based on the user currently logged in , as i will have to search document based on the objectId reference . And i could understand that this could happen if i continue with my current design .This works fine but searching document using objectId reference seems not be a good idea

Authorization middleware

module.exports = {

    checkAccess :  (permission_type,action) => {

        return  async (req, res, next) => {

            // check if the user object is in the request after verifying jwt
            if(req.user){

                // find the accountUserRole with the user data from the req after passort jwt auth
                const accountUser = await AccountUserRole.findOne({ user :new ObjectId( req.user._id) }).populate('account');
                if(accountUser)
                {
                    // find  the account  and check the type 

                    if(accountUser.account)
                    {   
                        if(accountUser.account.type === 'single')
                        {   
                            // if account  is single grant access
                            return next();
                        }
                        else if(accountUser.account.type === 'organization'){


                             // find the user permission 

                             // check permission with permission type and see if action is true 

                             // if true move to next middileware else throw  access denied error  


                        }
                    }

                }

            }
        }


    }


}

I decided to scrap my current schema as i understand that forcing RDBMS approach on NoSQL is a bad idea.

Unlike relational databases, with MongoDB the best schema design depends a lot on how you're going to be accessing the data. What will you be using the Account data for, and how will you be accessing it

My new redesigned schema and models

const userSchema = new Schema({
    first_name: String,
    last_name: String,
    email: String,
    phone: String,
    avatar: String,
    password: String,
    active: Boolean
    account :  { type: Schema.Types.ObjectId, ref: 'account', },
    role: { type: String, enum: ['admin', 'user'], default: 'user' },
    permssion: [
        {
            type: {  type: Schema.Types.ObjectId, ref: 'permissionType', required: true  },
            read: { type: Boolean, default: false, required: true  },
            write: { type: Boolean, default: false, required: true },
            delete: { type: Boolean, default: false, required: true },
        }
    ]

});

const User = mongoose.model('user', userSchema);

const accountSchema =  mongoose.Schema({
    account_type: { type: String, enum: ['single', 'organization'], default: 'single' },
    organization: {  
            name: {  type: String, required: true },
            logo: { type: String, required: true  }
         },
    billing_address: String,
    shipping_address: String,

});


const Account = mongoose.model('account', accountSchema);


const permissionTypeSchema =  mongoose.Schema({
    name :  { type: String, required: true   }

});

const PermissionType = mongoose.model('permissionType', permissionTypeSchema);

Still i am not sure if this is the right way to do it , please help me with you suggestions.

Answer 1

I would suggest:

1 - Define your permission levels, for example: If the user is assigned to a specific Role / Permission level, what features/options he can access.

2 - Permission levels should be recognized by Number (1 = Admin, 2 = User) etc and that key should be indexed in MongoDB (You can use and rely on the ObjectID as well).

3 - Your user object/schema should only have a permission key with the type of Number in Mongoose - no need to create a separate schema for this.

 const userSchema = new Schema({
    first_name: String,
    last_name: String,
    email: String,
    phone: String,
    avatar: String,
    password: String,
    active: Boolean
    account :  { type: Schema.Types.ObjectId, ref: 'account', },
    permssion: {type: Number, required: true, default: 2} // Default's User

});

With this approach, you can modify your auth check middleware to just check if the permission level sent by the client is identified by the DB and if it does, give the user access else throw access denied error.

If you want you can add another field with permission type and return the name of the permission as well but I think you should handle it on the client, not on the server / be.

I partially understood the requirements (Bad at reading too many words) so I have left anything untouched, let me know.

Answer 2

you can merge user and user account schema :

added some more fileds which is useful to you .

const userSchema = new Schema({
    first_name: { type: String,default:'',required:true},
    last_name: { type: String,default:'',required:true},
    email:  { type: String,unique:true,required:true,index: true},
    email_verified :{type: Boolean,default:false},
    email_verify_token:{type: String,default:null},
    phone:  { type: String,default:''},
    phone_verified :{type: Boolean,default:false},
    phone_otp_number:{type:Number,default:null},
    phone_otp_expired_at:{ type: Date,default:null},
    avatar:  { type: String,default:''},
    password: { type: String,required:true},
    password_reset_token:{type: String,default:null},
    reset_token_expired_at: { type: Date,default:null},
    active: { type: Boolean,default:true}
    account_type: { type: String, enum: ['single', 'organization'], default: 'single' },
    organization: {type:Schema.Types.Mixed,default:{}},
    billing_address: { type: String,default:''}
    shipping_address: { type: String,default:''}
    role: { type: String, enum: ['admin', 'user'], default: 'user' },
    permission: [
        {
            type: {  type: Schema.Types.ObjectId, ref: 'permissionType', required: true  },
            read: { type: Boolean, default: false, required: true  },
            write: { type: Boolean, default: false, required: true },
            delete: { type: Boolean, default: false, required: true },
        }
    ],
   created_at: { type: Date, default: Date.now },
   updated_at: { type: Date, default: Date.now }
});

in your middleware :

module.exports = {

  checkAccess :  (permission_type,action) => {

    return  async (req, res, next) => {

        // check if the user object is in the request after verifying jwt
         if(req.user){
              if(req.user.account_type === 'single')
                    {   
                        // if account  is single grant access
                        return next();
                    }
                    else{


                         // find the user permission 

                         // check permission with permission type and see if action is true 

                         // if true move to next middileware else throw  access denied error  


                    }
         }
       }
   }
};