CODEWITHSUNDEEP
sqldata-modeling
ryvantage
ryvantage

Reputation: 13496

MVC design: Deleting an entity with undeletable children

I develop a lot of MVC applications on my own and I am looking for good resources related to a particular problem.

In a lot of my applications, I need to give the end-user CRUD abilities on a lot of entities. But, often times I'll find that these entities are parents of un-deletable children records. Take the following model as an example:

enter image description here

Here, I want to give the user the ability to delete employees (maybe because they've left the company, etc.). However, these employees are listed on projects. To change the creator_employee_id on projects would make the data obviously incorrect, and to cascade delete the projects where that employee is listed is clearly a no-no.

So, I've handled this in the past by adding a flag:

enter image description here

So, when a user goes to delete an employee, I check if deleting that employee violates any foreign key constraints. If it doesn't, I delete the employee. If it does, I set the employee to "active = 0".

However, this entire process of checking, iffing, deleting or deactivating is really complicated and cumbersome to compensate for on the code-level. Because an employee might not just be tied to projects, but to other entities as well, you have to write a check for each entity, determine if it makes sense to "delete" or "deactivate," etc. Then you have to give the user a way to view/reactivate inactive records.

My question is: Are there any other approaches to handling this specific kind of problem? I've tried looking around the web, but this problem is difficult to find the wording for. Perhaps even just the right words will help point me in the right direction. I hope this problem makes sense.

Upvotes: 0

Views: 80

Answers (2)

Aheho
Aheho

Reputation: 12821

My approach would be to set the active flag to false in all cases when an employee leaves. Keep that code simple and don't try to use branching logic to handle different scenarios based on the existence of related projects.

If you really set on removing old employees when they don't have any projects related to them, then setup a maintenance task that runs monthly, weekly, or whatever, that cleans out any inactive employees with no related records.

Upvotes: 0

Gerd Wagner
Gerd Wagner

Reputation: 5673

I think that the only clean alternative to deleting entities is to manage their history, possibly with an active flag as in your example (or better with lifecycle timestamps). However, you have to treat all entities in a uniform manner and not delete some of them (those that don't have dependent entities), but not others (having dependent entities).

Upvotes: 1

Related Questions