Model for tracking objects that change names

[vjwilson]

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?

[adam_k]

In my book either all info regarding an event that could change (type of fuel sounds like this) have to get saved with the event itself (ie tank containing jet emptied).

If the size of these info along with the number of records repeating them is high, then you might want to archive both the tanks and their events. You can easily archive by adding a column in the tanks table that will hold if this tank is used or not and incrementing ID for each tank.

[BitBlt]

Sounds like this could be handled with a TANK_STATE table (child of Tank table) that would have a unique id and foreign keys TankID, LocationID (the physical location), TankState ("used", "unused", "retired", etc.), FuelType ("jet", "rocket", etc.) and StateChangeDateTime.

That way, if the Tank is taken out of service, a new row for that tank is inserted with a new TankState, a new StateChangeDateTime and LocationID (if it moved).

If a new fuel type is put into the tank (since I hope it can only hold one type of fuel at a time), then a new row for that tank is inserted with a new FuelType and a new StateChangeDateTime.

You see where this goes? As soon as there is a change in any of the attributes affecting the tank (or other ones not mentioned), insert a new row with appropriately changed date/time and attributes. Easy to query, easy to group, never lose a tank or its history.

Hope this gives you some usable ideas! Good luck!