Shopping Cart with Combo Deals

barkster

I was asked to develop a shopping cart that would the creation of combo deals using existing products to build the combo. What I need to have happen is when a combo is added to the cart and purchased the individual products that combine to make the combo need to individually deducted from the inventory. Where I'm having difficulties is coming up with a way to get the combos into the cart because right now they are a separate table from the products table and I'm not sure if I'm going about this the right way. Here is a diagram of my tables and a link to a demo of the inventory system. Any suggestions on how to do this or get the combos into the products table and making it work?

http://69.65.102.98/~lightfli/productdetail.php?productid=5

Products Table

product_id | name | price

1 | motor | 2.20
2 | engine | 22.12
3 | prop | 4.00
2 | prop2 | 4.60
7 | mount | 8.00

Package_Map Table

product_id | package_id

1 | 1
2 | 1
3 | 1
2 | 2
7 | 2

Packages Table

package_id | name | price | product_id

1 | Premium Combo | 200.12 | 2
2 | Basic Combo | 140.21 | 2
3 | Basic Combo | 122.32 | 1

jeremyphphaven

First thing I notice is your missing a way to show what is included in a combo.

Then you can check it against that data at a view cart/checkout level to see if there are singular items in a cart that make up a combo.

barkster

If you look at the demo I'm not. I use the package_map table to lookup the products in the combo.

jeremyphphaven

oh, I see, you have multiple entries using the same product_id... didn't see that before.

not sure I like how it's setup personally, but it should suffice... just additional queries / joins in your sql statements.

So what's the prob?
IN the cart and at checkout you need to pull each item id and determine if it's part of a bundle... once you flag a product as part of a bundle, then see if the other item(s) that comprise the bundle are already in the cart as well, then flag them as part of the same bundle.

If they're not part of any bundle, flag the product as checked, that way you don't check products over and over again.

clear as mud? =O)

barkster

Yea I was hoping on something a little simpler. Guess cause I essentially have two product tables. If you have a better way please suggest. I really don't want to try and figure out a way of distinguishing between a combo or a regular product and building a bunch of queries. I don't think my way is the right or easiest way to do it, that is why I posted the question 😕 to me it isn't right.

jeremyphphaven

imo, from a database perspective, you can probaby do away with the package_map table and just add the id's of the products in the one table:

package_id | name | price | product_id

1 | Premium Combo | 200.12 | 2,4,5,6
2 | Basic Combo | 140.21 | 2,54,3,22
3 | Basic Combo | 122.32 | 1,5

then you don't have to do any sql joins or what not to get the right query if you're not savy with that.

barkster

Sound good, how would you handle a product vs a combo item in the cart as far as a primary key value, I'm using the productID but that is where I ran into a problem because they could have two of the same values.