Supervisor / Self Join cascade delete / Relationship Question

[AndyC88]

Hi All!

This is probably really simple - but I feel like I've got a fundamental misunderstanding of how this is meant to work. I have a table with several fields; three of which are "supervisor" fields.

Issue: When I try to delete a member of staff, ALL related records are deleted, rather than just the highlighted fields in red which is what I'd like.

Very grateful for assistance

 

[Gasman]

If you have referential integrity, that is what you want.?
You delete records. You clear fields?
So you would need an Update query to set those fields to whatever you need.?

 

[Minty]

I would not enforce deletes on a self joined table, that's asking for a problem IMHO.
In fact, I'm not sure I would even bother defining that as a relationship.

I'm also not sure I would lay out the supervisor data in that way.
If you can have multiple managers/supervisors (which seems a bit strange) I would put them in a child table so you can have 1 , 3 or 300 supervisors.

I think your current design will lead to lots of problems for when your supervisors have supervisors...

A single supervisorID field in a normal hierarchy would be fine.

 

[arnelgp]

always use Forms and not Naked tables.
in form you can Validate if there is/are records being affected by your actions.

 

[AndyC88]

If you have referential integrity, that is what you want.?
You delete records. You clear fields?
So you would need an Update query to set those fields to whatever you need.?

It's probably my lack of understanding - I did not want to clear the whole field. In the above example, Jack leaves the company and his PersonID is deleted. Presently, with the defined relationships, it deletes ALL staff members who had him as their top level supervisor, rather than delete his PersonID recorded in the other staff supervisor fields...

 

[AndyC88]

I would not enforce deletes on a self joined table, that's asking for a problem IMHO.
In fact, I'm not sure I would even bother defining that as a relationship.

I'm also not sure I would lay out the supervisor data in that way.
If you can have multiple managers/supervisors (which seems a bit strange) I would put them in a child table so you can have 1 , 3 or 300 supervisors.

I think your current design will lead to lots of problems for when your supervisors have supervisors...

A single supervisorID field in a normal hierarchy would be fine.

I did put them in a child table to begin with and used a subform to display it on my main form; it slowed down the form quite a bit so I reverted to having the fields listed in the main table.

 

[Minty]

I think the main problem here is the process, you shouldn't ever delete the record of the person, simply have a DateLeft field and exclude them from any lookups etc. based on that?

 

[AndyC88]

I think the main problem here is the process, you shouldn't ever delete the record of the person, simply have a DateLeft field and exclude them from any lookups etc. based on that?

Interesting. For this particular application there's no requirement to keep the record of anyone who has left so it would seem "cleaner" to me to just delete the record. Thoughts?

 

[mike60smart]

I agree with Minty.

The best option is a related table to store all of the related Supervisors, with a Start & End Date

I take it that the Supervisors change on a regular basis?

 

[Minty]

Interesting. For this particular application there's no requirement to keep the record of anyone who has left so it would seem "cleaner" to me to just delete the record. Thoughts?

If you store anything related to that employee, let's say no 6 - for instance a "who updated this record" field, if you delete that record then that ID has just become meaningless. The who is now unknown.

A single employee record is not going to take up any space in the great scheme of things, and generally, records of any sort shouldn't be deleted, they should be marked as expired or archived, preferably with a date field rather than a check box, so you can see when it happened.

 

[NauticalGent]

A single employee record is not going to take up any space in the great scheme of things, and generally, records of any sort shouldn't be deleted, they should be marked as expired or archived, preferably with a date field rather than a check box, so you can see when it happened.

Not that Minty needs my support, but I could not agree more: this is the accepted "best pratice" within the data-management enterprise.

I personally have suffered minor annoyances because of deleted personal whose information was needed after they had left of even if they happen to return.

 

[MajP]

The purpose of establishing a relationship is to enforce referential integrity. But not all relationships require cascade deletes, that is why that is optional.

This is tough in a self referencing because records are both Parent and Child and can be both. So you cannot use cascade deletes
1. Case 1: Employee A leaves and is no ones supervisor. Just delete that person.
2. Case 2: Employee leaves and is a supervisor. When you delete that person you have to run three updated queries.
Update Personnel Set SupervisorID1 = Null where SuperVisorID1 = IDOfPersonTobeDeleted
Update Personnel Set SupervisorID2 = Null where SuperVisorID2 = IDOfPersonToBeDeleted
Update Personnel Set SupervisorID3 = Null where SuperVisorID3 = IDOfPersonToBeDeleted

Since you have RI set you have to have a supervisor value in Sup1, Sup2, Sup3 that is in Personnel or the value is null. If that person does not have a supervisor, then you either have to have a Dummy Person in the database. "Undefined Supervisor". Then everyone without a 1,2,3 Sup gets assigned the dummy supervisor. Or you have to be your own supervisor.
I personally do not use a dummy. If a person is supposed to get a supervisor, but not yet assigned leave it null. If that person is at the top of the food chain then I make them their own supervisor. This way I know what records are unassigned and which are top dogs.

 

[Pat Hartman]

Unless you have a matrix type organization, each individual has one and only one supervisor. NOT three!!!! This is the typical self-referencing table. To produce an org chart takes either recursive VBA or a query that joins the employee table multiple times. This is easy if your hierarchy depth is limited. Say you have no more than 8 levels to your org chart. That means your query includes eight instances of the employee table which are joined supervisorID to employeeID using left joins.

If you have a matrixed organization, you must use relation tables to implement the many-to-many joins.

 

[AndyC88]

Thanks all, very helpful. @Pat Hartman we have one "line manager" and then several individuals above in the heirachy that have a reporting function on the individual - which may or may not include their line manager (simple! )

After some thinking - it's almost a moot point as the case for an actual "deletion" is going to be very unlikely, and can be easily achieved by running an update query beforehand to set supervisors to Null if required.

Likewise, I don't think there's a requirement for establishing referential integrity via self joins. Thanks for the input all.