Re: DBDesign Q2:

> > *********
> > 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
>
> I don't find this objectionable. It may pose a technical challenge,
> but that can be overcome. For example, Oracle allows constraint
> checking to be deferred until the end of the transaction, so you can
> do this:
>
> insert into department( departmentid, eemployeeid ) values
> ('D1','E1');
> insert into employee (employeeid, name, ddepartmentid) values
> ('E1','Smith','D1');
> commit;
>
> To enforce the rule that the manager of the department must also be an
> employee in the department would require an additional database
> constraint (or "assertion"), which would also have to be checked at
> the end of the transaction. Not all DBMS products support this though
> (Oracle doesn't).

Are you ok with the style that employee references department table and
department references employee table?

> > 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?
>
> I don't understand this one. What is employee.eemployeeid?

Employee.eEmployeeID(FK) references Employee.EmployeeID(PK)  

> Out of those, I'd stick with solution A. Another possibility you
> didn't mention is this:
>
> Department (DepartmentID PK, Name)
> Employee (EmployeeID PK , Name, ?, dDepartmetnID FK, manager_flag)
>
> The "manager_flag" column is a boolean or yes/no type value that
> specifies whether the employee is manager of the department he/she
> belongs to. That enforces the rule that an employee can only manage
> his own department, but does not enforce the rule that a department
> must have one and only one manager - again, a deferred database
> constraint is required.
>
I am personally in favour of the solution with the flag. The problem is more than one employee can be a managers or no managers at all if all flag are false, but this can be fixed with a trigger.

Solution A. The problem is that the manager (Department.eEmployeeID) can point to employee ((Employee.EmployeeID) from different department

Between the solution A and the proposed solution with flag which one are you in favour of? Received on Fri Nov 28 2003 - 05:05:52 CET