Database Table Structure for Shopping Cart

Question

I'm a beginner with SQL and am working on one of my first databases. I am trying to create a very cut-down shopping cart and am currently working on getting the database together before I start coding the site.

One of the things I am struggling with is how to structure the products portion of the database. If all my products were individual items it would be ok. My problem is thatI would also like the ability to create "packages" of sorts that would combine several products into one Package/product with its own unique ID but different price.

Has anyone tackled this before...Heck, I am sure someone has. My question is: Is there a best practice way to structure the database tables to allow for this sort of thing? Logic-wise I am having a hard time wrapping my head around this one.

Answer 1

I would probably create a package table with the package id and price for the package and then have an intermediate (cross reference) table between the package and products with package IDs (as a FK to the package table) and corresponding product IDs (as FK to the product table).

Answer 2

Ayende Rahien (of RhinoMocks) has recently been doing a series on building a shopping cart, starting here: http://ayende.com/Blog/archive/2008/12/07/designing-a-shopping-cart.aspx. It does use a class diagram but you should be able to convert the concept into a database diagram.

Answer 3

Anything Ayende is doing is going to be over the head of a beginner. To answer your question, you need 3 tables. One for your products, one for your packages and one to "link" products to packages. I'm assuming you've got the first two covered. The third is called a Many-To-Many relationship.

It should be called ProductsPackages and have two columns (PackageId and ProductId). You should create foreign key constraints to the Products and Packages tables.

Now, to add a product to a package you insert the id of each into the ProductsPackages table.

Answer 4

One way that avoids extra tables, but imposes some limits is to simply add a self-referencing "ParentID" field to the product table. That "ParentID" simply points to the associated package product. Since it's all the same table, your packages have the same fields (price, etc.) as your individual products. To find what products make up a package, simply query for all product with a "ParentID" of the package's "ProductID".

The limitation is that products can only be a part of one package. So if you sell a baseball, bat, and glove, as well as a 'baseball' package including all three, the glove could not ever be part of the 'football' package.

However, one advantage is that both the 'baseball' and the 'football' packages and the 'coach handbook' product could be part of the 'complete sports' package. And if you remove the bat from the 'baseball' package, it's removed from the 'complete sports' package as well. All without adding a single table.

Of course the single package per product limitation means this certainly won't work for every situation.