Inventory design help

Ignition
1

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 (143 KB)

2

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.

3

I had to leave office. I will reply tomorrow.
Thanks Travis

I hate talking through a Mobile phone.

4

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 (147 KB)

5

Are the id_machine, id_company and id_employee columns foreign keys in the tbl_invtransdetails table?

6

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 (148 KB)

7

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.

8

Thanks

9

Ready for some help here. Here is the tables:

tables.JPG420×820 11.3 KB

Here is the test data in the table:

TableAll.JPG848×820 98 KB

Here is the query I came up with to get the last Hard count:

Last Hard Counts.JPG850×820 76.4 KB

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

10

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.

11

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]

12

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;

13

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]