Designing a database for stock control

Question

Having a few issues with my database design. I have designed my system like the following image

As you can see it is a pretty basic system to a point. A user can create a supplier by adding supplier details. A user can then add a product and link it to a supplier. Thats the pretty straight forward bit (I hope!). Now I will attach my database design which should hopefully cover what I have mentioned.

So a supplier can have one contact (person within the suppliers company the user of my system will contact) and a supplier can have one to many products.

He is the part I can't figure out. Twice a week, the user of my system will receive stock from all their suppliers. When they receive this stock, they should go into the update stock screen within the application and input the amount of stock they have received for a certain product. I have added a products_stock table which should hopefully put me on my way to cover this aspect (I think it is missing a lot though).

The last screen however is a display of predicted stock. Lets say for instance on the day my products are delivered, I receive 10 units for Product One. I will then manually count the number of units I have left from the last order for Product One (say 2) and update the stock count for Product One to 12. This means, that really, I only needed 8 units between the two deliveries for product one. The predicted stock screen is supposed to show the predicted stock levels I should place an order for, for a particular product, over a specified time period. So if 8 units was the average stock sold for product one per week, if I wanted to see how many units I should order for product one for a month, it should display about 32 units.

This is not supposed to be a complex system, it should have this manual aspect to it. I have designed the database up until a point, I was hoping I could get some suggestions regarding the products_stock table and how I can handle stock predictions for a specific period of time (if I maybe need additional tables).

Any advice appreciated.

Thanks

Answer 1

Inventory is more like a view and not a table. Inventory is really a series of movements of goods between locations, as well as periodic counts / adjustments.

Inventory is complex. You should play with some inventory software and read some data modeling books.

Use the concepts of Locations (real and virtual) and Movements (a movement should be its own entity).

Item smashes? Move it from its inventory location to the "damaged" location

Item went missing? Move it from inventory to the "ether" location

Found a random Item? Move it from "ether" to inventory

Sold an Item? Move it from inventory to "sold"

Bought an Item? Move it from purchased to inventory

Some other things to keep in mind:

• You could sell something, and it's returned, and you put it back in inventory
• You could buy something, but it ain't right, so you send it back to the seller
• You could sell something that you don't own (on consignment, or not in inventory yet)
• You might have something in inventory that is not currently for sale.

I won't get into the accounting aspects of inventory :)

Answer 2

One possible approach:

Have a separate "stock item" record for each unit received. When it is sold, record the date of sale in this record.

Then if you want to know how many items were sold in a given time range, just count the records with sold dates in that range, like select count(*) from stock_item where product_id=@product and sold_date between @from and @thru. If you need to keep track of items lost to spoilage, theft, whatever, then you might have a status flag of some kind that says whether it's in stock, sold, destroyed, whatever, and also the date of that status.

If the number of units that pass through is large and there's no other reason to keep individual stock records, you could just have "daily sales" records instead, with a date and number sold that day. Same idea.