title: How to handle quantity/stock monitoring in an inventory system?
body: <p>I'm creating a simple inventory/pos system for a hardware store as a school project.</p>
  
  <p>I'm wondering how to properly implement monitoring of stocks/quantity on hand and sales of items. As of now i have a field 'QuantityOnHand' in my products table. This is where the total number of stocks is stored. Whenever a sale is made, i subtract the quantity of item(s) sold on the 'QuantityOnHand' field and save the items in two related tables, 'Sales' for the recording of the transaction and 'SalesItems' for the items sold. </p>
  
  <p>This design was ok for the most part but i cannot create a report that compares the remaining quantity of items of the past day/week against the remaining balance of the current minus the sales/defective items because the quantity of items was not properly tracked and is only saved in a single field.</p>
  
  <p>How do i design the database in such a way that i can properly monitor the quantity of items?</p>