I have run into an interesting problem. I have inherited a database for daemon process that polls and accepts reports from remote embedded systems. Each site that has one of these systems can monitor over a dozen different fuel tanks. (In practice, most monitor 2, 3 or 4 tanks.)
When something happens, such as a tank being refilled or a tank reaching a minimum level, the program saved that event in a Postgres database. The way the database was originally constructed, it was saving all the information from each fuel tank (type of fuel, etc.) in the event record, even though there was a separate "tanks" table. I added a foreign key field to the table to associate it with a particular embedded unit, and a foreign key to the events table to associate it with a particular tank.
Now here's the problem: tanks can be added, removed, or have the type of fuel they store changed at any time. Adding tanks shouldn't be a problem, but if one is removed, those event recorded would be "orphaned". Worse if the type of fuel is changed, from say "jet" to "rocket", then when someone searches through the history, they would think all those old events happened to the "rocket" fuel, when in fact they happened to the "jet" fuel.
I have received a couple of suggestions offline: (1) make a second, archive table of the tanks, and when anything changes, move that tank record, with its unique ID, to the archive table, and make a new record with new ID for the new state of the tank, or (2) and an "active" field to the tanks table, and still create new rows when specs change, but only flag the current state of the tanks as "active".
Does anyone have any opinion on these proposed solutions, or another idea that might work?