I am wondering how to handle entries in a database which should not exist anymore but are still necessary for dependent tables.
Example: I have one table for parking spots, one for cars, and one for the time when a car parked in a specific spot. I want to keep track when which car was on which slot. Lets assume one day one of the parking slots will get removed.
What now? If I delete the spot in the table and I make use of referential integrity all entries from the past associated with this slot will vanish, that's not acceptable. But if I remove the referential integrity and only delete the slot I will have a lot of orphaned entries in the table which stores the entries when which car was parked in a spot.
I am pretty sure there must be a simple answer to handle this problem but unfortunately I was not able to specify the problem good enough to find a solution on stackoverflow/Google. Therefore I have to ask.
Normally, you would just put a flag on in the "parking space" table that says whether it is active or not. Well, actually, perhaps not just a flag, but a date when it is first available and a date when it is no longer available.
Incorporating this into a data model is a bit trickier. In MS Access, I would recommend that you do the date check when attempting to insert references to the space. This can be handled with a trigger.