[SOLVED] multiple items in a row

[bhavin_85]

Hi guys

 

this is totally a n00b question but i cant figure out how to do it!

 

I need to have an invoice table which will hold all of the data for the invoices...it will hold the customer id, invoice id and items

 

My question is if one invoice has many items ie 1 customer purchases 10 different products how would i create a table to represent that?

 

Do i create a table that has 10 different item fields? or is there a better way to do this? as a customer could purchase more then 10 items so this solution really doesnt work  ???

 

any help is much appreciated!!

 

Bhav

[paul2463]

have the items in a seperate table linked together with the invoiceid with a "1 to many" relationship, each invoice must have at least 1 item in (or there is no point producing an invoice) but each invoice can have many items

so you two tables for this are

 

table invoice, with invoiceID, customerID and invoiceNumber

table invoiceitems with invoiceitemsID, invoice.invoiceID, itemID

[bhavin_85]

hmm good idea, but not sure that will totally work....i need the items to be generic - ring, watch, necklace...then when they are entered into the invoice the specific details are captured could i still do that with 2 tables? this means that inoice id wouldnt work either would it? because any item could be in any number of invoices

[paul2463]

which is why only the itemID is kept in the invoiceitems table not the item itself

 

Table item

itemid item

        1           ring

        2           necklace

        3           watch

 

 

table invoice

invoiceid customerID invoiceNumber

1             3           inv123456

 

 

table invoiceitems

invoiceitemsID invoice.invoiceid itemid

1               1               1 (ring)

2               1               2 (necklace)

3               1               3 (watch)

 

so customer 3 bought a watch a ring and a necklace all on invoice number inv123456