DBDesign Q2:

From: Andy <net__space_at_hotmail.com>
Date: 26 Nov 2003 20:09:09 -0800
Message-ID: <edb90340.0311262009.33976f1a@posting.google.com>

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. Received on Wed Nov 26 2003 - 22:09:09 CST