Re: ERwin Question (employee department relationship)

In article <DASSDG.2.000F1695_at_uwoadmin.uwo.ca> DASSDG_at_uwoadmin.uwo.ca writes:
>From: DASSDG_at_uwoadmin.uwo.ca
>Subject: ERwin Question
>Date: Thu, 1 Dec 1994 15:05:11

>I have just recently started using ERwin and I would appreciate
>any comments on the following situation:
 

>Situation:
 

> I have two entities (Employee and Department) and I want to
> define the following two relationships:
 

> 1. Every employee has to belong to a department.
 

> Employee table contains a foreign key to the
> department table.
 

> 2. Each Department may have zero or 1 manager.
 

> Department table contains a foreign key to the
> employee table (nulls are allowed).
 

>Problem:
 

> ERwin will accept one relationship, but will not accept the
> other one.
 

> The message given when trying to add the second relationship
> is "relationship causes illegal cycle".
 

> Am I doing something wrong ?
> Do I have something setup incorrectly ?
> Is this a situation that ERwin will not allow you to enter ?
>

Logically you would be creating a situation where the database design supports a possible invalid relationship. You could enter an employee and assign him to department x but then make him the manager of department y. I am assuming you are implementing a flat organizational struction since you didn't mention a foreign relationship from department back to department.

There are two logical solutions, the first is that if the employee table identifies positions then you could derive the data since the employee in department x that was in the 'department manager' position would be the department manager of department x. The other solution would be to have a department_manager indicator (flag) in the employee entity.

Most of the time none of it is that simple because the real situation is that employees are assigned to positions for periods of time and the positions exist in departments. A truer representation would have; department, employee, position, and position_assignment entities. Add appropriate relationships, etc. If it were an HR system it would likely have an employee_salary dependent entity also.

Can't help you on why ERWin won't let you do what you want.

Lawrence..... Received on Wed Dec 07 1994 - 11:25:37 CET