Initial Stock vs UnitsInStock

[weifuk]

Hi All,

I developed an inventory system, with a product table which records quantity in stock.

However, my friends told me yesterday that it is better to store InitialStock, and make a query to view available stock instead.

Which is best? What are the pros and cons?

Thank you!

[szlamany]

Hi All,

I developed an inventory system, with a product table which records quantity in stock.

However, my friends told me yesterday that it is better to store InitialStock, and make a query to view available stock instead.

Which is best? What are the pros and cons?

Thank you!

I would personally create a DEBIT/CREDIT type table.

The first entry for an item would be a CREDIT entry (type code="C") and have the initial stock amount as the value.

It would structure the table something like:

ITEM CODE
TRANSACTION DATE
TRANSACTION TYPE (C or D)
AMOUNT

You could always add a TRANSACTION SEQ if you expect more than one ITEM CODE/TRANSACTION DATE activity to occur on the same day.

Each time stock is reduced, a TYPE=D entry is loaded.

[weifuk]

I would personally create a DEBIT/CREDIT type table.

The first entry for an item would be a CREDIT entry (type code="C") and have the initial stock amount as the value.

It would structure the table something like:

ITEM CODE
TRANSACTION DATE
TRANSACTION TYPE (C or D)
AMOUNT

You could always add a TRANSACTION SEQ if you expect more than one ITEM CODE/TRANSACTION DATE activity to occur on the same day.

Each time stock is reduced, a TYPE=D entry is loaded.

Szlamany,

Thanks for the fast reply. Good idea, however I am confused with what you said.

I picture 2 ways from what you said:

1. Product: ProductID, ProductName, UnitsInStock
SalesDetail: ProductID, Quantity
Transaction: ProductID, TransactionType, TransactionDate, Amount

Everytime there is a new record in SalesDetail, it is directly updated to the transaction table(debit). And also Product.UnitsInStock = Product.UnitsInStock - Transaction.Amount.

Everytime there is a transaction (either debit or credit), the amount of the transaction is directly updated to the UnitInStock field in Product table.


or is it:

2. Product: ProductID, ProductName, InitialStock
SalesDetail: ProductID, Quantity
Transaction: ProductID, TransactionType, TransactionDate, Amount

Everytime there is a new record in SalesDetail, it is directly updated to the transaction table(debit). But the Product.InitialStock is not updated, until we told to, say it is updated in the beginning of a new accounting month/year?

Thank you

[szlamany]

More like:

PRODUCT: PRODUCT ID, PRODUCT NAME

Sales Detail and Transaction are one table.

TRANSACTION: PRODUCT ID, TRANS TYPE, TRANS DATE, QUANTITY

The "earliest" entry with a C in the TRANSACTION table is the INITIAL STOCK.

All purchases are D entries that eat away at the stock.

All re-orders are subsequent C entries in TRANSACTION table for re-stocking amounts.

At any time you can find the "first" entry and know the initial stock, or entries within a date range to find stock out and stock in figures.

If you need to, you could throw an additional field in TRANSACTION for TRANSACTION CODE...

1 - initial stock
2 - re-stock
5 - purchase
6 - lost in flood
7 - lost in stock count

[erickwidya]

weifuk..i agree with ur first option..the 2nd is not good since u don't know 'live' quantity of ur Product, i'm using like ur first option now but since here the prog not use for Accounting stuff then there's no InitialStock

buy szlamany sure give other option to make one table for Sales Detail and Transaction..and looks good one too

[weifuk]

weifuk..i agree with ur first option..the 2nd is not good since u don't know 'live' quantity of ur Product, i'm using like ur first option now but since here the prog not use for Accounting stuff then there's no InitialStock

buy szlamany sure give other option to make one table for Sales Detail and Transaction..and looks good one too

Actually, I was with you before Erick, but later I find myself more comfortable with the second option, so I put the initial stock in the product table, and run query from the database to find the 'live' quantity of product stock, for example run sum query from Sales, Purchase, Return goods, etc. It's very comfortable not to write codes everytime I want to update stocks. Updating another table through code more susceptible to error than let the database handles the update itself.

More like:

PRODUCT: PRODUCT ID, PRODUCT NAME

Sales Detail and Transaction are one table.

TRANSACTION: PRODUCT ID, TRANS TYPE, TRANS DATE, QUANTITY

The "earliest" entry with a C in the TRANSACTION table is the INITIAL STOCK.

All purchases are D entries that eat away at the stock.

All re-orders are subsequent C entries in TRANSACTION table for re-stocking amounts.

At any time you can find the "first" entry and know the initial stock, or entries within a date range to find stock out and stock in figures.

If you need to, you could throw an additional field in TRANSACTION for TRANSACTION CODE...

1 - initial stock
2 - re-stock
5 - purchase
6 - lost in flood
7 - lost in stock count

What if the database are more complex to those described above. Suppose

Purchase: PurchaseID, Date, SupplierID, ShipDate, ShipOption, Payment, etc.
PurchaseDetail: PurchaseDetailID, PurchaseID, ProductID, Qty, Price, etc.
Sales: SalesID, Date, CustomerID, Shipdate, ShipOption, Payment, etc.
SalesDetail: SalesDetailID, SalesID, ProductID, Qty, Price, Discount, etc.

