Security Group Management

I am working on a new application which will act as our recruiting/employee management system but will not be an HRS platform. This application will handle all the activities that occur before they become an employee.

In our IT environment we use security groups to help provision employees into groups that grant them access to data, programs, digital locations, etc. These security groups are rule based and are used to examine an employee's Company, Department, Job Title, and Office Location to determine their group membership.

I'm trying to build an interface in QB which would display to the user the various security groups the employee will be placed in based on the selections made in the fields referenced above. An employee will be in at least 1 group but could be in multiple groups and we currently have about 175 different groups configured.

I considered creating a single formula field in the Employees table, called Security Group, which would hold all the various security group rule formulas, but that felt a bit too much to manage.

I also considered creating individual formula fields, 1 for each security group, but that too feels too much to manage.

I have also considered creating a Security Groups table which has 1 row for each group. The table would include: Group Name, Group Type, Group Description, and Group Rule. The group rule field would hold the actual QB formula that could be used to evaluate if an employee fits into that group. But I'm at a loss as to how to connect the Security Group table with the Employees table so that Employees can benefit from the group rule formulas it holds. Again, an employee can be in 1 or more security group.

Looking for any suggestions on the best way to set this up so that the management of the security groups is nominal (we may need to create more in the future), so that we can see which security groups an employee would be a member of, and so we could see all the members of each of the security groups.