0

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?

Thanks

Camus
  • 827
  • 2
  • 20
  • 36
  • 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
  • 1
    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 Answers1

2

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.

Jeff Siver
  • 7,434
  • 30
  • 32
  • You are right. Do you recommend create a composite key in the OrderProduct table? – Camus Nov 13 '12 at 00: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 00: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 01: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.Mc Nov 13 '12 at 01: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 03:28
  • That violates third normal form (http://en.wikipedia.org/wiki/Third_normal_form) so I recommend it (it makes maintaining the data more difficult). Plus, with the way SQL works, it's pretty easy to join in a CustomerOrder table to relate a customer to products. – Jeff Siver Nov 13 '12 at 16:50