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 :
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
Answer
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.