Designing inventory system

Hi

Good Day!

I am designing a database to manage an Inventory system. I have two tables in it - Receives and Issues with two common fields -ProductID and Quantity.

I need some facilities in the database -

# Current Stock - Two fields - ProductID & BalanceQty. I want to get the balance quantity for a given product.

# Product Ledger - I want to have a ledger of product transaction with Date, ReceiveQty, IssueQty, Balance.

How can I have these facilities? Should I use a SProc or View? I need some expert openion. Please give me some details example. I am very new in SQLServer.

Thnk you very much!

Regards

Kapalic

Your question isn't really very clear to me, but it is really down to what you are using the data for. I would say stored procedure if this is used in a user interface (particularly in a single location), or a view if you want to be able to access the data in multiple locations, or perhaps for ad-hoc access...

I am in favor of using stored procedures for all access to the database in your user interfaces to make things easier to maintain and use.|||

I agree with Louis. A stored procedure will be easier to maintain and easier to secure.

You definitely want the [CurrentStock].[BalanceQty] values to be dynamically calculated -and not static data stored in a table.

|||

You can use a stroed procedure where you can pass the ProductId & you can get the BalanceQty & other details - it should be handy

|||

Thnx guys for ur answers. Actually I couldn't make myself clear.

Can anyone please give me an example how to write these sprocs? I need to check if enough product exist in the receive table before issueing a product in the issue table.

Thnx!

|||

If you mean protecting against over promising stock, if the tables are in the same database, I would use two techniques:

1. Stored procedure to calculate available stock when the user asks for the list (like when a user of Amazon goes to the page of a product and it says they have 3 in stock

2. A trigger that prevents an order from being placed without stock being available.

The second one will be a bit, because you will likely want to let the user backorder a product, but your order item for a product should be clearly marked as that, so the user doesn't get stuck waiting forever. So the calculation of available stock must take into consideration:

1. Will this product be stocked again (status of the product)

2. It there stock to cover the order now, even if there was when the user chose to order it, if not reject the line item, unless the user has specifically said "okay to back order"

But I would use a trigger to make sure that the status of the line item matched the expectation of order.

|||

Thnx Buddys!

I've written a view to show all the product with current stocks (PurchaseQty - IssueQty - DamageQty). And a sproc to wrap the view.

But I can't get an way to show a ledger for individual products, like -

Date Product PurchaseQty IssueQty DamageQty BalanceQty

Regards

Kapalic

|||

Use a WHERE clause, passing in the ProductID -both in the stored procedure and the VIEW.

Very similar to the code we wrote for you last week.