Re: DBDesign Q2:

net__space_at_hotmail.com (Andy) wrote in message news:<edb90340.0311262009.33976f1a_at_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

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

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

Another hole is that a department can be set up with no manager at all. Again, database constraints deferred until the end of the transaction would be required to enforce these rules (if the DBMS supports such constraints).

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

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

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.

Whichever solution you choose requires the addition of database (inter-table) constraints that are deferred until the end of the transaction. Combinations of primary, unique and foreign keys alone cannot enforce all the rules. With most (all?) existing DBMS products, this means that the business rules simply cannot all be enforced in the database; some must be enforced in the application, or by forcing use of stored procedures to maintain the data, or not enforced at all (except by exception reporting and manual intervention). Received on Thu Nov 27 2003 - 13:45:53 CET