I am looking to create a database that will allow me to manage multiple products.
I have a first table “inventory” which lists the different products that I offer:
ID1 product 1 quantities nb ordered nb sold
ID product 2 quantities nb ordered nb sold
Then I have a sales table that references the different sales. Is it possible to make a dynamic follow-up of the stocks with orders including different products with different quantities? example of order:
product 1 *3
product 2 * 4
product 3 *1
I would like to have several quantities on the screen below
to achieve what you are planning you would need three tables instead of two:
sales line items
STRUCTURE CHANGE: The sales line items would be the table linking products and sales and would contain several records, each matching a specific product to a specific sale. These records could then contain several fields among which one of them could be the quantity of the product in that sale (but there could be many others including shipping status, product condition, priority, etc.)
VISUALIZATION: To visualize all the products in each sale in a friendly, intuitive manner you could use a view in the sales line items table grouped by sale, so that for each sale you have a group containing all the products related to it
you can achieve what you are mentioning by having, for example, for each product a column with the product and one with the quantity (in a Product 1, Quantity Product 1, Product 2, Quantity Product 2 format). However, I would strongly discourage you from opting from such a solution because it reduces significantly the functionality of your system, preventing roll-ups, counts, averages, etc. at a sales level. Moreover it makes any automations for line items much harder and messy.