How to handle multiple products in single invoice

+3 Altaf Husain Neva · January 15, 2015
I have an invoice table in which the fields are: 1) invoice_id(PK)   2)product_id (FK)    3)total_amount.
Now if the customer is buying multiple products than how should i store multiple products? I have heard that using mul Key is not a good way. Any other solution?

Post a Reply

Replies

Oldest  Newest  Rating
+2 Branislav Lazic · January 15, 2015
Create two tables: invoices and invoice_items. Something like this:


CREATE TABLE invoices (
  id_invoice INT PRIMARY KEY,
  date_issued DATETIME,
  total_amount DECIMAL(10,2)
);

CREATE TABLE products (
  id_product INT PRIMARY KEY,
  name_of_product VARCHAR(55),
  VAT DECIMAL(4,2),
  discount DECIMAL(4,2),
  price DECIMAL(10,2)
);

CREATE TABLE invoice_items (
  id_invoice INT,
  id_product INT,
  FOREIGN KEY (id_invoice) REFERENCES invoices(id_invoice) 
  ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (id_product) REFERENCES products(id_product)
  ON DELETE CASCADE ON UPDATE CASCADE
);
0 Altaf Husain Neva · January 17, 2015
Wow! I got it. Thanks for the help. I mean not just a thank but lots of it. I was working with my final year project on Supply Chain Management and i was extremely in need of this.

Ohh yes one more question. In the 3rd table you have not created any primary key and i have heard that without a primary key you can't join tables and all. So do I have to create one or it will work without it ?
0 Branislav Lazic · January 18, 2015
No. But it's desirable.
  • 1

SQL & Databases

107,023 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator