tracking inventory movement over time and space with a relational database

As part of a fish inventory system I'm designing, I have to track where the fish are. I need to be able to show instance data - i.e. there were 14 bluefish in tank3 and 17 redfish in tank5 last Monday. And I need to be able to show chain of custody- i.e. after 20 greenfish arrived on May 4, 2000, 5 went to tank3 and 15 went to tank2. On June 3rd 10 moved from tank2 to tank5.

If I store the fish count and the count date then instance data is easy and no calculations are necessary.

If I store the fish increment/decrement and the transfer date, then instance data has to be calculated. On the plus side, the user enters the number they know (how many fish they moved) rather than the number they don't know (how many fish total/position).

With either of these options a custody chain would have to be inferr开发者_如何学Ced (e.g. 5 redfish left tank 2 at 12:01 on May 2 and 3 redfish arrived in tank7 at the same (or very close) time. Therefore those 3 redfish came from tank 2). This seems like it would be error prone.

So, this means I need to store the chain of custody. Which of these (or another model entirely!) would work better?

CREATE TABLE  fish_transfers(
    transfer_id INT unsigned NOT NULL auto_increment PRIMARY KEY,
    fishtype_id INT unsigned NOT NULL,
    tank_id INT unsigned NOT NULL,
    from_tank INT unsigned, //can be null if this is the first tank
    to_tank INT unsigned, //can be null if this is the last tank
    fish_count INT unsigned NOT NULL,  //current count
    count_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP)

OR

CREATE TABLE  fish_transfers(
    transfer_id INT unsigned NOT NULL auto_increment PRIMARY KEY,
    fishtype_id INT unsigned NOT NULL,
    tank_id INT unsigned NOT NULL,
    target_tank INT unsigned,   //can be null if this is the first or last tank
    transfer_count INT NOT NULL,  //increment or decrement
    transfer_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP)
    //target tank is known to be after current tank if transfer_count is negative and before current tank if transfer_count is positive

Proposal (looks like your 1st solution):

CREATE TABLE  fish_transfers(
    transfer_id INT unsigned NOT NULL auto_increment PRIMARY KEY,
    fishtype_id INT unsigned NOT NULL,
    source_tank_id INT unsigned,  // can be null if this is the first tank
    target_tank_id INT unsigned,  // can be null if this is the last tank
    transfer_count INT NOT NULL,  // always positive (amount of fishes transferred)
    transfer_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP)

The solution offered by @frosty-z is indeed the better solution, but I thought I should add an explanation why:

The reason that this is a better option is that it is straightforward: all the relevant data to the transfer is included in a single record so it's simple to understand what happened. The 2nd solution you suggested is not as good because it requires inference by looking at multiple records. KISS.