Re: SQL Database design question

>> I need to ensure that every employee created has and maintains at
least one
entry in the EmployeeBranches table. Otherwise I'm going to have rogue employees that won't be able to work anywhere! <<

CREATE TABLE Personnel
(ssn CHAR(9) NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL);

CREATE TABLE Branches
(branch_id INTEGER NOT NULL PRIMARY KEY, branch_name VARCHAR(35) NOT NULL);

CREATE TABLE JobAssignments
(ssn CHAR(9) NOT NULL PRIMARY KEY -- nobody is in two branches REFERENCES Personnel (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
branch_id INTEGER NOT NULL
REFERENCES Branches (branch_id)
ON UPDATE CASCADE
ON DELETE CASCADE); The key on the SSN will assure that nobody is at two branches and that a branch can have many employees assigned to it. Ideally, you would want an SQL-92 constraint to check that each employee does have a branch assignment. Here is one way:

CREATE ASSERTION Everyone_assigned
CHECK ((SELECT COUNT(ssn)
FROM JobAssignments)
= (SELECT COUNT(ssn)
FROM Personnel));

This is a surprise to people at first because they expect to see a JOIN to do the one-to-one mapping between personnel and job assignments. But the PK-FK requirement provides that for you. Any unassigned employee will make Personnel table bigger than the JobAssignments table and an employee in JobAssignments must have a match in Personnel in the first place. The good optimizers extract things like that as predicates and use them, which is why we want DRI instead of triggers and application side logic.

In T-SQL at this time, you would put this logic in a TRIGGER and have a stored procedure that inserts into both tables as a single transaction. The updates and deletes will cascade and clean up the job assignments. Received on Fri Jan 28 2005 - 18:30:01 CET