SQL Question

(If it matters this is postgreSQL and I am not very experienced with SQL)

Say I have 3 tables:
User, Department and UserDept (a user can be a member of more than one department). Partial tables shown below

The department manager should be a member of the department and I want that enforced by the DB server but the complication here is the fact that a user can belong to more than one department .

One way to so that is to allow any user to be made department manager, and then if they are not already a member of the department the should server auto create an entry in the UserDept table… But I am not sure how to do that. If there is a straight forward to do that way please let me know.

The more obvious way would be to require that the user first be required to be made a member of the department, then one could just have the user picked from the department members as defined in the UserDept table…See he partial table definitions below:

But then what happens if the User is deleted to the ManagerKey field? I would just want ManagerKey set to NULL (which is straight forward for a single field foreign key reference)… I certainly Don’t want the Department Table Primary key set to NULL , the record deleted or have an invalid manager key stick around!

From reading docs and looking at on-line examples I am not sure how how to do this… Does anybody know how to handle this?

Thanks
-Karen

CREATE TABLE IF NOT EXISTS UserTable( 
	Key0             	SERIAL PRIMARY KEY,
	FirstName			TEXT NOT NULL,
	LastName			TEXT NOT NULL);

CREATE Department( 
	Key0            SERIAL PRIMARY KEY,
	Name			TEXT NOT NULL,
	ManagerKey		INTEGER ,
    FOREIGN KEY (ManagerKey, Key0) REFERENCES UserDept( UserKey, DeptKey) ON DELETE SET NULL);

CREATE TABLE IF NOT EXISTS UserDept( 
	UserKey			INTEGER   NOT NULL REFERENCES UserTable(Key0) ON DELETE CASCADE,
	DeptKey			INTEGER  NOT NULL REFERENCES Department(Key0) ON DELETE CASCADE,
	PRIMARY KEY (UserKey, DeptKey) );

The dept manager is not a “userdept” - its a user so ManagerKey should just reference the User table

I wouldnt use the UserDept table for this at all

Then the only complication is that IF the ManagerKey entry is not in the UserDept table you add one (probably via a trigger as I’m not sure you can do it without something like that in a purely declarative way)

you’ll probably need triggers to do certain actions (like remove a person from UserDept and from any ManagerKeys when you delete a user)
they’d be “before delete” typ triggers since declarative referential integrity constraints often wont let you delete rows that are referred to by other tables

Thanks Norm,

I could have named that table “DeptMembers” as it is basically a list of all members of each department… I just named it by the linking keys.

So are you saying a Department manager should not be(or does not have to be) a department member? Everyplace I’ve worked the immediate department manager has always been considered a member of the department… is that not the general case?

BTW the reason I allow users to be members of multiple departments goes beyond dotted line reporting… I work in a small company and we need to wear multiple hats, and work in multiple functional areas that are headed by different people.

That would be best ideally and initially I did not do it that way.

But then i thought that the department manager should be a member of the department…

As you say, the only way to ensure that the teh manager would be a department member would be triggers on both UserTable, and the UserDept table.

That seemed too complex and error prone (in writing the triggers or missing a usage case) … basically fragile and I was not sure how to get it right! :wink:

Turns out I was pretty close to what I needed to do to make it work if one requires that a user first be added a a department member, before they can be assigned as the manager.

So where is what seems to work:

CREATE TABLE IF NOT EXISTS UserTable( 
	Key0             	SERIAL PRIMARY KEY,
	FirstName			TEXT NOT NULL,
	LastName			TEXT NOT NULL);

CREATE Department( 
	Key0            SERIAL PRIMARY KEY,
	Name			TEXT NOT NULL,
	ManagerKey		INTEGER   REFERENCES UserTable(Key0) On DELETE SET NULL,
    FOREIGN KEY (ManagerKey, Key0) REFERENCES UserDept( UserKey, DeptKey) ON DELETE SET NULL);

CREATE TABLE IF NOT EXISTS UserDept( 
	UserKey			INTEGER   NOT NULL REFERENCES UserTable(Key0) ON DELETE CASCADE,
	DeptKey			INTEGER  NOT NULL REFERENCES Department(Key0) ON DELETE CASCADE,
	PRIMARY KEY (UserKey, DeptKey) );

Though I actually had to add the FOREIGN KEY (ManagerKey, Key0) REFERENCES UserDept( UserKey, DeptKey) after creating the tables, because they all had to exist first.

Don’t know if I needed the ON DELETE set NULL clause on that reference … But it did not hurt.

[ Edit …Yes it did… I forgot to test deleting the user from the department… looks like i will need a Delete Trigger on UserDept too to Null manager. ]

To test I created a user, and department. I could not assign the manager until I assigned the user to the UserDepartment table.

Then I deleted the user, and the department table was not deleted but the managerKey was nulled as desired.

-Karen