SQL Database design question

[Dan Williams]

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

[David Portas]

Re: SQL Database design question

You prevent duplication in the EmployeeBranche s 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
EmployeeBranche s, 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 EmployeeBranche s (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
--

[Dan Williams]

Re: SQL Database design question

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:1106907752 .312855.40980@c 13g2000cwb.goog legroups.com...[color=blue]
> You prevent duplication in the EmployeeBranche s 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
> EmployeeBranche s, otherwise you could never add new rows to the
> Employees table.
>[/color]

This is exactly my problem.
I need to ensure that every employee created has and maintains at least one
entry in the EmployeeBranche s 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 EmployeeBranche s 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 EmployeeBranche s 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

[Tony Andrews]

Re: SQL Database design question

Dan Williams wrote:[color=blue]
> I need to ensure that every employee created has and maintains at[/color]
least one[color=blue]
> entry in the EmployeeBranche s table.[/color]

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_Branch es 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 EmployeeBranche s 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_Branch es where
EE_Code = ...)

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

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

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

[David Portas]

Re: SQL Database design question

You can add a cascading delete and a trigger:

CREATE TABLE EmployeeBranche s (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_orp haned_employees
ON EmployeeBranche s FOR UPDATE, DELETE
AS
IF EXISTS
(SELECT *
FROM Employees AS E
LEFT JOIN EmployeeBranche s 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('Orph aned 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
--

[David Portas]

Re: SQL Database design question

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

--
David Portas
SQL Server MVP
--

[-CELKO-]

Re: SQL Database design question

>> I need to ensure that every employee created has and maintains at
least one
entry in the EmployeeBranche s 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_assign ed
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.

[David Portas]

Re: SQL Database design question

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

[Neo]

Re: SQL Database design question

> 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=(%.cl s=branch &
%.code=MFG_PLT_ 10))

[FatSam]

Re: SQL Database design question

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.

[Neo]

Re: SQL Database design question

> .. This is a classic many-to-many relationship...

Yes, it is similar to the "Employee Works_On Project" example on p54 of
"Fund of Db Sys", Elmasri and Navathe, 2nd Ed. I was wondering, if the
table is named T_EmployeeBranc h (as shown below), how does the db know
the relationship between employee and branch? If the table is named
T_WorksAt, how does the db know if "John worksAt research" vs "research
worksAt John", since column order should be irrelevant?

T_EmployeeBranc h or T_WorksAt
-----------------------------
ID EmpID BranchID
-- ----- ------
01 john research
02 mary research
03 mary mfgPlt10

// In the experimental db:
// Find relationships between John and Research.
// Find the verb worksAt.
(SELECT john.% = research)

// Find the relationship between Research and John.
// Finds nothing.
(SELECT research.% = john)

[Erland Sommarskog]

Re: SQL Database design question

[posted and mailed, please reply in news]

Dan Williams (dtwilliams@hot mail.com) writes:[color=blue]
> 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)?[/color]

Since SQL Server does not have deferred constraints or triggers, this
is not terribly easy to implement.

Here is a fairly kludgy way of doing it:

CREATE TABLE employees(empid int NOT NULL,
firstbranch smallint NOT NULL,
.....
CONSTRAINT pk_emp PRIMARY (empid))
go
CREATE TABLE jobassignments (empid int NOT NULL,
branchid smallint NOT NULL,
CONSTRAINT pk_jobasg (empid, branchid))
go

Then you have a trigger on employees that inserts a row into
jobassignments, using the value in firstbranch. From that point and on,
firstbranch would be a dead value.

Another trigger on jobassignments would disallow removing the last branch
for an employee. But since that trigger would prevent you to delete an
employee at all, you would need a way to override the check. One way is
to have an INSTEAD OF DELETE trigger on employees that creates a temp
table #delete$pending and then performs the deletes in jobassignments
and employees. The check in jobassignments actually looks like this:

IF object_id('temp db..#delete$pen ding') IS NULL
BEGIN
-- Check that not all branches for an employee is deleted.

I'm not sure that I would recommend anyone actually do this.

The more normal way to do this in SQL Server is to expose procedures
that performs the tasks and necessary integrity checking. Then you
disallow direct access to the table, and hope that people who have admin
access from Query Analyzer know what they are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[-CELKO-]

Re: SQL Database design question

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

My interpretation was that an employee is always at one branch, which
makes physical sense, but we can move him by updating his assignment.
If we want to have him in multiple branches, we could change the keys
on JobAssignments and use

CREATE TABLE JobAssignments
(ssn CHAR(9) NOT NULL
REFERENCES Personnel (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
branch_id INTEGER NOT NULL
REFERENCES Branches (branch_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ssn, branch_id));

Then use a DISTINCT in the assertion.

CREATE ASSERTION Everyone_assign ed_at_least_onc e
CHECK ((SELECT COUNT(DISTINCT ssn) FROM JobAssignments)
= (SELECT COUNT(ssn) FROM Personnel));

[saghir.taj@gmail.com]

Re: SQL Database design question

Dear Dan,

You need three tables simply

Branch
--------------------------
BranchCode
BranchName

Employee
----------------
Ee_Code
Ee_Name
FK_Branch // Foreign Key for branch, not nullable.

Branch _Employee // Junction Table
------------------------------------------
FK_Employee // Foreign Key for Employee
FK_Branch // Foreign Key for Branch


:) :) :)
Saghir Taj
MDBA
www.dbnest.com :The Nest of DB Professionals.

[William Cleveland]

Re: SQL Database design question

Erland Sommarskog wrote:
[color=blue]
> The more normal way to do this in SQL Server is to expose procedures
> that performs the tasks and necessary integrity checking. Then you
> disallow direct access to the table, and hope that people who have admin
> access from Query Analyzer know what they are doing.
>[/color]

I've gone that route, and it should work. The problem is when you're
not the DBA, and a later developer slips something past the DBA that
doesn't follow your rules.

I'm afraid I don't have a good solution, except for the suggestion
I've made at work that both the DBA *and* the original database
designer sign off on changes in a database. So far, no luck.

Bill