I am using Microsoft SQL Server 2000 and have a question about our database
design.

Here is a sublist of tables and columns we currently have:-

Employee
----------
Ee_Code PRIMARY KEY
Ee_Name NOT NULL

Branch
------------
Branch_ID PRIMARY KEY
Branch_Name NOT NULL


Is it better to create a new EmployeeBranch table that contains a list of
employees and the relevant branches they can work at (as they should be able
to work at more than one branch), or is it better to create several columns
in the Employee table that correspond to the branches they can work at.

For example,

EmployeeBranch
---------------
Ee_Code
Branch_ID

or

Employee
----------
Ee_Code
Ee_Name
Ee_Branch1
Ee_Branch2
Ee_Branch3, etc...

To me it obviously appears better to use my first suggestion. But, how do i
go about ensuring that each employee has at least one entry in the
EmployeeBranch table, and that each employee can only have one occurrence of
each individual branch (ie. there's no duplication of EmployeeBranch data)?

Is it possible to setup constraints and relationships on our tables to allow
for this and how do i go about doing it?

Thanks in advance for any suggestions

Dan

You prevent duplication in the EmployeeBranches table as in any other
table: with a PK or UNIQUE constraint. However, your constraints have
to allow an employee to be added without a corresponding entry in
EmployeeBranches, otherwise you could never add new rows to the
Employees table.

CREATE TABLE Employees (ee_code CHAR(10) PRIMARY KEY, ee_name
VARCHAR(50) NOT NULL)

CREATE TABLE Branches (branch_id INTEGER PRIMARY KEY, branch_name
VARCHAR(50) NOT NULL UNIQUE)

CREATE TABLE EmployeeBranches (ee_code CHAR(10) NOT NULL REFERENCES
Employees (ee_code), branch_id INTEGER NOT NULL REFERENCES Branches
(branch_id), PRIMARY KEY (ee_code, branch_id))
--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1106907752.312855.40980@c13g2000cwb.googlegro ups.com...
> You prevent duplication in the EmployeeBranches table as in any other
> table: with a PK or UNIQUE constraint. However, your constraints have
> to allow an employee to be added without a corresponding entry in
> EmployeeBranches, otherwise you could never add new rows to the
> Employees table.
>

This is exactly my problem.
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!!

This was why I was thinking of adding an Ee_Branch column in my employee
table that is not NULLable and have it correspond to an entry in the Branch
table. This could be used as their primary branch location. I suppose I
could then use my EmployeeBranches table to assign them to other branches,
ensuring that it's different to it's primary branch. But then this isn't
exactly efficient.

Alternatively, I could get my web front end to ensure that on creating a new
employee, it automatically adds an entry to the EmployeeBranches table too.
But how do I prevent deletion of this initially created record, ensuring
that each employee has at least one branch assigned to it??

This is becoming very confusing!

Any other suggestions?

Dan

Dan Williams wrote:
> I need to ensure that every employee created has and maintains at
least one
> entry in the EmployeeBranches table.

I don't know much about SQL Server. I presume it does not permit
complex check constraints like this?:

ALTER TABLE Employees ADD CONSTRAINT eb_chk
CHECK (EXISTS (SELECT NULL FROM Employee_Branches eb WHERE eb.EE_Code =
e.EE_Code));

It would need to be a DEFERRED constraint. A deferred constraint is
not checked until you commit, so it allows you to enter a new Employee
row without hitting a constraint violation right away because you
haven't inserted any EmployeeBranches rows yet!

If that doesn't work, another approach is to use triggers to maintain a
Branch_Count column in the Employees table. I don't know SQL Server
syntax, but the pseudocode would be something like:

on insert of Employees:
set Branch_Count = (select count(*) from Employee_Branches where
EE_Code = ...)

after insert of EmployeeBranches:
update Employees set Branch_Count = Branch_Count+1 where EE_Code =
....

after delete of EmployeeBranches:
update Employees set Branch_Count = Branch_Count-1 where EE_Code =
....

Then you would need a DEFERRED check constraint on Employees:
check(Branch_Count > 0).

You can add a cascading delete and a trigger:

CREATE TABLE EmployeeBranches (ee_code CHAR(10) NOT NULL REFERENCES
Employees (ee_code) ON DELETE CASCADE, branch_id INTEGER NOT NULL
REFERENCES Branches (branch_id), PRIMARY KEY (ee_code, branch_id))

CREATE TRIGGER trg_prevent_orphaned_employees
ON EmployeeBranches FOR UPDATE, DELETE
AS
IF EXISTS
(SELECT *
FROM Employees AS E
LEFT JOIN EmployeeBranches AS B
ON E.ee_code = B.ee_code
WHERE B.ee_code IS NULL
AND E.ee_code IN
(SELECT ee_code
FROM Inserted
UNION ALL
SELECT ee_code
FROM Deleted))
BEGIN
ROLLBACK TRAN
RAISERROR('Orphaned employees not permitted',16,1)
END

The ON DELETE CASCADE option is required otherwise it wouldn't be
possible to delete an employee.

Alternatively, I assume you don't allow your web app to modify these
tables directly so you may prefer to do a similar check in the SPs that
perform the updates and deletes.

--
David Portas
SQL Server MVP
--

SQL Server doesn't support deferred constraints or subqueries in check
constraints.

--
David Portas
SQL Server MVP
--

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

>From Dan's original post "they [the employees] should be able to work
at more than one branch". The JobAssignments table would therefore
represents a many-to-many relationship. That was my interpretation
anyway.

--
David Portas
SQL Server MVP
--

> Is it better to create a new EmployeeBranch ... or create several
columns in the Employee table

Add EmployeeBranch table. If db doesn't implement desired constraint,
add in code. In script below for a small/simple experimental db, each
employee can work at 0 to many branches.

// Create items in directory to classify things.
(CREATE *employee.item ~in = dir)
(CREATE *intern.item ~in = dir)
(CREATE *temporary.item ~in = dir)
(CREATE *branch.item ~in = dir)
(CREATE *code.item ~in = dir)

// Create the verb 'works at'.
(CREATE *worksAt.cls = verb)

// Create intern John who works at research branch.
(CREATE *john.cls = employee)
(CREATE john.cls= intern)
(CREATE john.code = +JOHN_I_123)
(CREATE john.worksAt = +research)

// Create temp Mary who works at research and mfg plant 10 with a code.
(CREATE *mary.cls = employee)
(CREATE mary.cls = temporary)
(CREATE mary.code = +MARY_T_303)
(CREATE mary.worksAt = +research)
(CREATE mary.worksAt = (CREATE *mfg plant 10.cls=branch & it.code=
+MFG_PLT_10))

// Find temporary employee(s)
// who work at a branch whose code is MFG_PLT_10.
// Finds Mary.
(SELECT %.cls=employee & %.cls=temporary & %.worksAt=(%.cls=branch &
%.code=MFG_PLT_10))

If you control the stored procedure layer into your database, this
won't be an issue -- just make sure that the SP that inserts/updates
the Employee records, also inserts and updates your Employee-Branch
intersection table. This is a classic many-to-many relationship, and
there's no reason in the world to model it in any way except with the
intersection (or junction, if you like) table.

This does require you to have some control over the processes that are
touching & updating your table structures. If you're going through an
SP layer, this problem simply doesn't exist. If you're not, you should
be.