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.
