Quote:
Now if admin decides to add department 4 I need the code to skip 1 2 3 (as this is already in the database) and insert the 4th department into the staff allocation table. Vice versa....if department 3 is removed for example I need to skip 1 and 2 and then deactivate 3.Correct me if I misunderstanding you, but are you AUTOMATICALLY adding deptids into the intersection table anytime you add a new department??? Not a smart idea.
The only time that the intersection table should be updated is:
1) When an employee is added to a department, or;
2) When the employee leaves a department;
You do not need to have all departments associated with userids in the intersection table.
Got it?
Also, for UPDATING the tbl_User_Department intersection table when a department is deleted (or userid is deleted, although again, not recommended), all you have to do is:
1) If you are using this with Links SQL, then you can specify the DepartmentID/UserID as foreign keys and have them automatically deleted when you delete the primary key, can't you??
2) If you aren't using this with Links SQL, then you can add an additional delete SQL statement...
For example:
DELETE FROM tbl_User_Departments WHERE (DepartmentID = $FORM->{deptid})
DELETE FROM tbl_Departments WHERE (DepartmentID = $FORM->{deptid})
See??? Basically, it is always wise to delete rows out of intersection tables before deleting the rows out of the table where the PRIMARY keys (PK) are located.
Basically, to maintain referential integrity between tables, there won't be a method of keeping the "department" reference in the intersection table after you have deleted the row in the tbl_Department table. So, the best thing to do is outright delete the row in the intersection table.
========================================
Buh Bye!
Cheers,
Me