Re: SQL Database design question

[posted and mailed, please reply in news]

Dan Williams (dtwilliams_at_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))

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, esquel_at_sommarskog.se

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