Re: Locking question

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/