DB structure for bundled products

Hi, I am having problem with desinging db structure for bundled products.
In my website I sell subscription and other products for subscribers.
Subscription has additional products in it included.
I defined subscriptions as a product with product_category = SUBSCRIPTION
Currently I have product and bundled_product tables:

product table has:
product_id
product_category_id
product_price
product_discount
date_added
....

bundled_product table has:
bundle_id
product_id
date_added

In this structure I am I have some questions:

1. Where the product price should be in? in product table or bundled_product table
2. To list products to users which table should I use product table or bundled_product table. because I also have single products.
If I do this I will need to add one row for single products;
3. should I add another cross relation table for relation between bundled_product and product table? 
4. Lets say subscription bundle consists of product_type A, B, and C products (one from each product type). Lets say as promotion I want to add additional product type C to subscription. when there is 2 products with same type in bundled I need tell user which C product is MAIN product and which is PROMOTIONAL 


I building this type of DB first time I cant decide how should I design my DB.

Thanks in advance...

1. Where the product price should be in? in product table or bundled_product table

Depends on your business choices. If your bundle is always equal to your individual products, you would put them in the individual products. If not, you need both a bundle price and an individual price. For flexibility I would do both.

2. To list products to users which table should I use product table or bundled_product table. because I also have single products.
If I do this I will need to add one row for single products;

Depends on your business logic. If you are only selling bundles, then only present those. If you are presenting individuals and bundles then you need to use both tables. If I were doing this, I would have one table of products, with a column indicating if it was a bundle or individual product.

3. should I add another cross relation table for relation between bundled_product and product table? 

Yes, if you need to break it down. Again depends on your business logic. However, you could just have what is included in the bundle in the product description. If you were doing it in one table, and the product being viewed was a bundle, you could then lookup in a separate components table to get what components it is made up from. There are lots of approaches to this, it all depends on what outcome you are looking for.

4. Lets say subscription bundle consists of product_type A, B, and C products (one from each product type). Lets say as promotion I want to add additional product type C to subscription. when there is 2 products with same type in bundled I need tell user which C product is MAIN product and which is PROMOTIONAL

In a components table, have an extra column indicating if the component is a promotional component or not. You could even add a column for expiry date too, if you needed a countdown for how long the promotion has left to run for instance. Or you have your standard products and bundles, and a separate table for promotions, containing all the promotional product id's etc. Again there are lots of different approaches to this.

It is always tricky getting a compromise between a nice simple approach, and flexibility for future changes. The amount of times customers say to me things like "oh, actually, for promotions we want to do X as well" when the entire site was built on the premise of promotions being of type Y, so that doing type X promotions is virtually impossible.

The answer to this is a detailed spec, and once building has started, I love saying "we can do that for you, but since it was not included in the original spec there will be additional charges for development and reworking the original code. Would you like me to do a quote for that?" Very often you get the answer "No it's ok, its not that important really".

My advice would be to have all your saleable products in one table, including individual items and bundles. Use a column to indicate what is what, and keep components in a separate table for bundles, keep promotions in a separate table for promo prices and expiry dates etc. Then a few joins and a bit of logic and you can present whatever you want to your users in any way you choose. Your categories table might join to a category_contents table which would indicate what bundles or products are in each category. Your bundles can display components, and your promotions table will tell you which ones have special offers or sale prices etc.

However, all of this is down to your chosen user experience, and your specifications for the flexibility you want to achieve in the administration of you site.

Hope that helps in some way,

Paul

I'm in agreement with Paul.

I'd setup the database to have a products table with a column to indicate if the product was a bundle.
Then another table with all of the products associated (and qty per product if you sell anything physical or limited uses/licenses/etc):

Table:product_bundles
product_bundle_id
parent_product_id
child_product_id
quantity
date_added

I'd actually put the product_bundle_id in the product table column for the bundled column (i.e. it is either a zero for no bundle or the bundle ID, which makes it much easier for your SQL to find the bundle's contents when you need it)

This lets you have all of the reporting and normal flows for your products and bundles using one table instead of having to group multiple tables together.

Thank you for your advices PaulD and Kaosweaver I appricate that. I will consider you advices when updating my structure