Personally, I like to make the database responsible for the integrity of the data within it as much as possible. And therefore would favour Depsesz's solution to trying to manage it within the application.
Cheers
Gary
On Wed, Oct 26, 2016 at 8:18 PM, Frank Millman <frank@chagford.com> wrote:
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.