DBDesign Q2:

Hi All!

I like employee and department scenario from DBDesgin Q.
It looks more intuitive than my previous sample.

***********************************************************************
Business rule:

Each employee works only in one department. 
Department is managed by only one of employee who works in this
department.
One employee cannot work for two different departments and one
employee cannot manage two different departments

**************************************************************************

*********
SolutionA(not good. Two entity referent each other)

Department (DepartmentID PK, Name, eEmployeeID FK)
Employee (EmployeeID PK , Name, …, dDepartmetnID FK)
To add records in solutionA to Department and Employee tables:
Add a record to Department with eEmployeeID= NULL
Add Employee records
Set eEmployeeID to value in Department table

*********
Solution B: (I think it is cleaner than A, but there is a whole)
Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, …, dDepartmetnID FK)
(the next is subtyping of employee)
DepartmentMngr(EmployeeID PK/FK, dDepartmentID FK/U1)

It will satisfy bus. rule, but there is a whole.
Employee who is a manager can reference one department in Employee
table and the other department in DepartmentMngr.

*********
Solution C.(Problem: How buss.  rule can be forced that only one
employee from department is a manager?)
Department (DepartmentID PK, Name)
Employee (EmployeeID PK , Name, …, dDepartmetnID FK, eEmployeeID FK)

Q. How buss.  Rule can be forced that only one employee from
department is a manager?

Are there any suggestions about implementing the above business rule?
Which of implementations are you in favor?

Thank you in advance,
Andy.