我有一个关于实现四个表之间简单关系的问题。
employee
department
course
course_employee
我已经创建了它们,但我希望确认一下是否有可能会影响数据库设计的内容被忽略。以下是关系:
employee to department -> many to one
employee to course -> many to many
course_employee表是course和employee表之间的中介者。
以下是我的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);
以下是从Sql Developer导出的实体关系图:
我想为员工添加已完成的课程,但我不确定是否可以通过某种关系来实现。能否了解数据库设计的人分享一下意见?非常感谢!