mysqlsqlinventorydbexpresssqlfiddle

SQL Query to calculates 'reserved' inventory items


We are creating a inventory system for items called readoutprobes and readoutprobekits. The schema, below, is simplified, using the words items and itemkits.

enter image description here

An itemkit, is a predefined collection of 1 or more items, i.e. a kit. In a kit, a specific type of an item, can only occur once. A kit, typically contains ~40 items. The definition of items in a kit, is captured by the itemkit_item table. The inventory for the kits, are captured in the itemkit_containers table.

An itemkit_container do not track physical item containers. Instead, its assumed that a physical itemkit is properly 'assembled', using a set of physical items, but we don't know which ones. When populated, the 'populated' field in an itemkit_containers record, is set to true.

The inventory for items are tracked by a item_containers table. Its existence is monitored by the containers volume. When the volume is 0, the container is considered emptied.

Getting the count of physical item containers, with a volume > 0, for a specific item, is obtained from the item_container table, and the same for the kits

We want to get a 'reserved count' number for each item, reflecting the kits inventory.

For example, say we got an item, named A, having a count of 42. If we are creating an itemkit containing an item named A, and a corresponding itemkit_container, we want to have a count of 'reserved' being 1, for item A.

The 'master query' for items looks like this:

SELECT items.*,         
    ic.item_count
FROM items
LEFT JOIN (
    SELECT p.id, COUNT(*) item_count, ic.item_id
    FROM  items AS p, item_containers AS ic
    WHERE p.id = ic.item_id AND ic.volume > 0
    GROUP BY p.id
    ) AS ic   
    ON ic.item_id = items.id        
GROUP BY items.id    
ORDER BY items.id;

Data in the items table:

enter image description here

Data in the item_containers table:

enter image description here

Data in the itemkits table:

enter image description here

Data in the itemkit_item table:

enter image description here

And data in the itemkit_containers:

enter image description here

As can be observed, the only record of an itemkit, and its inventory, contains items with item ID's = {1,3}

This question is to find out how to query for the number of 'free' (or reserved) physical items, i.e. item_containers inventory there is, at any one point in time.

The above query, returns this result:

enter image description here

We want an additional field, that indicate a 'Reserved' count for each item, reflecting the status of actual inventory for items and itemkits.

For the data above, this would be

A -> Reserved = 1
B -> Reserved = 0
C -> Reserved = 1
D -> Reserved = 0

A db fiddle that creates and populates the above tables is here: DB Fiddle

We are using MySQL 8.0.

NOTE: The answer below is close to correct. However, it does not relate item_containers (actual invnetory) with the itemkit_container records, but instead the itemkit records. This become clear by toggling the populated field in the itemkit_containers table to '0'. I.e.:

enter image description here

The output, even though the kit is no longer populated shows the same 'Reserved' count. Reserved should be equal to '0' in this case. Here is a fiddle for that case: Fiddle where Reserved should be all '0'


Solution

  • Thanks for such detailed description and all the necessary sample data.

    As you already tried in your query you can have the item with quantity by joining items and item_containers table. For calculating free or reserved item you need to left join itemkit_containsers table since inventory for items in a kit is stored there. So just calculate the count for any item in itemkit_containers then you got your reserved quantity and by subtracting it from item_count of item_containsers table will give you free quantity for that item.

    Schema and insert statements:

     CREATE TABLE `items` (
       `id` int NOT NULL AUTO_INCREMENT,
       `name` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'oligoname + fluorophore wavelength',
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='ReadoutProbes for mFISH Survey';
     
     CREATE TABLE `item_containers` (
       `id` int NOT NULL AUTO_INCREMENT,
       `item_id` int NOT NULL COMMENT 'content of tube',
       `volume` float(12,2) NOT NULL COMMENT 'volume in micro liter (uL)',
       PRIMARY KEY (`id`),
       KEY `fk_item_containers_items` (`item_id`),
       CONSTRAINT `fk_item_containers_items` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=764 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Physical tubes received from vendor';
     
     CREATE TABLE `itemkits` (
       `id` int NOT NULL AUTO_INCREMENT,
       `name` varchar(100) DEFAULT NULL,
       PRIMARY KEY (`id`),
       UNIQUE KEY `name` (`name`),
       UNIQUE KEY `Unique` (`name`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1030 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='A readout kit is a collection of readouts, and defined in a codebook';
     
     CREATE TABLE `itemkit_containers` (
       `id` int NOT NULL AUTO_INCREMENT,
       `itemkit_id` int NOT NULL,
       `populated` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Field used for checking in checking out a tray',
       PRIMARY KEY (`id`),
       KEY `fk_readoutkit_tray_readoutkits` (`itemkit_id`),
       CONSTRAINT `fk_readoutkit_tray_readoutkits` FOREIGN KEY (`itemkit_id`) REFERENCES `itemkits` (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1027 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Physical readoutkit_tray';
     
     CREATE TABLE `itemkit_item` (
       `itemkit_id` int NOT NULL,
       `item_id` int NOT NULL,
       UNIQUE KEY `Uniqueness` (`itemkit_id`,`item_id`),
       KEY `fk_readoutkit_item_readout_probes` (`item_id`),
       CONSTRAINT `fk_readoutkit_item_readout_probes` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
       CONSTRAINT `fk_readoutkit_item_readoutkits` FOREIGN KEY (`itemkit_id`) REFERENCES `itemkits` (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='associations table for definition of a readout kit';
           
     insert  into `items`(`id`,`name`) values 
     (1,'A'),
     (2,'B'),
     (3,'C'),
     (4,'D');
     
     insert  into `itemkits`(`id`,`name`) values 
     (1,'Kit_1');
     
     insert  into `itemkit_containers`(`itemkit_id`,`populated`) values 
     (1,0);
     
     insert  into `itemkit_item`(`itemkit_id`,`item_id`) values 
     (1,1),
     (1,3);
     
     insert  into `item_containers`(`item_id`,`volume`) values 
     (1,1.00),
     (2,1.00),
     (3,1.00),
     (4,1.00),
     (1,1.00);
     
    

    Query:

    select i.id,i.name,sum(ic.volume) as total_volume,
          sum(coalesce(ii.item_count,0)) as Reserved 
          from items i inner join item_containers ic on i.id=ic.item_id
          left join (select item_id,count(*) as item_count from itemkit_containers ic
          inner join itemkit_item i on ic.itemkit_id =i.itemkit_id and ic.populated=1
          group by item_id) ii
          on i.id=ii.item_id
          group by i.id,i.name
          order by i.id,i.name
    

    Output:

    id name total_volume Reserved
    1 A 2.00 0
    2 B 1.00 0
    3 C 1.00 0
    4 D 1.00 0

    db<fiddle here

    Db-Fiddle with with populated and not populated itemkit_containsers:

    Select queries (sample data):

     SELECT * from items;
     SELECT item_id, volume from item_containers;
     SELECT * FROM itemkits;
     SELECT itemkit_id, populated FROM itemkit_containers;
     SELECT * FROM itemkit_item;
    

    Output:

    id name
    1 A
    2 B
    3 C
    4 D
    item_id volume
    1 1.00
    2 1.00
    3 1.00
    4 1.00
    1 1.00
    id name
    1 Kit_1
    2 Kit_2
    itemkit_id populated
    1 0
    2 1
    itemkit_id item_id
    1 1
    2 2
    1 3

    Query:

          select i.id,i.name,sum(ic.volume) as total_volume,
          sum(coalesce(ii.item_count,0)) as Reserved 
          from items i inner join item_containers ic on i.id=ic.item_id
          left join (select item_id,count(*) as item_count from itemkit_containers ic
          inner join itemkit_item i on ic.itemkit_id =i.itemkit_id and ic.populated=1
          group by item_id) ii
          on i.id=ii.item_id
          group by i.id,i.name
          order by i.id,i.name
    

    Output:

    id name total_volume Reserved
    1 A 2.00 0
    2 B 1.00 1
    3 C 1.00 0
    4 D 1.00 0

    db<fiddle here