Mysql – Database design for tracked inventory that could get “transformed”

database-designMySQL

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:

  1. 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.

  2. Movement tracking – Mark all changes in a single modifications table. Can't really think about how to reliably carry this out though.

  3. 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.

Best Answer

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:

  1. INSERT into Combos to get a new combined_id.
  2. Change Current as needed
  3. INSERT multiple rows into History.
Related Question