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?