Take the 2-minute tour ×

I have a question about many-tomany relationship. I know that we have to create a junction table. But let say we have this scenario:

one table for Customers one table for Orders one table for products

One order can have many products and one product might be in many orders. Therefore we create a junction table. Could I just create a junction table between Customers and products and this junction would be a Order table to store the Orders?


share|improve this question
You would need an OrderProducts table. Orders would have CustomerID, and OrderProducts would have many OrderID and ProductID it would still need its own index because the many of the same Product might be on that Order –  Shmiddty Nov 12 '12 at 23:31
Alternatively, you could have OrderProducts use OrderID and ProductID as a composite primary key, and include a Quantity field. –  Shmiddty Nov 12 '12 at 23:32
You probably wouldn't want to create a junction table ala CustomerProducts because it wouldn't allow you to combine the various rows into individual Orders –  Shmiddty Nov 12 '12 at 23:35

1 Answer 1

If you do that, how would you know which products a customer included on a specific order? And, how would you handle the customer ordering the same product on multiple orders?

So, no, you can't just create a Customer to Product junction table because that will not give you the information you ultimately need.

share|improve this answer
You are right. Do you recommend create a composite key in the OrderProduct table? –  Camus Nov 13 '12 at 0:02
If yes. Is it a good practice, lets say, create the primary key of the order table, combining the foreign key(customerID) and the orderID. Then create a combined key in the OrderProduct from the foreign key of the order(customerID and orderID) + the productID. Therefore would be easier to query. –  Camus Nov 13 '12 at 0:05
I avoid composite keys most times. The problem I've run into is too many columns in the composite key. In this example, if you do a composite key in OrderProduct, a key in OrderProductThing now contains three columns. Writing those queries gets painful as composite keys include more and more columns. –  Jeff Siver Nov 13 '12 at 1:11
What problem are you trying to solve? Are you running a particular report or query that gives you double ups? Or is this just a modelling exercise? Every Orders database I've seen has an Order Header table (with Customer) and an Order Detail Table (With Product and Order Header ID) –  Nick.McDermaid Nov 13 '12 at 1:23
It is just a modelling exercise. If I do like you said how can I query to know all the products names that the user has bought. Isn't better have a composite foreign (customerID and OrderID) in the OrderProduct table, so it'd be much easier to query? –  Camus Nov 13 '12 at 3:28

Your Answer


