DB structure for bundled products

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