4

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 :

  • Employee ( ID , Fname , Mname , Lname , Sex , Phone , Address , Hiring date , .. etc )
  • Department ( ID , name )

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wadda7
  • 69
  • 1
  • 2
  • 4

5 Answers5

2

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.

Luis Quijada
  • 2,345
  • 1
  • 26
  • 31
1

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.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

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

Pratik Mandrekar
  • 9,362
  • 4
  • 45
  • 65
0

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

omoabobade
  • 515
  • 5
  • 16
  • Yes.....omoabobade is right. Though the relation with a composite primary key (emplyee ID, Department ID) is valid, but it is not required here at all. – sudip Aug 18 '12 at 20:33
0

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58