Re: DBDesign Q2:

[EMAIL PROTECTED] (Andy) wrote in message news:<[EMAIL PROTECTED]>...
> 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).