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?
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.