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?

Three tables with three different kinds of information

TankContents
TankContentId, Description, etc...

PhysicalTanks
PhysicalTankId, Description, etc...

Tanks
TankId, PhysicalTankId, TankContentsId, DateBegin, DateEnd

This design allows you to keep your existing model mostly intact, but to record all of the information that you need. You just set the DateEnd value instead of deleting a tank, so there is never a need to cascade information or deal with orphans or widows. When a physical tank is used for another purpose (content), you just create a new row for it in the Tanks table and you're good to go.

-PatP