A Simple DB Structure for a Shopping Cart

This is a little tricky! In order to model a shopping cart, let's first understand why our knowledge in SQL isn't quite enough (well, actually, it's enough, but we need to apply it smarter).

What doesn't work, first: Placing a foreign key into the Products table into a Customer doesn't really help: That only allows each customer to have one product in the shopping cart and it doesn't even allow for more than one item of this product . The other way around also doesn't work a foreign key into a Customer in a product allowsa for only one customer to order it bad...

So, here's the trick: Create a table SHOPPING_CART with three fields 1. a Customer ID (FK into the Customers table) 2. a Product ID (FK into the Products table) 3. a number (how many of this product are in the cart) The primary key of this is the combination of Customer ID and Product ID figure out how to do that!). That's already about all you need to do.

The scenarios then are: A. To add n items of product p to the shopping cart of customer c, you insert the tuple (c,p,n) into the SHOPPING_CART table. B. To loop over the contents of the shopping cart of customer c, you use something like SELECT ProductID, Number FROM SHOPPING_CART WHERE CustomerID = c

Exercise: I provided you with my solution of the Categories/Products/Customer Schema from project 2. Please, use this from now on. Add the SHOPPING_CART table as described. Manually place for customer Gerald Bostock 2 DULL 122 and one jPET 12 into his shopping cart using INSERTs. Drop it all into the drop box.