Suppose I have a table Inspection with a field for Place (the location where the Inspection occurred). The field is a foreign key to a Place table, where each place has a Name, among other things. The Place names can change over time (for instance the Place could be a restaurant where the name changes when its ownership changes), but we would like the Inspection to continue to hold the original value it was given when it was created.
Is there a best practice for something like this? One thought is to have two fields, one to store the foreign key, regardless of whether it's been updated, and another to hold a string value for the original Place name. Another thought is to simply make Places immutable. You would have to create a new Place every time the name changed, rather than updating existing Places. Perhaps you could even related one or more Places somehow so that you know that multiple rows in the table actually relate to the same physical location.