I’m working on a new project that has a reasonably simple structure:

• 
  

  Organizations table

  
  


• 
  

  People table where employees and clients are tagged as such

  
  


• 
  

  Employees: can belong to one organization, can see all of their client records, but not all org level clients

  
  


• 
  

  Clients: have a one-to-one relationship with an employee and organization

  
  


• 
  

  Meta Data: 5 or 6 other tables with various data like courses, assessments, task list etc

  
  


• 
  

  an org admin can see everything about all of their clients

  
  


• 
  

  an employee can see everything about the clients they’ve been assigned to (and they can add courses, - assessments, tasks etc, to a client profile)

  
  


• 
  

  a client can see their own stuff only

  
  

Basically the approach I was thinking of was to create join tables for all of the meta data tables like this:

PeopleCourses:

• PersonID (links to people table)


• Course ID ( links to courses table)


• ShowToEmployee (links to people table)


• ShowInOrganizationDashboard (links to Org table)

I’ll be using Stacker for the front end so I’m a bit limited with using views and filtering with dynamic variables.