Re: DBDesign Q2:

net__space_at_hotmail.com (Andy) wrote in message news:<edb90340.0311272005.3500e39d_at_posting.google.com>...
> > > *********
> > > 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?
Yes, like I just said: "I don't find this objectionable." In reality, I would probably allow a department to exist without a manager assigned - a company might want to define its new department structure first, and assign managers later. But if the rule really were that every department MUST have a manager and every employee MUST be in a department, then I would use deferred constraints to handle that.

>
> > > 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)

OK, so this is a diferrent set up altogether: now it is employees who are managed by other employees, and departments are not managed at all. May be valid, but it isn't the same thing.

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

I don't think it's that simple actually. Suppose you have a department D1 with 2 employees E1 and E2, and that E1 is flagged as being the manager. If a department must always have exactly 1 manager, how do you change the manager from E1 to E2? You can't "unflag" E1 first, because then there would be no manager, and you can't flag E2 first, because then there would be 2 managers. This is a check that must be done at the transaction level (after both updates), whereas triggers fire at the statement level.

Maybe you could get around that using a fancy update statement like:

update employee
set manager_flag = case when manager_flag='Y' then 'N' else 'Y' end where employee_id in (E1, E2);

Another solution might be to use a trigger on employee to maintain a new column "number_of_managers" on department. We can then add a check constraint on this column: check(number_of_managers = 1). As long as we can DEFER this check until the end of the transaction, we are OK.

> 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?

Generally, I'd go for A because if every department is managed by 1 employee, that sounds like a foreign key from department to employee to me. Received on Fri Nov 28 2003 - 12:17:05 CET