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.The following seems to match your business rules. Treat Department Manager
as an attribute of the Department rather than the Employee.
CREATE SCHEMA AUTHORIZATION dbo
CREATE TABLE Employees (empno INTEGER PRIMARY KEY, deptno INTEGER NOT NULL
REFERENCES Departments (deptno), UNIQUE (deptno, empno))
CREATE TABLE Departments (deptno INTEGER PRIMARY KEY, deptname VARCHAR(20)
NOT NULL UNIQUE, deptmanager_empno INTEGER NOT NULL, FOREIGN KEY (deptno,
deptmanager_empno) REFERENCES Employees (deptno, empno))
The usual caveat about design questions applies: it's very difficult to give
design advice online without the opportunity to research a particular
situation in detail.
--
David Portas
----
Please reply only to the newsgroup
--|||net__space@.hotmail.com (Andy) wrote in message news:<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
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).|||well,
i just can't image how these two can be used! can you continue your sample
and try to populate the tables with some sample data.
</wqw
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Bp6dna5zxsvBf1iiRVn-hQ@.giganews.com...
> The following seems to match your business rules. Treat Department Manager
> as an attribute of the Department rather than the Employee.
> CREATE SCHEMA AUTHORIZATION dbo
> CREATE TABLE Employees (empno INTEGER PRIMARY KEY, deptno INTEGER NOT NULL
> REFERENCES Departments (deptno), UNIQUE (deptno, empno))
> CREATE TABLE Departments (deptno INTEGER PRIMARY KEY, deptname VARCHAR(20)
> NOT NULL UNIQUE, deptmanager_empno INTEGER NOT NULL, FOREIGN KEY (deptno,
> deptmanager_empno) REFERENCES Employees (deptno, empno))
> The usual caveat about design questions applies: it's very difficult to
give
> design advice online without the opportunity to research a particular
> situation in detail.
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --|||> 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.
it's not clear what's the FK on DepartmentMngr refers to. try composite one
like this:
FOREIGN KEY(EmployeeID, dDepartmentID )
REFERENCES Employee (EmployeeID, dDepartmentID)
> 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?
if you go with the "manager_flag" to enforce "only one employee from a
department is its manager" you can use an indexed view. something like this:
SELECT dDepartmetnID
FROM Employee
WHERE manager_flag=1
and then create the PK on dDepartmetnID -- this will ensure no two employees
are manager of the same department.
cheers,
</wqw|||Ah! you spotted my deliberate mistake ;-).
Make Deptmanager_empno NULLable. Obviously that's a compromise to Andy's
requirements. Possibly you could make it non-nullable once you've populated
both tables though that could make for some interesting logical conundrums
if you need to carry out a management reshuffle between departments!
Possibly it might be acceptable to leave Deptmanager_empno nullable but add
a UNIQUE constraint so that no more than one department can be without a
manager at any one time.
--
David Portas
----
Please reply only to the newsgroup
--|||> > *********
> > 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?|||"Vlad Vissoultchev" <wqweto@.nospam.myrealbox.com> wrote in message news:<uCXMLGStDHA.2060@.TK2MSFTNGP10.phx.gbl>...
> > 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.
> it's not clear what's the FK on DepartmentMngr refers to.
DepartmentMngr.dDepartmentID refers Department.DepartmentID|||net__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311272005.3500e39d@.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.
