Product_ID in my Invoice table I'm doing a uni assignment.
I have

a stock table where stock is assigned an ID (product_ID).
a sales table where the stock is purchased.
an invoice table where sales are processed.

I want the invoice entry to contain many product_id's and their quantities.
I currently have &quot;Invoice_id, Customer_id, Staff_id, Product_id, Quantity, Price, Total Cost&quot;.
Doing it this way I can only have one product_id per entry. I want there to be multiple entries under one invoice_ID.
Am I thinking of this the right way? When you get an invoice it says the product on it and how many of them you bought.
For what you're trying to accomplish, you'll want to normalize your data into two Invoice related tables. One for header information, e.g. Invoice_Id, Invoice_Date, Customer_Id, TotalCost; and one for the invoice line information, e.g. InvoiceLine_Id, Invoice_Id, Product_id, Quantity, Price.
The InvoiceLine table will have one row per product that was purchased on this invoice. It will have a unique InvoiceLine_Id per row (key field), and the InvoiceId field in it can be repeated across rows because it will be a many-to-one foreign key relationship to the InvoiceHeader table.