# Inventory design help **URL:** \ **Category:** Ignition\ **Created:** [April 6, 2011, 2:56pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847 "2011-04-06T14:56:49Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [April 6, 2011, 2:56pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/1 "2011-04-06T14:56:49Z")
Need some help here, I am good with the system all the way down to the “tbl\_invtransactions” table. This is the point I question my thinking. I do not want to store the quantity of a material by location in a table. I wish to have it calculated on the fly when needed. This is recommended in many places I have researched. Try to explain now… A transaction is going to be one of the 3 types (In, Out, Hard Count). Hard count will be like a living inventory point for each location. They would do a physical count of the location at any time and enter it into the system. To calculate the qty in a location at a later date everything is flagged by the date of the last Hard count for that location. latest “Hard Count” for that location + the “In’s” since that date - the “Out’s” since that date. This would allow them to do a physical inventory of a location at anytime. What are the Pro’s and Con’s of this way of doing it? How much would table size come into play if I was to not purge any of the records? The details of the reasons for material going out ( tbl\_invtransdetails ). What would be the best way to set this up based on the info in the diagram? I do not feel like there should be a bunch of fields in this table that could be null or blank. The attached pdf of the database diagram should help. [TableStructure.pdf](https://us1.discourse-cdn.com/inductiveautomation/original/1X/0652681b812a0b68e7092edf3ec4a4e15b1c6a97.pdf) (143 KB) ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [April 6, 2011, 3:35pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/2 "2011-04-06T15:35:36Z")
It is true calculating the quantity on the fly is better. If you do it that way you are guaranteed to calculate correct data making the data more reliable. However, it is harder to query the information in one statement. You most likely have to do a subquery to get the last hard count date in order to get all of the ins and outs since that date. You can also keep a count on the tbl\_ourmaterials table. That means you have to update the count correctly in the application once transactions are added or removed. This would reduce query time but you have to make sure the application updates it correctly. If it doesn’t that can lead to inconsistent and non-reliable data. Table size is not that important here if you use foreign keys and indexes correctly. You will get good performance even if the table is really large. If you have multiple reasons for a transaction it is perfectly fine to add another table. That is normalizing the database correctly. If the operator types in the reason every time you can remove the tbl\_invreasons table. I like your approach and if you need help with the specific queries let us know. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [April 6, 2011, 7:31pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/3 "2011-04-06T19:31:46Z")
I had to leave office. I will reply tomorrow. Thanks Travis I hate talking through a Mobile phone. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [April 7, 2011, 12:17pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/4 "2011-04-07T12:17:12Z")
Yes, I plan to calculate the quantity on the fly. I may look for a little guidance in the queries. I altered the table tbl\_invtransdetails in the new attachment. This is the table I just don’t feel comfortable with. Of the 3 added fields only 1 will contain data for each record the other 2 would be nulls. I can live with it, but is there a better way? [TableStructure.pdf](https://us1.discourse-cdn.com/inductiveautomation/original/1X/783cd4b855165b7fa3e7823a85ef52be9a327134.pdf) (147 KB) ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [April 7, 2011, 6:31pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/5 "2011-04-07T18:31:30Z")
Are the id\_machine, id\_company and id\_employee columns foreign keys in the tbl\_invtransdetails table? ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [April 7, 2011, 6:49pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/6 "2011-04-07T18:49:36Z")
Yes they are. They would go back up and link into the tables: tbl\_employees tbl\_companys tbl\_machines I see I did not show that in the diagram… My Bad. I attached a new one with all the FK’s displayed. [TableStructure.pdf](https://us1.discourse-cdn.com/inductiveautomation/original/1X/450e5c0e4a8448dd4d00c8b2308e3af8c839a6da.pdf) (148 KB) ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [April 7, 2011, 10:19pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/7 "2011-04-07T22:19:04Z")
Well, there is not a whole lot you can do without creating more tables. Since they are foreign keys it is better to leave them null then to use one column that could either be the machine, company or employee. It is not that bad to have columns that will be null. You can perform LEFT JOINs and use CASE statements in your SQL query to display the right reason. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [April 8, 2011, 12:05pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/8 "2011-04-08T12:05:54Z")
Thanks ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [May 27, 2011, 6:13pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/9 "2011-05-27T18:13:59Z")
Ready for some help here. Here is the tables: ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/93cdea8c424312c3261c0c24ec042e6f183bbcad.JPG) Here is the test data in the table: ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/becbe9c0ceae9c14e39322493a55dcfcb6d52ab2.JPG) Here is the query I came up with to get the last Hard count: ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/6dc333ba7dda6e4e822a56252f75ce7103937302.JPG) I am lost now on how to get a total by “id\_ourmaterial” by “id\_location”. I need it to go from (last “Hard Count” + all “In”) - “Out” and display a total by location. I am looking for it to show: id\_ourmaterial id\_location Qty 38 15 210 38 12 185 ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [June 1, 2011, 2:37pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/10 "2011-06-01T14:37:45Z")
Made a view “vw\_lasthardcount” using this: `Select id_ourmaterial, id_location, max(date) as date FROM tbl_invtransactions Where id_invtranstype = 3 group BY id_ourmaterial, id_location` Then using this query I can get materials totaled location and type of transaction: `SELECT a.id_invtranstype, a.id_ourmaterial, a.id_location, sum(a.qty) FROM tbl_invtransactions a, vw_lasthardcount v Where a.id_ourmaterial = v.id_ourmaterial and a.date >= v.date and a.id_location = v.id_location GROUP BY id_ourmaterial, id_location, id_invtranstype` Now I need to get the math done in a query, any help please. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [June 1, 2011, 5:48pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/11 "2011-06-01T17:48:21Z")
Code for table: [code]/\* Navicat MySQL Data Transfer Source Server : bdihmi Source Server Version : 50137 Source Host : srv-bdihmi:3306 Source Database : TestDB Target Server Type : MYSQL Target Server Version : 50137 File Encoding : 65001 Date: 2011-06-01 13:47:02 \*/ SET FOREIGN\_KEY\_CHECKS=0; * * * – Table structure for `tbl_invtransactions` * * * DROP TABLE IF EXISTS `tbl_invtransactions`; CREATE TABLE `tbl_invtransactions` ( `id_invtransaction` int(11) NOT NULL AUTO\_INCREMENT, `id_employee` int(11) NOT NULL, `id_invtranstype` int(11) NOT NULL, `id_ourmaterial` int(11) NOT NULL, `id_location` int(11) NOT NULL, `qty` int(11) NOT NULL, `date` datetime NOT NULL, PRIMARY KEY (`id_invtransaction`), KEY `fk_locations_invtransactions` (`id_location`), KEY `fk_ourmaterials_invtransactions` (`id_ourmaterial`), KEY `fk_employees_invtransactions` (`id_employee`), KEY `fk_invtranstypes_invtransactions` (`id_invtranstype`), CONSTRAINT `fk_employees_invtransactions` FOREIGN KEY (`id_employee`) REFERENCES `tbl_employees` (`id_employee`) ON UPDATE CASCADE, CONSTRAINT `fk_invtranstypes_invtransactions` FOREIGN KEY (`id_invtranstype`) REFERENCES `tbl_invtranstypes` (`id_invtranstype`) ON UPDATE CASCADE, CONSTRAINT `fk_locations_invtransactions` FOREIGN KEY (`id_location`) REFERENCES `tbl_locations` (`id_location`) ON UPDATE CASCADE, CONSTRAINT `fk_ourmaterials_invtransactions` FOREIGN KEY (`id_ourmaterial`) REFERENCES `tbl_ourmaterials` (`id_ourmaterial`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO\_INCREMENT=77 DEFAULT CHARSET=latin1; * * * – Records of tbl\_invtransactions * * * INSERT INTO `tbl_invtransactions` VALUES (‘64’, ‘464’, ‘3’, ‘38’, ‘15’, ‘100’, ‘2011-05-26 14:45:16’); INSERT INTO `tbl_invtransactions` VALUES (‘65’, ‘464’, ‘2’, ‘38’, ‘15’, ‘10’, ‘2011-05-26 14:46:19’); INSERT INTO `tbl_invtransactions` VALUES (‘66’, ‘464’, ‘2’, ‘38’, ‘15’, ‘10’, ‘2011-05-26 14:46:37’); INSERT INTO `tbl_invtransactions` VALUES (‘67’, ‘464’, ‘2’, ‘38’, ‘15’, ‘10’, ‘2011-05-26 14:47:00’); INSERT INTO `tbl_invtransactions` VALUES (‘68’, ‘464’, ‘1’, ‘38’, ‘15’, ‘15’, ‘2011-05-26 14:47:19’); INSERT INTO `tbl_invtransactions` VALUES (‘69’, ‘464’, ‘3’, ‘38’, ‘12’, ‘200’, ‘2011-05-27 11:24:27’); INSERT INTO `tbl_invtransactions` VALUES (‘70’, ‘464’, ‘2’, ‘38’, ‘12’, ‘10’, ‘2011-05-27 11:25:34’); INSERT INTO `tbl_invtransactions` VALUES (‘71’, ‘464’, ‘2’, ‘38’, ‘12’, ‘7’, ‘2011-05-27 11:26:45’); INSERT INTO `tbl_invtransactions` VALUES (‘72’, ‘464’, ‘1’, ‘38’, ‘12’, ‘2’, ‘2011-05-27 11:27:00’); INSERT INTO `tbl_invtransactions` VALUES (‘73’, ‘464’, ‘3’, ‘38’, ‘15’, ‘300’, ‘2011-05-27 11:30:08’); INSERT INTO `tbl_invtransactions` VALUES (‘74’, ‘464’, ‘2’, ‘38’, ‘15’, ‘73’, ‘2011-05-27 11:30:36’); INSERT INTO `tbl_invtransactions` VALUES (‘75’, ‘464’, ‘2’, ‘38’, ‘15’, ‘23’, ‘2011-05-27 11:30:58’); INSERT INTO `tbl_invtransactions` VALUES (‘76’, ‘464’, ‘1’, ‘38’, ‘15’, ‘6’, ‘2011-05-27 11:31:09’); [/code] ---
**Author:** ![dave.fogle](https://avatars.discourse-cdn.com/v4/letter/d/43a26b/32.png) [@dave.fogle](https://forum.inductiveautomation.com/u/dave.fogle)\ **Post date:** [June 1, 2011, 6:54pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/12 "2011-06-01T18:54:54Z")
You can try doing something along these lines. It doesn't look all that nice but I think it'll get the job done. You'll have to change the names to represent your column and table names but I think the way it works should apply to your scenario as well. `SELECT SUM(a.qty) as Total, locationID, materID FROM ((Select -(qty) as qty, locationID, materID from test2 Where typeID = 2) UNION (SELECT SUM(qty) as qty, locationID, materID FROM test2 WHERE typeID = 3 or typeID = 1 GROUP BY locationID, materID)) as a GROUP BY locationID, materID;` ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [June 2, 2011, 12:31pm UTC](https://forum.inductiveautomation.com/t/inventory-design-help/2847/13 "2011-06-02T12:31:49Z")
Well Dave with your example there and all the views I had made I was able to put this code together. It uses no views and all sub query’s. Not sure if this is the most efficient way to do this. Will be curious to see Travis or anyone Else’s remarks on this. [code]SELECT warehousename, locationname, SUM(a.qty) as Total, a.id\_location, a.id\_ourmaterial FROM ((Select -(qty) as qty, id\_location, id\_ourmaterial, id\_invtransaction from (SELECT p.id\_invtransaction, p.id\_employee, p.id\_invtranstype, p.id\_ourmaterial, p.id\_location, p.qty, p.date FROM tbl\_invtransactions p, (Select id\_ourmaterial, id\_location, max(date) as date FROM tbl\_invtransactions Where id\_invtranstype = 3 Group BY id\_ourmaterial, id\_location) as v Where p.id\_ourmaterial = v.id\_ourmaterial and p.date \>= v.date and p.id\_location = v.id\_location) as m Where id\_invtranstype = 2) UNION ``` (SELECT SUM(qty) as qty, id_location, id_ourmaterial, id_invtransaction FROM (SELECT p.id_invtransaction, p.id_employee, p.id_invtranstype, p.id_ourmaterial, p.id_location, p.qty, p.date FROM tbl_invtransactions p, (Select id_ourmaterial, id_location, max(date) as date FROM tbl_invtransactions Where id_invtranstype = 3 Group BY id_ourmaterial, id_location) as v Where p.id_ourmaterial = v.id_ourmaterial and p.date >= v.date and p.id_location = v.id_location) as n WHERE id_invtranstype = 3 or id_invtranstype = 1 GROUP BY id_location, id_ourmaterial)) as a ``` INNER JOIN tbl\_locations ON a.id\_location = tbl\_locations.id\_location INNER JOIN tbl\_warehouses ON tbl\_locations.id\_warehouse = tbl\_warehouses.id\_warehouse GROUP BY a.id\_location, a.id\_ourmaterial;[/code]