0

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.

  • By "serialized items", do you mean "each item has a unique serial number"? By "track" you mean that you need to know the entire history of each "item"? Do the "combined" items have a pseudo-serialnumber for reference? – Rick James Mar 31 '17 at 22:51
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.