[Semibug] Keeping Track of Inventory with OpenBSD?

[Ron / BCLUG]

Jonathan Drews wrote on 2024-03-23 20:00:

> I need advice on how to keep track of inventory.

A *lot* of thought must go into the structure of the data you'll need to 
store.


Let's start with basics, the SKU (Stock Keeping Unit):

* id
* description (i.e. green silk)
* location (in a 400,000+ sq ft warehouse this was critical, maybe not 
for you but it's still *highly* recommended)
* vendor_id (links to a table of vendors)
* quantity (yards? rolls? shipping unit qty? minimum purchase qty?)
* cost (to you)
* markup

If there are 2 rolls of green silk - 1½ yards and 25 yards - how will 
that be stored? One entry of 26½ yards, or two entries? If 2 entries, 
and there *cannot* be duplicates in the SKU table, then there needs to 
be an intermediary table with items in stock (inventory table).


If a location is stored, every "bin" of every shelf needs an ID and 
there needs to be a table to store these.


If the 25 yard roll is in one bin, and the 1½ yard roll of the same SKU 
is in the "odds & ends" bin, ... you'd need to be able to find them both.


When someone purchases 10 yards of green silk, there'll need to be an 
orders table to store that in, and when that happens, 10 yards will need 
to be removed from the "green silk" inventory.

If it's 1½ yards from the "odds & ends" bin and 8½ yards from the main 
roll, the system needs to reflect that.


So, tables required:

skus
vendors
bins
orders
order_items (all the items per order)
inventory (for each SKU, what's quantity on hand?)
customers
...


Of course, procedures would be a nice way of automating things like a 
purchase:

START TRANSACTION
newOrderID = INSERT INTO orders
newOrderItemID = INSERT INTO order_items
	VALUES (
		order_id = newOrderID,
		item = $sku.description,
		qty = $x
		cost = $qty * ($sku.cost * $sku.markup))
UPDATE inventory SET qty = qty - newOrderItemID.qty WHERE sku = $sku

... (repeat above for each item on a purchase)

UPDATE ORDER SET
	customer = ... WHERE order_id = newOrderID,
	total =
		SELECT SUM(cost)
		FROM order_items
		WHERE order_item = newOrderID
COMMIT



If that were a procedure, would it be manageable invoking it with the 
relevant parameters in the MySQL CLI every time the database gets touched?


Not even mentioned - foreign keys & constraints (i.e. can't have an SKU 
without a vendor attached - ever). Nor an order_item without an order_id,...


Probably want to print out labels for each item in stock and maybe for 
each bin (section of shelving).

Pro tip: each bin's ID ought to reflect aisle, shelving segment, and shelf.


There's just a tiny bit of to consider.


/source: worked with multiple Warehouse Management Systems, in warehouse 
environments over 400,000 square feet.