I can't answer for Bob, but the problem that I see, immediately, is a
performance hit that would be acceptable in small systems, but would
bring a large system to its knees.

Bear in mind that my perception of system scaling is skewed - in one
exchange not too long ago we discovered that what I was calling a
"small" system was ten times the size of what the other person was
calling a large system.

Given a scenario with 60,000 items (an actual inventory that I am
currently working with), and worst case scenario of daily pricing
changes, by the end of the year you would need to retrieve 365 rows and
perform computations on them for every price.

Now if I hit that database with 1,000 users, each looking for a current
price on a single part, and I have been operating for a year, then my
spike load computations is that I need to retrieve 365,000 rows. Now
scale this up even further to Walmart's size. I can guarantee that they
have many times the 60,000 items that I have in my little inventory
system.

The second issue that this introduces, regardless of scaling, is a
potential for tampering, or more specifically it makes tampering harder
to detect. The Sabanes Oxley (SOX) laws, drafted in the wake of the
Enron scandal, dictates standards for many sensitive financial processes
in the US. Inventory is one such process. Recording only differences
makes tampering harder to detect because a manual auditor would need to
calculate daily values and relate them back to the submitted price that
is recorded elsewhere.

Google Barings Bank for details of how serious this can be. Nick Leeson
undertook risky investments and tampered with his company's IT system to
hide losses from his bosses. Over a few years, he single handedly wiped
out a respected investment bank. Financial data needs to be tamper
resistant, and the database structure needs to be able to expose
tampering.

Keeping a running log with real prices keeps the retrieved row count
stable (1 or 2) regardless of the size of the database, and with correct
use of indeces the IO count will be remarkably stable over time. A
running log is auditable because a man with a highlighter can print a
report, obtain a faxed report from the vendor, and quickly identify any
tampering, without any computations.

On Sun, 2005-01-09 at 03:16, tigereye_philip wrote:
>
>
> Bob,
>
> Sorry to reply to myself (and from yahoo-groups, as it lets me see
> the reply tree,) but I don't think I ever saw a reason my solution to
> your problem wouldn't work, though it is incomplete -- you later
> mentioned you're getting notifications as price changes happen, so
> you'd need some mechanism/trigger to calculate and insert only the
> relative diff value, not the raw 'new' value. The sum/group_by works
> well for multiple SKU items at a time, too... I seem to remember that
> being a question you asked at some point.
>
> If only for my own education, is there some problem I'm not seeing
> related to my solution? Notifications being sent out of order, etc.?
>
> Thanks and good luck,
> -Philip
>
> --- In firebird-support@yahoogroups.com, "unordained"
> <unordained_00@c...> wrote:
> > This would be a great way to use the accounting method and
> do 'changes' at points in time.
> >
> > SKU PRICE_DATE PRICE_DIFF
> > --- ------------ ----------
> > 123 1-Dec-2004 1.50
> > 123 9-Dec-2004 2.00
> > 123 7-Jan-2005 -1.25
> >
> > Then you can do the following:
> >
> > select
> > i.sku, i.receipt_date, sum(s.price_diff)
> > from
> > inventory i
> > join sku_pricing s on (s.sku = i.sku and s.price_date <=
> > i.receipt_date)
> > where
> > i.sku = 123
> > group by i.sku, i.receipt_date
> >
> > At least I think that'd work ... but for our accounting system, it
> works wonders (except we do the
> > actual debit/credit columns ... don't ask me why.)
> >
> > -Philip
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>