and more debit credit transaction.

I assume that SalesDetail and Transaction is not in one table, where would we want to put other fields in SalesDetail table or PurchaseDetail table? If they are, then the dbase is not normal?

It is very easy with Transaction table to keep trace of each transaction records, but in my opinion it is double job (assume salesDetail and transaction are not in the same table, each time there is a transaction, we have to update both salesdetail and transaction everytime)

please correct me if im wrong

[szlamany]

Actually, I was with you before Erick, but later I find myself more comfortable with the second option, so I put the initial stock in the product table, and run query from the database to find the 'live' quantity of product stock, for example run sum query from Sales, Purchase, Return goods, etc. It's very comfortable not to write codes everytime I want to update stocks. Updating another table through code more susceptible to error than let the database handles the update itself.




What if the database are more complex to those described above. Suppose

Purchase: PurchaseID, Date, SupplierID, ShipDate, ShipOption, Payment, etc.
PurchaseDetail: PurchaseDetailID, PurchaseID, ProductID, Qty, Price, etc.
Sales: SalesID, Date, CustomerID, Shipdate, ShipOption, Payment, etc.
SalesDetail: SalesDetailID, SalesID, ProductID, Qty, Price, Discount, etc.

and more debit credit transaction.

I assume that SalesDetail and Transaction is not in one table, where would we want to put other fields in SalesDetail table or PurchaseDetail table? If they are, then the dbase is not normal?

It is very easy with Transaction table to keep trace of each transaction records, but in my opinion it is double job (assume salesDetail and transaction are not in the same table, each time there is a transaction, we have to update both salesdetail and transaction everytime)

please correct me if im wrong

The whole point of the TRANSACTION TABLE is to have just a single place for all stock figures - initial stock, re-order, purchase - everything.

If additional info is required for the SALES DETAIL, then have that info in a SALES DETAIL table and key it to relate to the TRANSACTION table.

If the "main drive" of the system is to track stock, then having the stock in a single column of a single table should be your first priority. That is my opinion.

We have an accounting package that does invoices, purchase orders, all kinds of transactions - budgets - etc. It's all surrounding a main ledger table that has the DEBITAMT, CREDITAMT and ENCUMBEREDAMT fields in it. Other tables hang off this for additional INVOICE details and PURCHASE order details.

The ease of knowing that all financial figures come from a single table - three columns in that table - is great. I've been developing accounting packages since 1980 - and I've used many different designs.

[erickwidya]

so I put the initial stock in the product table, and run query from the database to find the 'live' quantity of product stock, for example run sum query from Sales, Purchase, Return goods, etc.

weifuk..so u saying everytime u want to know quantity in hand u run query against those tables? hm..not efective i think but again that only my personal opinion but i'm learning here

The ease of knowing that all financial figures come from a single table - three columns in that table - is great

szlamany, not quite understand..

[weifuk]

weifuk..so u saying everytime u want to know quantity in hand u run query against those tables? hm..not efective i think but again that only my personal opinion but i'm learning here

szlamany, not quite understand..

i think otherwise, it's very effective not to write codes, and omits chances of making mistakes from codes. may i know, why do you think it's not effective? hmmm maybe the only "not effectiveness" is everytime we want the quantity on hand stock, it has to recalculate from the beginning transaction, but again it's not done from our code...

If the "main drive" of the system is to track stock, then having the stock in a single column of a single table should be your first priority. That is my opinion.

what szlamany gives here is good i think. It is used to track stocks from one table.

the table probably are something like:
Item Item Number, Description, manufacturer, etc)
----Stock (Item Number, Warehouse, quantity on hand, etc)
--------Transactions (Item Number, Transaction Type, Transaction Quantity, etc)

it is the child of product table, is it right szlamany?

[erickwidya]

hmmm maybe the only "not effectiveness" is everytime we want the quantity on hand stock, it has to recalculate from the beginning transaction, but again it's not done from our code

yes..that's what i'm thinking about..have u consider if ur table has grow..it'll took sometime to calculate is just to know the stock in hand right?
the code isn't do the calculate stuff..what i mean is ur code will update the stock in hand for each transaction that involved with it..so ur code finally just 'grab' the stock in hand to display..

[dglienna]

add another field, to show QOH (Quantity On Hand)

[szlamany]

One of the hardest things for people to do with SQL is loose the old "sequential" processing concepts of programming.

Never store a total - never store a calculated value. That is 1980-mainframe programming.

SQL is designed, with subqueries and other concepts, to be able to strike a freshly calculated total in milliseconds.

We have an accounting package with 100,000 rows added per fiscal year. We have 10 fiscal years in the table now. We can figure out a function or department balance or budget from the detail records of the ledger table instantly.

Back in the 1980's we stored those figures in an ACCOUNT table. We had "recalculate" and "fix" programs that had to run regularly to correct issues.

SQL is all about loosing those old-hat concepts.

We have medical info tables with 3 million rows. We have hours contribution tables with 2 million rows. We use all these detail tables to calculate eligibility and benefit usage. All in SQL - all in STORED PROCEDURES.

As soon as you store an INITIAL STOCK or a STOCK-on-HAND value, you have slipped out of the true SQL realm. Forget about normal forms - that's simply not SET-BASED logic.

[erickwidya]

szlamany thx again..