How would you do this?

[mbriggs]

Model is a shopping cart type system. You have an invoice at the end of the transaction and an invoice number. Now you have several items that go on that INVOICE NUMBER(Unique Number). So lets say you are in Customer service and you want to call back that invoice with its items that where on that Invoice number. How would you set the database up?

Or lets say you have 5 cars in inventory to ship and you want to create a load number for that 5 car shipment. Now as a customer service person you must be able to punch in the VIN number of the car and search by load number and display the entire load of 5 units and their details. So in essence its a shopping cart model the cars are the items and the invoice number is the load number and in the back end for the customer service agents there should be a way to call either the invoice number or load number and return its contents.


so look below you have 3 units with 3 unique vin# I would like to take these 3 units out of inventory and move them into a unique load_id. So in the database it will have the unique load_id as a primary key for that new table and the VIn# as another primary key. The load_id must be some sort of auto increment to get the load_id and then just i guess make that load id print 3 time so as to attach this load _id against the 3 units.

maybe just explain to me what tables to create from lets say

current_inv ----> load_id_table


I hope i am explaining what i am looking for






My output for testing:
********************************************************
chk1 variable contains:
array(4) { [0]=> string(17) "2C4GM48L55R556410" [1]=> string(17) "1D7HE28N25S218670" [2]=> string(17) "1D4GP45R65B429926" [3]=> string(17) "1D7HA16D95J648976" }
SQL: select * from excell where vin in('2C4GM48L55R556410','1D7HE28N25S218670','1D4GP45R65B429926','1D7HA16D95J648976')

*****************************************************
THese are the 3 units for my example

1D4GP45R65B429926 Caravan Fwd -Jerry Ulm Dodge Inc Tampa FL
1D7HA16D95J648976 Ram Regular Cab 1500 -Jerry Ulm Dodge Inc Tampa FL
1D7HE28N25S218670 Dodge Dakota Quad-Dayton Andrews Inc Clear

[mbriggs]

I knew i was explaining it wrong.

[timvw]

It appears you have a 1 - n relation between invoice and items.

So you could store a link to the items by adding the invoice primary key to the item..

Or you add a linktable which has invoice_id, item_id columns..


Your query would look like (now all you need to do is add some WHERE restrictions...):

SELECT *
FROM invoices
INNER JOIN invoiceitmes USING (invoice_id)
INNER JOIN items USING (item_id)