Inventory control database help

[marcuscoker]

Hi

I am trying to develop an inventory control database and would really appreciate some advice on the way I propose to develop the application


Inventory Control Database.


Outline for inventory control database.

The database will have the following tables:

Supply Table

Person Table

Supplied Table

On hand table

Orders Table

The Supply Table - will store the names of the stock items available

The persons table - will basically hold information on the person receiving the product/supplies

The Supplied Table - will hold information on who is being supplied, what is being supplied, who is the supplier, how much is supplied and when.

The On Hand table - will initially store the opening stock for all the stock items available. When an individual is given some of that stock, the amount of stock given will be subtracted from the on hand total, for that particular stock item.

The Orders Table – Will be the place where we will record all orders received. Once an order is received, this figure will be added to the On Hand Total for that particular item. So ultimately the on hand table should reflect initially the opening stock figure, then the opening stock figure plus any amounts ordered. I anticipate that after the initial opening stock figure is entered onto the system, the on hand table will be automatically populated by the orders table, and the site user will only need to manually update the On hand table in case of emergency, for example if a mistake is made on the order table.


I would really appreciate some help with the table design logic here. This is the first time that I have developed a stock ordering system, and would appreciate any advice on the over all design.

Initially, I want to build this as an access application then convert it to a web application ASP/SQL Server

Thanks

Marcus

 

[Len Boorman]

This is a big project. Do not underestimate it.

Okay

So you have identified Stock Receipts but what if supplier only supplies a part quantity, How will you handle this and subsequent deliveries.

Stock Issues. need to consider these as well ?

What about Re Order. Do you intend to have a Re-Order point. Be careful because you will need to consider what is on order but not delivered.

I would suggest that you actually create the Enterprise Rules.

Actually write down exactly what you will handle within the application and most importantly what you will NOT handle.

Be pedantic and explicit because if you do not get the structure right it will be a dog to change later.

These Enterprise Rules will help identifying the Entities.

You will have I suggest to start with
Stock Items
Suppliers
Orders

Does not sound much but
Can a Part be Supplied by Many Suppliers
Can a Supplier supply Many different Parts
Part Order Delivery
Multi Line Orders
Prices (Same for situation where you have multiple suppliers ????
Stock Location ????
Transaction History ???
Cycle Counting for Stock Checking ????

A few things to consider and as you go through these no doubt a bunch more will pop out of the woodwork

Len

 

[marcuscoker]

hi

thanks for your suggestions, it has definitely given me some more ideas on how to move forwards.

I would be interested to find out if you agreed with the way I intended to keep a track of how mcuh stock is around, for example, when a supply item is distributed, the value in the onhand table is automatically replaced with the onhand value - the amount supplied

With regards to having more than one supplier to make up an order, that is not so much of an issue in this instance, but is something that I will definitely put to the project team.

Thanks again for your advice

Marcus

 

[Len Boorman]

The on hand stock requires update on Issue and Receipt as you say.

No you have a choice.

Many commercial systems maintain a Transaction Log so that you are able to trace Issues and Receipts by Date and maybe other data such as PO No and Issue Ref.

The data flow and therefore the processes to achieve this need to be considered and mapped out. Bear in mind the more traceability you build in the created will be the transaction overhead in all probability. So you need to define (Enterprise Rules) what you are going to do exactlyand when. Processing power is not such an issue these days but I can remember when overnight processes updated stock receipts etc. During the day they were all "pending" transactions.

Hopefully you have plenty of process power available but what about your network. How much traffic is going to be generated.

Lots of issues but now is the time to bring them into the open so that you do not have too many surprises later

Len

 

[neileg]

Storing the on hand value is usually a poor approach. It is better to calculate this on the fly using the receipts and issues data.

Do a search in here. Inventory and stock control are regulars in here. As Len has already said, this is not an easy subject.

 

[marcuscoker]

Many thanks for all your advice, I think that I will need to rethink this issue, and it probably would be better if I stored the onhand values on the fly.

Thanks

Marcus

 

[Len Boorman]

neileg is absolutely spot on the ball. Consider the situation sometime down the line when you have say 10,000 receipt transactions and 10,000 Issue transactions.

Calculating the on hand "on the fly" whilst the way to go has an overhead.

On the fly is the correct method. never store a calculated value. However you may have to make some compromises depending of anticipated transaction count and processing power available.

Be aware and up front about the compromise, why you are doing it and the implications.

The first compromise is the hardest because basically you should avoid it if at all possible. The second gets met with "well you compromised before" and is therefore easier.

I have had experience as have many others of inventory systems at various levels and they can get very complex indeed so as suggested search the Forum and get lots of background info

Good luck

L