Database Design For Authorization Of An Relation

Question

Maybe the title of the question is wrong but I could not find a different way to put it. I am trying to create a term project for my database class. Here is my diagram

Here you can see the teacher can open courses and Can Attend these courses. My problem is whenever Learner wants to attend these courses, I need to ask the teacher whether the teacher allowing or not. My Initial approach to this problem is at the below

But When I do this approach how can I make sure that which approve points to the course?

Is there a pattern for this type of er or relational models?

Answer 1

You need to rearrange the relationships here:

TEACHER
   |
 opens --- approves --- is attended by
   |           |             |
COURSE         -- LEARNER ----

You have a separate entity called a CourseOpening, representing a Course that was opened by a Teacher. A Learner can attend a Course, but only if opened by a Teacher, and the Teacher has approved the Learner to that Course. So the Learner is foreign-keyed only against this new table, and therefore you can also enforce that they are approved by the Teacher for that same opening.

You can probably combine the approval and attendance into the same table, although you don't have to, as you can foreign-key one against the other.

CREATE TABLE Teacher (
  Id int IDENTITY PRIMARY KEY,
  Name varchar(100) NOT NULL
  ..
);

CREATE TABLE Course (
  Id int IDENTITY PRIMARY KEY,
  Name varchar(100) NOT NULL
  ..
);

CREATE TABLE Learner (
  Id int IDENTITY PRIMARY KEY,
  Name varchar(100) NOT NULL
  ..
);

CREATE TABLE CourseOpening (
  CourseId int NOT NULL REFERENCES Course (Id),
  TeacherId int NOT NULL REFERENCES Teacher (Id),
  PRIMARY KEY (CourseId, TeacherId),
);

CREATE TABLE LearnerCourse (
  CourseId int NOT NULL,
  TeacherId int NOT NULL,
  LearnerId int NOT NULL REFERENCES Learner (Id),
  IsApproved bit NOT NULL,
  IsAttending bit NOT NULL,
  PRIMARY KEY (CourseId, TeacherId, LearnerId),
  FOREIGN KEY (CourseId, TeacherId) REFERENCES CourseOpening (CourseId, TeacherId)
);

As you can see, a Learner can only be approved or attend a Course which has been opened by a Teacher.

Answer 2

The approval is an attribute of the course. This is a many to many relationship as many students can attend the course and (presumably) the students can attend many courses so would need a link table:

CourseId,
TeacherId,
LearnerId,
TeacherApprovedDate

As well as any other columns you need to describe the course assignment. The approval date would be NULL if the course has not been approved.

Alternatively, you could use a Boolean column, IsApproved

You may want to add a surrogate key such as CourseAssignmentId or use a composite primary key of CourseId, TeacherId, LearnerId