Database design for tracked inventory that could get “transformed” I need to track some serialized items in the inventory that can be either dismantled into multiple items or combined together into a single item. I have thought of a few options:


Flow tracking - Use two tables for inv_group_out -> inv_group_in with a joining table. Entry into either table can be a single item or multiple items each referencing the original inventory_id. New inventory item(s) get created for the re-entry of the inventory.
Movement tracking - Mark all changes in a single modifications table. Can't really think about how to reliably carry this out though.
Flow tracking alternative - Use the same table for original purchase/sales using a separate column to indicate that these are just inventory movements


Would love to hear some opinions on which would be preferred.
I'm guessing that you want two things:  A full history of each item, and a "current" location of each item.  Let's make that two database tables.

When items are "combined", let's give the combination a separate serial number.

CREATE TABLE History (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    item_id ...,   -- serial number of item
    combined_id ...,  -- pseudo-serialnumber of combination (or 0 if item by self)
    dt DATE TIME,   -- when the change occurred
    PRIMARY KEY(id),
    INDEX(item_id, dt)  -- handy for listing history of an item
) ENGINE=InnoDB;

CREATE TABLE Current (
    item_id ...,   -- serial number of item
    combined_id ...,  -- what it is currently in
    dt DATE TIME,   -- when it was constructed
    PRIMARY KEY(item_id),
    INDEX(combined_id)   -- to identify current components
) ENGINE=InnoDB;

CREATE TABLE Combos (
    combined_id INT UNSIGNED AUTO_INCREMENT NOT NULL, -- for generating ids
    etc -- description or whatever
    PRIMARY KEY(combined_id)
) ENGINE=InnoDB;


When you "combine" a bunch of items:


INSERT into Combos to get a new combined_id.
Change Current as needed
INSERT multiple rows into History.