inventory system & multiple locations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

inventory system & multiple locations

Author

Topic

bangingtunes
Starting Member

23 Posts

Posted - 2003-02-01 : 13:13:07

Can anyone provide any hints on modelling an inventory system that tracks stock quantity accross multiple locations.

I dont feel confident enough in my programming to keep a stock figure acurrate over time accross my whole app, so i need the ability to recalculate the stock figure at any time based on whats been purchased sold etc (from different locations).

If anyone knows any pages etc that tackle this please let me know.

Thanks

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-01 : 22:15:19

Do you need to keep track of each transaction, or only a quantity value for each product/location combination or both.

Location Table

LocationID
...And all other columns that would be unique to this locationid

Product Table
ProductID
....Info unique to this product

Inventory Table
LocationID PK FK To Location Table
ProductID PK FK To Location Table
Quantity
PricePerUnit
Discount
etc...

Sales Table
SalesID PK
SalesDate
..Any other columns unique to the whole sale

Notice LocationID can be moved to Sales Table if you only sell items from one location in each sale. If you sell items from multiple locations on the same sale keep it in the salesitems.

SalesItems Table
SalesItemID PK
SalesID FK To Sales Table
LocationID FK To Location Table
ProductID FK To Product Table
QuantitySold
PricePerUnit
PriceChargedPerUnit

Purchases
PurchaseID PK
PurchaseDate
.. Any other columns unique to this vendor purchase

PurchasesItems
PurchaseItemID PK
PurchaseID FK To Purchase Table
LocationID FK To Location Table
ProductID FK To Product Table
QuantityPurchased
PricePerUnit
PricePaidPerUnit

To update inventory run a query that sums sales - purchases and update the inventory table. This keeps track of every sale, every purchase and inventory based on location.

It's a start.
Now you must get more specific and focus your question and we can massage the basic design to fit your needs

bangingtunes
Starting Member

23 Posts

Posted - 2003-02-03 : 11:45:29

Thanks a lot for your answer ValterBorges.

What you have stated below is similar to what i was expecting. Youve given me enough info to be confident im going about things the right way. Which is what i was really after i suppose.

Thanks

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-02-03 : 16:01:04

quote:

Thanks a lot for your answer ValterBorges.

What you have stated below is similar to what i was expecting. Youve given me enough info to be confident im going about things the right way. Which is what i was really after i suppose.

Thanks

In addition to the suggestion above I would have an Inventory Movements table. I would put every movement in this with datetime, ref to SalesID, PurchaseID etc and a transaction type. Your main transaction types are normally:-

Purchase Receipts,
Inventory Transfers (between locations)
Inventory Adjustments (e.g Inventory write-offs)
Sales

If you manufacture something then this list grows with raw materials being used for Work in Progress etc.

You would program your app to insert a new row in this table each time you do any kind of inventory movement. You then have one place which gives you a complete overview of movements for any given item so if you have a problem with accuracy you can easily see what happened.

You would need to think carefully about if you really want to calculate how much stock you should have. To do this you would need to have some starting point which is accurate and then add and subtract from that. If you have all the transactions you need to calculate it, then why couldn't you have done it along the way? If your stock isn't accurate in real-time how would you prevenmt something being sold that wasn't actually on hand?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-03 : 17:22:06

quote:

If your stock isn't accurate in real-time how would you prevenmt something being sold that wasn't actually on hand?

I agree,

If your're records don't show a transaction taking place because of a glitch in the system then the only way you would find out the system needs adjustment is when the item is ordered and someone finds out there are none left or to do a physical count.

If it's a high price item or the combined quantity missing is cutting into your revenue stream and it's worth spending money on investigation then you pull out all the purchase orders, receipts, and computer records and you try to find the culprit to rule out data entry errors or lack of data entry.

If nothing comes up and it continues to happen then secretly place camera, do period inspections, and watch lots of hours of boring tape until you find the mouse that's eating all your gaskets.

Edited by - ValterBorges on 02/03/2003 17:25:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources