Inventory database on an item basis
Im hoping someone can point me in the right direction. :)
I'm a beginner and currently building an inventory database for my home business. I have successfully created products, suppliers, customers, purchase orders, purchase order items, customer order and customer order items tables :)
So I can now create purchase orders and customers orders and I also have a table that show my total inventory per product per category (ordered, in transit, shipped, etc).
Next step is for me to create a detailed inventory table (?) where 1 product = 1 record. So the first records in that table would be manually created from my initial stocktake then next records would be created every time I create a purchase order. First issue I see is that my purchase order can have several item per record : 1 line in the order can be for 3 items of the same product. So I'd need to then create 3 records in my detailed inventory table.
Is this doable? I feel like I'm bitting bigger than I can chew. If you know of any example that could stir me in the right direction that would be lovely.