I have a database with the following table:
Products - Product_Orders - Suppliers - Customers - Sales - Sales_items.
I want to keep products inventory in the following manner:
1 - When we order for a product, then the quantity ordered is added to the initial quantity in stock of that product.
2 - When we sale a product, the quantity sold is deducted from the initial quantity in stock of that product.
3 - When we reach a minimum stock quantity for a product, then that product is removed from the list of products available for sale.
Questions:
- What are the required tables to achieve the above ?
- What will be the columns of those tables?
- What will the relationships be like?
I am planning to use forms to insert records, so there will definitely be a front end programming. But for now I need to understand the structure of the database before anything else.