Hi there, I'm trying to create a simple shop database, which can hold the following info...
I can't seem to get my head around how I can store more than one product_id (and its quantity) in a orders record without using another table.
So I'd have a orders table, with a order_id for each record, which would be linked to a orderdetails table. Each record in the orderdetails table would hold the order_id it was for, the product_id and quantity ordered.
I hope this makes sense to someone as I'm a bit confused now .
And, since you could order more than one product in a given order, you should also add a line number field in the order details table.
The PK for the order details would then be the order ID AND the Line Number.
The order ID would be a FK to the orders table...the part number would be a FK to the parts table.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert