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.