Stock Levels

nzkiwi80

I've got a small e-commerce site, I have a products table but have a second stock table

Product:
ID: Name: Price

Stock:
ProductID : Order ID

Currently when I load stock, I load a row into that table with null in the order ID column.

Is this okay? Should I, or is it better to have 'stock' column in my product table and load a purchase into an orderitems table when purchase and decrease the stock column by the number purchased?

pbismad

For items that are purchased, you should have an order(s) table and an order_items table. The order id, item id and quantity of each item in each order goes in the order_items table.

Stock can go up or down for reasons other than stock received and purchases - loss, breakage, samples given out, replacement items sent, items returned that are usable and returned to stock, ... These should be recorded in the stock table, along with stock received.

To get the current stock for any item id(s), you would get the SUM() of the +/- quantity in the stock table and UNION this with negative SUM() the quantity from the order_items table.

Example query -

select item_id, sum(qty) qty from (
 select item_id, sum(quantity) qty from stock WHERE item_id IN(1,2) group by item_id
 union all
 select item_id, -sum(quantity) qty from order_items WHERE item_id IN(1,2) group by item_id
) x group by item_id

nzkiwi80

Thanks for the reply, based on your example. What's your stock table structure look like?

pbismad

You need all the who, what, when, where, and why information about each stock transaction -

id (autoincrement primary index)
item_id
quantity
datetime
type_id (transaction type/reason)
user_id (id of the user who inserted the record)
memo/comment (user entered text)
order_id (when transaction is related to an order)

nzkiwi80

pbismad

Thanks, what would datetime be for? likewise typeId?

Weedpacket

nzkiwi80 Thanks, what would datetime be for?

My guess would be that it's when the transaction took place.

nzkiwi80

Weedpacket

Yes, so we've got a row for each instance of a product on the stock table and I update that when a transaction happens then load it into a order_items tables with the same thing. Is that the best way?

pbismad

nzkiwi80 I update that

There's no updating involved in this. You always insert new row(s) of data to record each (unit) piece of information in a database. This provides an audit trail so that you can determine if a programming mistake, duplicate submission, or nefarious activity has altered an amount. Once the raw data has been recorded, you simply query to get any result or report based on that data.

You should have a product/item table. This is where the items are defined. The id column in this table is the item id that is used when storing data related to the items.

When someone selects items for an order and goes through the checkout process, one row is inserted into the order(s) table with the unique/one-time information about the order. The id column in this table is the order id that is used when storing data related to the order. A row for each item id in the order is inserted into the order_items table.

The stock table holds a separate row for each transaction that increases or decreases stock for each item id, other than what is recorded in the order_items table. If you order and receive stock of an item, you insert a row into the stock table, with a positive quantity, every time you receive stock. If any other type of event occurs that increases or decreases stock of an item - loss, breakage, samples given out, replacement items sent, items returned that are usable and returned to stock, ... a separate row of data is inserted into the stock table, every time one of these events occurs.

To get the current quantity for any item id(s), you would use the query I already posted.

Weedpacket

Are we talking about stock items or order items? They're not the same thing.

nzkiwi80

pbismad

Thanks, so if I had a product 'Blue Jeans', it'd have that in my product table. I'd add a row to my stock table with a qnty of 10 for example.

If I had no orders for that item, it'd show 10 available. If my order_items table had the product ID in it for 1 order it'd show 9? is that right?

sneakyimp

nzkiwi80 Thanks, so if I had a product 'Blue Jeans', it'd have that in my product table. I'd add a row to my stock table with a qnty of 10 for example.

You should probably have one record in your stock table for each option e.g., "slim fit", possibly different sizes, etc.

nzkiwi80 If I had no orders for that item, it'd show 10 available. If my order_items table had the product ID in it for 1 order it'd show 9? is that right?

This would require that you always join the stock table to the orders table to get a count of your current inventory. I don't think that's a very good idea. As someone who sells various music-related merch, it has been my experience that there are many reasons to reduce your stock/inventory. E.g., I gave away a bunch of LPs for promotional reasons. Some merch gets broken or damaged. Etc. I might consider maintaining the current count of inventory by updating your stock table when you part with an item for any reason. You could decrement this value as each order is submitted. You might also occasionally count your inventory and simply update it.

