On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote: > Hi all > > I am designing an inventory application, and I want to ensure that the stock level of any item cannot go negative. > > Instead of maintaining a running balance per item, I store the original quantities received in one table (call it ‘inv_rec’), and any amounts removed in another table (call it ‘inv_alloc’). > > CREATE TABLE inv_rec > (row_id SERIAL PRIMARY KEY, > product_id INT REFERENCES inv_products, > qty INT); > > CREATE TABLE inv_alloc > (row_id SERIAL PRIMARY KEY, > rec_id INT REFERENCES inv_rec, > qty INT); > > To get the balance of a particular item - > > SELECT SUM( > a.qty + COALESCE( > (SELECT SUM(b.qty) FROM inv_alloc b > WHERE b.rec_id = a.row_id), 0)) > FROM inv_rec a > WHERE a.product_id = 99; > > To remove a quantity from a particular item - > > INSERT INTO inv_alloc (rec_id, qty) > VALUES (23, -1); > Is this the correct approach, or am I missing something? What I would do, is to add trigger on inv_alloc, than when you insert/update/delete row there, it updates appropriate row in inv_rec by correct number. Then, I'd add check on inv_rec to make sure qty is never < 0. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general