Thread: Storing Stock Levels / Calculated Fields

I'm working on a project at the moment that will require storing of stock levels for individual products. The slight complication is that the products are chemicals and are stored in bulk and pre-weighed amounts (no, they're not illegal drugs!)

If I used table salt as an example, that might help to demonstrate. Each time you produce a batch of salt you get a yield (say the first batch produces 100g). You might sell the salt in amounts of 1g and 5g.

From the first batch, you pre-weigh 10 lots of 1g and 2 lots of 5g. So, the pre-weighed stock is 10 lots of 1g and 2 lots of 5g and your bulk stock is now 80g. As you sell the pre-weighed, you make up more and more pre-weighed packs until your bulk is down to 20g. So you make some more and this batch yields 110g. So your bulk is now 130g. You carry on transferring bulk to pre-weigh.

Each product is stored in the db and against that there is a weights, currency and price table for pre-weighed.

What will also be stored is the product yield per batch and as each product has pre-weighed packs produced. The weighed amounts are very precise and in much smaller sizes than the example I've given, hence the need for accuracy.

So, I think, in theory, my current bulk stock could be calculated by taking the sum of all the batch yields - the total of the pre-weighed packs made up. The pre-weighed stock could be calculated by taking the total packs pre-weighed - the sum of all those dispatched.

But then would this gradually get slower and slower as the number of items included in the sum grows and grows?

The other slight complication is that customers may ask for product from a specific batch (if they've had product from that batch before) so I can't really just bulk all the batches of each product in together. I need to have the ability to break it down by stock of each individual batch.

Would I be better just storing the current stock level of each batch in a seperate table / field and then summing them all up to get by product? Even though, in theory, I'm storing a calculated field.

Or any ideas on the best way to handle this?

I'm not sure how many items we're talking about here, but am assuming it could be quite a lot. Having said that, you can increase performance of the SUM function by adding an index on the relevant columns.

I, like many others, know that sometimes it is necessary to store calculated values. It's often frowned upon, but sometimes necessary. If you're running this query once per page load, I don't think it would be a problem. And given the precision needed, I most likely wouldn't choose to store it as a calculated field.

Well, not that many to start with, but in a few years it could be lots. I'm thinking of a product list page where it would be calculating for several products. Just wonder if this might cause problems and make it slow?

I'm also thinking calculating the values rather than storing, just to ensure no errors can creep in. Most products are sold in milli and micro grams and are weighed to 3 dps. Just want to make sure I get the calculation right in my head!!

Also, not starting from scratch so there are already stock levels of both bulk and pre-weighed.

Another (another!) couple of complications is that they have two offices storing stock and also have onsite and offsite bulk storage. Jeez this is gonna get complicated!!

Ideally the two offices will run off the same DB, so am still wondering how to differentiate the two different stocks?

Originally Posted by richyrich

Well, not that many to start with, but in a few years it could be lots. I'm thinking of a product list page where it would be calculating for several products. Just wonder if this might cause problems and make it slow?

Post an example of how this report might look. It might be worth creating a quick script to populate the table with a LOT of sample data to accurately test the performance. Also, as I always say, implement some sort of data caching. The first page load might be quite slow, but subsequent page loads should be fairly quick when pulling from the cache.

Originally Posted by richyrich

Another (another!) couple of complications is that they have two offices storing stock and also have onsite and offsite bulk storage. Jeez this is gonna get complicated!!

Ideally the two offices will run off the same DB, so am still wondering how to differentiate the two different stocks?

This should be quite easy. You should have a table that stores all the different offices...in your case, will only be two. This table should have a PK called something like "OfficeID". You will use this ID field when entering stock items. This way, when you create your query to pull the data, you'll have a WHERE clause that would be something like "WHERE OfficeID = 1" to only return the stock items for that particular office.

I think it would be something like:-

Code:

Product Code              Batch               Bulk Stock                PreWeighed
                                                                         1mg   500ug

  Code1                    1234567                100mg                    2        3
                                              Onsite:70mg                US:1     US:1
                                              Offsite:30mg               UK:1     UK:2

  Code1                    1234568                50mg                     4        10
                                              Onsite:50mg                US:2     US:4
                                              Offsite:0mg                 UK:2     UK:6

  Code2                    7654321                20mg                      6        12
                                              Onsite:20mg                US:3     US:4
                                              Offsite:0mg                 UK:3     UK:8

  Code2                    8654321                300mg                   10       15
                                              Onsite:100mg              US:6      US:7
                                              Offsite:200mg              UK:4      UK:8

There are just over 300 products at the moment, I think. Each product can be sold in different weight amounts. So, 1 might be sold in 1mg and 500ug and another might be in 2mg and 1mg.

I'm thinking the additional info on stock levels (Onsite/Offsite, UK/US) can just be done by an async postback to a function to retrieve that info. So, that's not too much of a problem.

I can include an offices table. I'm just wondering how to relate that to the stock items though.

Everything is produced and pre-weighed in the UK. Then the US office just holds stock of the pre-weighs. Actually, I don't think that's a major issue, because each pre-weigh will be logged, so I can then just default it to UK and then as pre-weighs from each batch are sent to US, the distribution guys just have to mark off how many of each weight from each batch has been sent. I guess US will just have to confirm delivery, but that's not a problem.

Well I can see where the calculations would take place, but I'm not so convinced that it would be a big problem.

Originally Posted by richyrich

I can include an offices table. I'm just wondering how to relate that to the stock items though.

What is your current database structure? Can you upload a diagram?

I don't have a diagram. I think the basic structure you'd need is:-

Code:

tblproducts (Products)
ID
Name
Description
.
.

tblproductbatch (Bulk Stock)
ID
ProductID
Weight
ProducedBy
DtProduced
.
.

tblbatchweights (Pre-Weighs)
ID
BatchId
WeighType
Amount
ScaleId
ProducedBy
DtProduced
.
.

tblsysweightscales (Scale mg, ug etc.)
ID
ShortName
.
.

tblsysweightypes (Pre-weigh / Waste)
ID
WeighType

As a simplified example. You might start off with 100 of bulk stock to weigh out 5 lots of 10. But, your weighs might actually be:-
9.996
10.002
9.999
9.998
9.996

The bulk stock is then reweighed, which might be 50. So the difference between the sum of the 5 weigh-outs and the final bulk weight is waste, and is also logged (hence the weigh type).

What does ProducedBy represent?

Originally Posted by jmurrayhead

What does ProducedBy represent?

Just the userID that produces the batch or pre-weigh

Ok, and tblBulkStock is the entire stock available for both offices to be broken down to weights?