From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | Frank Millman <frank(at)chagford(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Locking question |
Date: | 2016-10-26 08:46:56 |
Message-ID: | 20161026084656.23q5u6lkxfyde5vj@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Millman | 2016-10-26 10:18:19 | Re: Locking question |
Previous Message | Frank Millman | 2016-10-26 08:42:29 | Locking question |