|
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. > > [...] >
> 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. > Thanks,
depesz
I
can see how that would work, but I have two comments. 1.
I am writing my application to support 3 databases – PostgreSQL, sqlite3, and MS
SQL Server. Because they are all so different when it comes to triggers and
procedures, I am trying to avoid using them, and do as much within the
application as possible. 2.
I think you are suggesting maintaining a ‘balance’ column on inv_rec. This
raises the question of whether or when you should create and maintain a column
if the same information could be derived from other sources. I realise that this
is a judgement call, and sometimes I struggle to get the balance right. Is this
a situation where people would agree that it is warranted? I
would still appreciate some feedback as to whether my proposed solution would
work. Thanks Frank |
