views:

86

answers:

7

Assume that you are writing a simple app. The model is that a 'project' has a 'category'. The project's category can be edited by choosing from a drop down list that contains all possible categories.

The user creates the following Categories: C1, C2, C3.

The user creates the following Projects, Category associations: [P1, C1], [P2, C2], [P3, C3]

Later, category C2 was deleted. Now if the user tries to edit the category of Project P2, there are a few problems: a. The categories need to be retrieved to populate the drop down list. But deleted categories will not be retrieved. Hence C2 will not be retrieved. (If deleted categories are retrieved, then the Drop Down List will be full of deleted, unusable fields)

b. If C2 is also retrieved then there is a risk that the user can assign the deleted category C2 to P2 once again.

What's the best design approach to handle this?

+1  A: 

Don't allow them to delete C2. Or, alternately, allow them to delete C2 as needed, but change all the C2 categories in the system to an undefined category. That way they have to select a category the next time they edit the project (and it's still valid since it points to a previously created "undefined" category).

Michael Todd
+1  A: 

You're running into the issue of referential integrity.

You can't delete a category as long as it's still referenced by an existing project. However, you might consider adding a flag IsDeleted and now allow new projects to be assigned to that category.

Eric J.
+2  A: 

I have used an "Obsolete" field that doesn't delete the category from existence, just hides from the appropriate screens/drop down menus, but allows for the data to be used on reports for historical reasons.

simon
simon, using the 'obsolete' field is the 'soft delete' situation. But even there, how do you display the category? e.g. if your Project class had a foreign key reference to the Category class, the reference is still valid, but you cannot display the field because it is obsolete.
In my particular case the "Obsolete" data was also Date related; i.e. old data and not useful for assigning to new data. But I would think that if the category is now "Obsolete" and the user went to edit the upstream link, the Category should not be allowed. In the pull down you would have to validate the current value and if it's an "Obsolete" record, then indicate its "Obsolete" or set it to a default, the user is editing the record, you probably don't want to allow a "Obsolete" selection. If the user decides not to commit the change then there is no change.
simon
+1  A: 

In general, for structural elements like this, unless you have a serious lack of data storage, leave the element intact with a deleted flag, so that things that reference it from before it was deleted do not become corrupted. Alternatively, impelement a ripple-delete that removes everything that references it as well.

Paul McMillan
+1  A: 

To maintain consistency, you cannot delete C2 but you can add the option of disabling it instead of actually deleting. That way new projects are not assigned to C2.

Virat Kadaru
A: 

For reference tables, you need to either re-assign all the old data to a new valid category, or leave the category there with some additional fields that express when it is valid, e.g. the date or date range for which it is valid.

A boolean 'deleted' flag means you won't (might not) be able to edit old data without updating the category, but that can be deferred until old data gets edited.

MatthewMartin
MatthewMartin - thanks, I realized that I can still display the old data which now has a 'deleted' flag. But when the user clicks the 'Edit' button, he is only presented with the options which do not have the 'deleted' flag set.
A: 

I've handled this with the soft delete mentioned by others. Then when populating my dropdown list, I add C2 to the top of the dropdown list when modifying a project that is assigned to C2 (for deleted categories only). That way, things can be in C2, and can be moved out of C2, but nothing new can be placed in C2 (because if it's not already in C2, it's not in the dropdown list.)

I recall that the code to do this was not particularly pretty, but not very difficult either. I don't have the project open right now, but I can add more details later if you are interested.

quillbreaker