--- In firebird-support@yahoogroups.com, "albr >

> However surely the use of a Trigger to perform the inventory update is
> "correct", as is the updating of transactions as they occur (not
> batched up at the end of a sale) so how should Nick structure this
> "for a multiuser environment"?

You are talking about a specification that lacks internal consistency.
I can offer limited assistance to Nick because I don't understand his
environment, but I can certainly make observations about bottlenecks
in the design as he describes it.

There is nothing incorrect about any individual aspect. Yes, his
design will give him the right numbers. The requirement to update as
they occur is something you have inferred, but never explicitly stated.

That bottleneck is this: that anytime someone updates this movement
table, it is going to effectively lock out other simultaneous
transactions from updating the records pointing to the same item. You
can not have your cake and eat it. If you want to update a single
record in a master table every time a detail table is modified, then
only one transaction can modify any of the detail records for a
particular master record at a time. In a transactional database, this
"lock" must wait to see whether the transaction commits or rolls back
before it can be released. In Firebird's case, it only prevents
updates and deletes, not reads. When you have a situation where
multiple records get locked and where the transaction is running for a
measurable time, your transaction are going up a proverbial creek. So
his design will work, but will not scale to multi-users.

As per the correct design, well it really depends on the requirements.
A multi site supermarket style inventory system will have a very
different requirement to a specialty store with two registers.

If you want to consider the supermarket model, then obviously it is
not going to be appropriate to prevent anyone at a given store buying
milk until the person with a basket full of groceries finalises their
payments. There are a number of different options.

Once could updating each item in its own transaction, minimising the
time the lock is in place and making retry logic practical. If the
sale was aborted, then there would need to be a reversal process that
your software would need to be capable of managing.

Another option is to not worry at all about maintaining the inventory
numbers until after the human interactions are finished and the sale
finalised. Have a centralised service that every 15 seconds queries
for new completed sales and updates the relevant inventory items for
that sale and marks the sale as having updated the inventory counts.
Being centralised, you wouldn't need to worry about concurrent sales
as they would all be serialised, and lets face it, an automatic
reordering system is hardly going to care if it takes an additional 15
seconds to be told that a given stock is too low.

There a possibly other solutions available to, but you would need to
consider the requirements.

Adam