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

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

Dan Williams

"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@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

Tony Andrews

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

David Portas

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

David Portas

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

--
David Portas
SQL Server MVP
--

-CELKO-

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

David Portas

>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

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

FatSam

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

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

[posted and mailed, please reply in news]

Dan Williams (dt********@hotmail.com) writes:

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

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('tempdb..#delete$pending') 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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

-CELKO-

>> From Dan's original post "they [the employees] should be ab*le to
work at more than one branch". The JobAssignments table would
the*refore represents a many-to-many relationship. That was my
interpre*tation anyway. <<

My interpre*tation 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_assigned_at_least_once
CHECK ((SELECT COUNT(DISTINCT ssn) FROM JobAssignments)
= (SELECT COUNT(ssn) FROM Personnel));

saghir.taj

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

Erland Sommarskog wrote:

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.

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

David Cressey

"William Cleveland" <WC********@Ameritech.Net> wrote in message
news:a3****************@newssvr17.news.prodigy.com ...

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.

The problem with this is the case when a database is part of a software
package that is sold to multiple clients.

The DBA typically works for a client, while the original database designer
works for the vendor, or the people the vendor bought the rights from. I
think this is not a trivial distinction.

Erland Sommarskog

David Cressey (da***********@earthlink.net) writes:

"William Cleveland" <WC********@Ameritech.Net> wrote in message
news:a3****************@newssvr17.news.prodigy.com ...
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.

The problem with this is the case when a database is part of a software
package that is sold to multiple clients.

The DBA typically works for a client, while the original database
designer works for the vendor, or the people the vendor bought the
rights from. I think this is not a trivial distinction.

Indeed. Being on the vendor side of the fence, I say that all warranties
are waived if you meddle with the database by other means through our
application.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

David Cressey

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Indeed. Being on the vendor side of the fence, I say that all warranties
are waived if you meddle with the database by other means through our
application.

I've occasionally been hired by clients to turn their data into information.
From that side of the fence, I'd have to say that turnkey systems are
generally garbage. Encapsulation, like paint, covers a multitude of sins.

I've even walked away from gigs where the client wanted to use data in ways
the vendor should have thought of, but didn't.

SQL Database design question

This discussion thread is closed

Similar topics