Hi there, I'm trying to create a simple shop database, which can hold the following info...

• Products
• Customer details
• Orders

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 .

Any advice would be great - thanks.

Yes, you would need an order detail table that has a M:1 relationship with order.

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.

[QUOTE=subfocus]Hi there, I'm trying to create a simple shop database, which can hold the following info...

This Data Model might help to clarify your thinking :-
http://www.databaseanswers.org/data_...ders/index.htm

The same site lists many Customer-related Data Models :-
http://www.databaseanswers.org/data_models/index.htm

HTH

B.Dimple
Senior DBA