Re: maintaining on-hand balances in an inventory
From: Eric Sabine (mopar41_at_hyotyt_mail_nounderscores.com)
Date: 08/04/04
- Next message: Yaheya Quazi: "Strange Insert problem"
- Previous message: B. Gates: "RE: SQL Mail and Attachment size"
- In reply to: Bob: "maintaining on-hand balances in an inventory"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 4 Aug 2004 16:42:21 -0400
Here's an idea.
You can drop the trigger idea and drop the inventory level from the item
master. I believe keeping the quantity on hand in the item master is normal
form violation anyway. What I mean is the item master is for data about the
parts themselves, like attributes. Quantity on hand isn't an attribute like
weight, length, or color is.
Depending on how much data you have from the past, i.e., years and years of
data, you could go about this in 2 ways.
Way 1)
create an indexed view
let's assume that your transactions table is as follows
create table Inventory_Transactions (part_id int not null, Transaction_Type
int not null constraint fk_transaction_types foreign key references
transaction_types( t_type), quantity decimal (19, 9) not null,
transaction_stamp datetime constraint df_t_stamp default current_timestamp,
...)
a few rows could be
123, 1 (where 1 = starting balance), 500 (the day you opened for business
you had 500 pieces), 1/1/2004, ...
123, 5 (purchase order receipt), 1000, 1/2/2004, ...
123, 6 (shipment), -600, 12/1/2004
123, 8 (conducted an "inventory" on your warehouse), -4 (discovered that 4
pieces were missing), 12/31/2004, ...
so you have how many pieces at year end of 2004? 500+1000-600-4 ... 896
pieces on hand.
as it is today, for every transaction on this table, you have a trigger
handling inserts, updates, and deletes.
instead you could create an indexed view as follows
create view iv_quantityOnHand with schemabinding
as
select part_id, sum(quantity) as running_total, count_big(*) as cb
from dbo.Inventory_Transactions
group by part_id
go
then, you only need to query the above indexed view to gain the inventory
levels. If you're concerned about the amount of data over time in the
transactions table, you could add another view called
"yearly_opening_balance" which is populated at the end of each year with the
prior year's closing balance. Then you could truncate the transactions
table at the start of each (fiscal) year.
Then you create a new query to FULL join the indexed view's output with the
opening_balance table's data to get the current values of inventory.
just a thought.
hth
Eric
Bob wrote:
> I'm strongly considering changing a stock inventory system's method of
> keeping on-hand balances. There are two tables involved, an item
> master and an inventory transactions log. Inventory levels are
> maintained in a column in the item master with insert triggers on the
> transactions table by summing all transactions for any affected
> part(s). Dating back quite a while I can see starting balances are
> the first transactions. While this system has worked for years, I'm
> pretty sure there's a better way of doing it. Rather than reinventing
> the wheel, I ask here: can someone point me to some docs, clue me in
> on standard practices?
>
> Thanks in advance,
> Bob
- Next message: Yaheya Quazi: "Strange Insert problem"
- Previous message: B. Gates: "RE: SQL Mail and Attachment size"
- In reply to: Bob: "maintaining on-hand balances in an inventory"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
