CD Baby database approach to inventory and stock

by Derek Sivers

Related link: http://www.cdbaby.com/



CD Baby only has one warehouse today. But by the end of the year, we'll have multiple warehouses.

I used to count current stock/inventory by using a simple database table called inventory that really only counted items_received, then subtracting the quantity of items sold from the order's lineitems table.

But with multiple warehouses, this won't work anymore! Each warehouse needs to know how much its own current stock is. Unless I were to actually tie an order's lineitem with the warehouse_id it came from, I would need a new approach.

So - the new approach is this:

INVENTORY is a detailed historical trace of everything in and out of the warehouse.

STOCK is a current-status : how many of each item are in each warehouse right now. Used for quick lookups.

## THE DATABASE TABLES:
INVENTORY:
id | warehouse_id | item_id | quantity_in | quantity_out | created_at | person | shipment_id | notes

STOCK:
id | warehouse_id | item_id | stock_status_id | quantity

(SIDE NOTE: Stock_status_id is just something we can override by hand : if a musician tells us their CD won't be arriving for 3 more months, we'll set it to a DELAYED status. If it's permanently out of stock, we'll set it to PERMANENT status. Etc.)

Every time something adjusts the inventory, the stock table needs to be updated. So I decided to try my first PostgreSQL trigger. (See the PostgreSQL manual on PL/pgSQL).


CREATE OR REPLACE FUNCTION update_stock() RETURNS trigger AS '
DECLARE
w integer;
i integer;
instock integer;
BEGIN
IF TG_OP = ''DELETE'' THEN
w := OLD.warehouse_id;
i := OLD.item_id;
ELSE
w := NEW.warehouse_id;
i := NEW.item_id;
END IF;
SELECT INTO instock (COALESCE(SUM(quantity_in), 0) - COALESCE(SUM(quantity_out), 0)) FROM inventory WHERE item_id=i AND warehouse_id=w;
IF instock IS NULL THEN
DELETE FROM stock WHERE item_id=i AND warehouse_id=w;
ELSE
UPDATE stock SET quantity=instock WHERE item_id=i AND warehouse_id=w;
IF NOT FOUND THEN
INSERT INTO stock (warehouse_id, item_id, quantity) VALUES (w, i, instock);
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;

Everything before the first SELECT statement is just some basic setup stuff: you have to use the DECLARE section to first say what variables you're going to be using below.
Then I had to start my BEGIN section by changing how I got the warehouse_id and item_id based on whether I had just done a DELETE on the inventory table (use OLD), or an INSERT/UPDATE (use NEW).
Then the basic stuff begins:
Subtract the total-out from the total-in for this item for this warehouse.
If it's not found, then make sure stock table is not caching old data (DELETE).
Update the stock with the new current quantity.
Or if this is the first time, insert it.
That's it!

Then, to have the database use this function automatically, I just added this to my database definition:

CREATE TRIGGER stock_update AFTER INSERT OR UPDATE OR DELETE ON inventory FOR EACH ROW EXECUTE PROCEDURE update_stock();


Now any time I make ANY adjustment to a line the INVENTORY table (insert, update, delete), the STOCK table is instantly updated for that item in that warehouse. It's instant and wonderful.

Any improvements or suggestions are welcome


1 Comments

dereksivers
2005-04-29 08:59:45
Robert Treat's PostgreSQL feedback
Robert Treat's helpful PostgreSQL feedback on this function:


http://people.planetpostgresql.org/xzilla/index.php?p=13