CODEWITHSUNDEEP
database-designrelational
Wadda7
Wadda7

Reputation: 69

Employee Department Relationship in Database

I'm designing a relational database for SCM (as part of designing information system for the enterprise). But when it came to the relationship between the employee and its department, I had some troubles .

I designed the following entities :

and since the relation is one-to-many (every employee should work for - and only one - dept., while every dept has many employees), I added Department ID to the attributes of the Employee. But the problem is how to represent a MANAGE relationship (one-to-one).

Is it valid to design a new relation that we call : Employee_manage_Department and its attributes are (Employee ID , Department ID) where both columns are part of the primary key ??

thanks in advance

Upvotes: 4

Views: 17347

Answers (5)

Walter Mitty
Walter Mitty

Reputation: 18940

Is the manager of a departement always one of the employees who work in that department? If the answer is yes, then a boolean MnagerFlag in the Employee table should be sufficient.

You will need to declare a constraint or enforce a rule that prevents more than one employee in a depratment from having this flag set.

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Since manager-department relationship is 1:1, you can simply add a Manager ID to the department table, which would act as a foreign key referencing the employee table:

enter image description here

This introduces a circular dependency, preventing the insertion of new data, which can be resolved in one of the following ways:

  • Defer one of the circular FKs (if the DBMS supports it).
  • Make Manager ID NULL-able. You may need to do it anyway in case you need to support the concept of manager-less department.

BTW, this allows for a department to be managed by an employee from a different department. If that is undesirable, you'll need to use an identifying relationship so the same department ID can propagate back and forth:

enter image description here

NOTE: The separate Employee_manage_Department table would be appropriate for modeling M:N relationship.

Upvotes: 1

omoabobade
omoabobade

Reputation: 515

No i don't see this as necessary if an employee will and can only belong to a single department but if an employee can have more than one department then you can go ahead ... on a second thought if you would like to keep the from and the exit date of the staff i think you can

Upvotes: 0

Luis Quijada
Luis Quijada

Reputation: 2405

Yes, but as the roles of an Employee in the company has limited (life)time, I would add two new DateTime collumns, DATE_FROM and DATE_TO, making the DATE_FROM part of the composed primary key.

Upvotes: 2

Pratik Mandrekar
Pratik Mandrekar

Reputation: 9568

The Employee.department_id should be a foreign_key to the Department table and be unique and non-null. That satisfies your constraints of One Employee has one department and One department can have many employees

Upvotes: 0

Related Questions