Creating Product Combos from multiple products

[barkster]

I was asked to create a shopping cart/product system that would allow the owner of the site to create combos from multiple products on the site to offer package deal if purchased together.  I'm not quite sure how to handle this.  I was going to put the combo's in a separate table but he wants the inventory to be updated based on the individual products purchased in the combo and that's where I got stuck. Any suggestions on how to handle this in the database and in the cart?  Thanks

[aschk]

The database layout should be something like:

 

tables: products , product_package_map,  packages

 

1) each package can have many products

2) each product can be included in many packages

 

This leads to a many-to-many relationship.

 

Thus, in your packages table, you have the name and identifier (id) of the package.

In the products table, you have the name and id of the product + any other info.

And you NEED an interim table product_package_map (to normalise your many-to-many relationship), which contains the product_id, and package_id as a mapping.

 

e.g.

product_id | package_id
==================
1          | 1
2          | 1
3          | 1
2          | 2
7          | 2

 

from the above you can see the mapping between the product and packages tables.

[barkster]

That works but how would you do inventory on that?  Say if package 1 had product 3 & 5 in it then when you checkout you want to deduct 1 for each in the inventory.  How do you know if it is a combo product vs a single item product.  Thanks for the help.

[aschk]

The following gets all the product ids for the package you have selected:

 

SELECT product_id FROM product_package_map WHERE package_id = '{$id}'

 

Then foreach product_id that you get from the upper query.

 

UPDATE products SET inventory_count = inventory_count - 1 WHERE products.id = '{$product_id}'

 

Probably best to run this in a transaction else you have the possiblilty that the 2nd query might not get executed when the order is INSERT'ed into it's table.

[barkster]

Ok, thanks I'll have to research transactions.  Never messed with that