Efficient Design help

We are about to start a program that tracks the inventory (and other particulars about the inventory) in multiple cells, in multiple locations. We have tried a few test approaches, but keep running into roadblocks. Here is the basic scenario - One main server with a redundant backup, multiple locations (about 25) and about 100 cells at each location. Each cell stores a quantity of a particular part, and certain particulars (up to 12) that pertain to the part being stored within that cell.
We are having a difficult time determining the most efficient approach to take when programming for this inventory application. What we envision is a layout where all locations have screens that are templated, and filled in with information that was brought in by a DB query, based on the location entered by the operator. As inventory quantity and particulars change, the operator will click on a cell, enter the new quantity and particulars, hit Save, and it will be recorded. How can we achieve this without having ~2500 (25 locations, 100 cells each) sets of tags, transaction groups and DB tables???
It would be nice to have just one table (or, 3 joined tables, with location, cell, cell data), if possible. And, we want to record the new data as a new record, not edit the existing data. This way we have some historical information if needed. It may be the obvious that is escaping on this, but nonetheless, it still seems to be somewhat of a logistical quagmire. Any suggestions are greatly appreciated.

You may want to treat this as something of a point-of-sale system. Treat each entry in the inventory table as a credit or debit, using positive or negative numbers. That way, a sum query does the work for you.

Complexity is added in warehousing or the number of locations a similar part is in, but not terribly so.

As a table for where things get used, you can think of it as “customers”. Throw in a price for the items and you can add up where the inventory goes.

I can’t see where transaction groups would even be necessary, unless I’m missing something.

Tables: inventory, “customer”, warehouse-- that should be enough to start. If you get to the cost evaluation, you may want to add in a “sales” table for easier tabulation.

Forgive my using the words customer and warehouse. I’m just not certain what you mean by “location”.

You may be on the right track, but if we plan it the way you described, would it still be feasible to use a popup screen every time an operator clicked on a cell, to either view all of the cells particulars, or to modify the particulars within that cell? (Our initial thought was to use indirect tags). Also, what is meant by “location” is there are 25 different physical locations throughout the country, and by “particulars” we are referring to colors, grades, compounds, etc. of a certain part within a cell.

Ok, let me see if I can wrap my head around this. For one part, there can be, for instance, multiple colors or multiple grades? If so, then for inventory purposes, wouldn’t these really be separate parts? Kind of like the difference between a 5mm black oxide bolt and a 5mm stainless bolt?

Seems like you can containerize things around “location” and “cell” with a table to describe individual products.

You might get some ideas from http://databaseanswers.org/data_models/index.htm specifically the model for Warehouses of boxes of files

I think both of you are on the right track, but maybe I didn’t give enough explanation initially (was trying to be brief, yet complete. Doesn’t always work :slight_smile:
These facilities have cells (bins) that store sand(s). Each sand can be a different type, texture, color, have different properties, etc… Each property is given a grade based on that properties quality, and the grades of each property are combined for an overall grade. So, a bin may have XXX Lbs. of Type A sand. This load of sand may have 7 different properties, each of varying grades. Another bin may have the same Type of sand, but with 5 different propeties of various grades. Also, these two bins can be blended to increase/decrease the overall grade.
We are wanting to track the quantities of each bin, adding/subtracting weight, and tracking the properties and grades every time X quantity is added to a bin. Hopefully this clears it up a little, from muddy to mirky.

We do something similar to this. We just have a table of bins with descriptive names (“Bin 101”) and identifiers used on the actual screen (“101a”), while the actual information about the contents of the bin is stored in a ‘transaction table’ similar to what Jordan described. We make calls simple from Ignition by wrapping queries in stored procedures like spGetBinProduct that sums up the table over an appropriate time period. To make things simple, client tags (this would be even better if SQLTags supported the dataset type) retrieve the results of the SQL query and the screens use the client tags.

The only way this is going to be complicated is if you’re allowing operators to top load a different grade whereby a bin holds two grades (ie grade ‘C’ is half full with x weight of material and the operators decide to top off the bin with grade ‘B’ figuring that they can cleanly dump grade ‘C’ by releasing x weight.

I’m on vacation for one day, and the complexity just went up by a factor of wow. :laughing:

So if I’m reading this right each bin is actually a mixture? If so, then for the inventory tracking, is there anything that gets down a base component level? Or is there even a base component in sand? Besides silicon, I mean…

Thank you to all of you and your good ideas - we decided to go with a combination - a table that can be added to, and edited (somewhat), with the operator using a table instead of graphics. This way it can be operated quicker and give more information per page. That being said, an operator either adds a bin or edits a current bin. The program will run the same piece of instruction either way, inserting the information on the add/edit popup as a new record into the DB. This all works great.
The problem we are having now is that when we run a query on the table, we always want it to populate it with the bins (ex 1-30) and order it by t_stamp. Certain bins will be edited more frequently than others, so we can’t just use the t_stamp, we first GROUP BY the bin #, then order by t_stamp. Here is what we have:


bin_inv_tbl c
JOIN id_facil_bin m ON m.facil_id = c.facil_nmbr
m.facil_id = {Root Container.Facilities.Facil_Name_ID}
GROUP BY bin_nmbr

The problem with the above code is that the result will only grab the first of each bin number the query comes across, instead of the one with the latest time stamp for each bin. (i.e. - we want to retrieve each bin, but only one record for each bin, and it needs to be the one with the latest time stamp.

We have tried the DISTINCT instruction, but we don’t want it to apply to all of the fields, only the Bin number field. Any good ideas??

I’m going to go out on a limb and say you could use a sub selection to get your groupings too.

SELECT * FROM (SELECT UNIQUE [stuff] FROM [places]) GROUPBY [catagory]

I thank all of you for your input on this. It was a struggle at first, but after looking at your suggestions, and some testing, we finally came up with something that works quite well. In the event that you might need something that does the same thing, here’s what we came up with:

SELECT f.bin_inv_tbl_ndx, f.bin_nmbr, f.facil_nmbr, f.t_stamp FROM ( SELECT bin_nmbr, facil_nmbr, max(t_stamp) AS max_t_stamp FROM bin_inv_tbl GROUP BY bin_nmbr, facil_nmbr ) AS x inner join bin_inv_tbl AS f ON f.bin_nmbr = x.bin_nmbr AND f.facil_nmbr = x.facil_nmbr AND f.t_stamp = x.max_t_stamp WHERE f.facil_nmbr = {Root Container.Facilities.Facil_Name_ID}