How to implement many to many relationship in oracle database correct?

Question

I have a question about implementing simple relationship between four tables.

employee
department
course
course_employee

Created them all but I just want to be sure I am not missing something that can harm the db design. Here are the relations:

employee to department -> many to one
employee to course -> many to many

the course_employee table is mediator between course and employee tables.

Here is my SQL:

CREATE TABLE employee (
  id NUMBER PRIMARY KEY,
  username VARCHAR2(20),
  password VARCHAR2(100),
  email VARCHAR2(40),
  department_id NUMBER
);

CREATE TABLE department(
  id NUMBER PRIMARY KEY,
  department_name VARCHAR2(40),
  location VARCHAR2(30)
);


ALTER TABLE employee
ADD CONSTRAINT department_id
  FOREIGN KEY (department_id)
  REFERENCES department(id);

CREATE TABLE course(
  id NUMBER PRIMARY KEY,
  course_code  VARCHAR2(20),
  course_name VARCHAR2(100),
  difficulty VARCHAR(10),
  duration NUMBER
);

CREATE TABLE course_employee (
  course_id NUMBER,
  employee_id NUMBER
);

ALTER TABLE course_employee
ADD CONSTRAINT fk_course FOREIGN KEY(course_id)
REFERENCES course(id);


ALTER TABLE course_employee
ADD CONSTRAINT fk_employee FOREIGN KEY(employee_id)
REFERENCES employee(id);

And the exported ER diagram from sql developer:

I would like to add also completed courses for employee but I am not sure if this is achieved by some relationship. Can somebody who is aware of DB design share opinion? Thank you very much!

Answer 1

You can track the status of a training an employee enrolled for in the course_employee table. This table can hold any data specific to the fact that an employee enrolled into a course.

Answer 2

Your course_employee table is the junction table (many side) between the course table and the employee table. The course_id and employee_id must be both the foreign key and the primary key for the course_employee table in order for it to be a many-to-many relationship.