How can instructors add users to their classes and at the same time not know all of the institution users?

Question

I have the following entities

• Institutions - with institutionID and userID
• Institution Users - with institutionUserID
• Institution Classes - with institutionUserID, institutionID
• Institution Class Users - with fields classID, institutionUserID and isInstructor

I'd like the user responsible (instructor) for one Institution Class to manage its Institution Class Users. By managing I mean to be able to even add a new Institution Class Users. At the same time, I wouldn't want this teacher to know all of the Institution Users.

What comes to my mind is the usage of two other entities: Institution Departments and Institution Department Users. This would enable to shrink the list of available users for that considering that classes would then be held by the department - Institution Classes and Institution Class Users would then change to Institution Department Class and Institution Department Class Users.

How to proceed in this scenario?

Answer 1

Yes you could add three additional tables called Departments, DepartmentUsers and DepartmentClasses.

Departments would have a primary key field departmentId (and probably some other fields like departmentName, etc).

DepartmentUsers would be a linking table between Departments and InstitutionUsers so it would store the departmentId and institutionUserId (as foreign keys). (This table determines which users are in which department.)

DepartmentClasses would be the same as above, except it would link Departments to InstitutionClasses, so it would store the departmentId and institutionClassId. (This table determines which classes are in which department.)

Then for a particular class you can see only the applicable users (within the department) who can be added to it via InstitutionClasses join to DepartmentClasses join to Department join to DepartmentUsers.

(Then the institutionUserID field in your InstitutionClasses table only represents who's already been added to the class. You may want to rename the column to something more explicit like registeredInstitutionUserID.)