nzkiwi80

I've acturally got my head around what you're saying.

My questions are, how can I retrieve a list of products or one specific product based on this?

Currently my code has a HAVING clause

Secondly what is the IN(1,2) referencing ?

Lately, why is there no FROM table? Might link to the first question


select productId, sum(qty) qty from (
 select productId , sum(quantity) qty from stock WHERE productId IN(1,2) group by productId
 union all
 select productId, -sum(quantity) qty from order_items WHERE productId IN(1,2) group by productId
) x group by productId

Weedpacket

1 and 2 are example productId values. the inner selects are selecting records with those product IDs.
The inner SELECTs are selecting from the stock and order_items tables and the results are being combined (using union all) into a single relation that the outer SELECT is selecting rows from. (That concept of a relation is what a relational database is all about, which is why it's called that.)

nzkiwi80

Weedpacket I've not seen a query like this, Where would I select or join to my product tables so I could query conditional of name or price etc?

Weedpacket

sneakyimp You should probably have one record in your stock table for each option e.g., "slim fit", possibly different sizes, etc.

I expect each distinct item/variation would have its own SKU that uniquely identifies it; the products table would have a (single) distinct row for each SKU (and, as recognising the role of the SKU, another column in the table would be the number of units in stock).

sneakyimp

Weedpacket expect each distinct item/variation would have its own SKU that uniquely identifies it; the products table would have a (single) distinct row for each SKU (and, as recognising the role of the SKU, another column in the table would be the number of units in stock).

If your products table has a lengthy description for some fancy pair of jeans, let's say, it would be a shame to repeat that lengthy description in all the different rows for each waist size / length combination for those jeans.

Also do you mean to suggest that two different sizes of a particular pair of trousers would have distinct SKUs? It certainly makes sense to count the sizes separately -- you wouldn't want to continue selling a pant size you no longer had in stock.

Weedpacket

sneakyimp Also do you mean to suggest that two different sizes of a particular pair of trousers would have distinct SKUs?

That is the convention, yes. It might have a base component that is common to all variations, prefixed/suffixed if and where necessary to distinguish variants, there might be some clever encoding so that if you know your SKUs you can immediately see that "MMT56G" is a "Male Mannequin Torso, gloss finish", or it might just be a flat serial number; all that's up to whoever is keeping the stock since it's they who will be making and using the SKUs. But they want to be distinct so that, as you note, you don't claim to have size 32 trousers in stock only to discover you have nothing above size 28.

pbismad

nzkiwi80

You would do that in the outer/main query -

select i.name, item_id, sum(qty) qty from (
 select item_id, sum(quantity) qty from stock WHERE item_id IN(1,2) group by item_id
 union all
 select item_id, -sum(quantity) qty from order_items WHERE item_id IN(1,2) group by item_id
) x
JOIN items i ON x.item_id = i.id
group by x.item_id

Weedpacket

nzkiwi80 On the relations that have those properties (name and price would be properties of stock items, not order items (since items don't change name or unit price when sold)).

nzkiwi80

Coming back to this theory. If I had produce ID 1 with one item in the stock table. When an order is made I add a -1 to the stock table and one to the order items rows.

if I was to check available levels I’d have -1

when I cancel the order I add one to the stock table I’m back at 0, if I don’t want to cancel my order items for reference I’m stuck unless I add another to the stock table.

why do I need to union the order items table? Can’t I just do all the math in the stock table?

pbismad

nzkiwi80

You don't record the same piece of data in multiple places.

If someone places an order, it is only recorded in the order items table. For your example, product id 1, quantity 1. The UNION query SUMs the quantity in the stock table and subtracts the SUM of the quantity in the order items table. For your example, stock (1) - order items (1) = 0.

Canceling an order or canceling one or more item(s) in an order should involve setting a status value in the order items table that would exclude those rows from the UNION query, by adding a condition in the WHERE clause, e.g. status != 'canceled'. For your example, stock (1) - order items (0) = 1.

The suggestion to use a separate order items table and a stock table is because the rest of the data being recorded in each row in these tables is different. These are also two different contexts. A positive quantity ordered, is a reduction in stock and a positive quantity put into stock, increases available stock. By trying to use a single table, you will have unused columns and must deal with when is a quantity treated as positive and when is it treated as negative.