Product Table- Bundle of products

[machumpion]

Let's say I have a product table and an orders table.
Product table has fields: ProductID, Flavor, BagQuantity
Order table has fields: date, ProductID, OrderID, Quantity, Revenue

some ProductIDs sold are actually bundles of individual products

i.e." 4 bundle Chip bags: 2 ketchup/2 bbq"

there are already records in Product table for the individual items
i.e. BBQ chip bag- BBQ- 1 ;
Ketchup chip bag- Ketchup - 1

currently in the "Flavor" column for Bundle Products in the Product table, I label them as "various"

If I wanted to query the quantity of bags of each flavor sold (including for bundles), would I need to identify the flavors in each bundle by making a record in the product table for each bundled flavor?

i.e. 4 bundle chip bag:2bbq +2 ketchup - BBQ - 2
4 bundle chip bag: 2bbq + 2 ketchup - Ketchup - 2

 

[MarkK]

You might make a ProductComposite table, which joins a parent product to one or more component child products. Obviously Flavour is not a good field name in this case, since not all products will be best described by how they taste, but maybe ProductName, and ProductDescription are more generally useful.

tProductComposite
ProductCompositeID (Primary Key)
ParentID (Foreign Key, link to the parent product)
ChildID (Foreign Key, link to the child product)
Quantity (the count of child products present in the parent, default to 1)

This way you can define a product as being composed of fixed quantities of other products.

 

[machumpion]

You might make a ProductComposite table, which joins a parent product to one or more component child products. Obviously Flavour is not a good field name in this case, since not all products will be best described by how they taste, but maybe ProductName, and ProductDescription are more generally useful.

This way you can define a product as being composed of fixed quantities of other products.

currently in the Order table, there's a Product field (foreign key) that ties to Product Table: ProductID field. If I split the Product table into Parent and Child tables, do both ProductCompositeroductName and Product.ProductID tie back to Order.Product?

Also, let's say Flavor is used to link Children to Parent products. In the Parent table, would a bundled product feature a record for each flavor in that bundle?

thanks!

 

[jdraw]

It sounds like you need an OrderDetail (or OrderLineItem) table, UNLESS every Order is for 1 Product.

Order--->OrderDetail<----Product

where Product can be an Individual Product or Group of Products (ProductComposite as Markk said)

 

[MarkK]

If I split the Product table into Parent and Child tables

That is not what I proposed. There are no child products. A product is a product, and remains a product, and relates back to an order in exactly the same way. It just so happens that some products might be composed of some quantity of other products.

Also, let's say Flavor is used to link Children to Parent products. In the Parent table, would a bundled product feature a record for each flavor in that bundle?

Flavor should not be a link. The "dining set" product might contain 1 table, and 4 chairs, each of which you might also sell as separate products. Lets say the table has a ProductID of 1, and the chairs have a ProductID of 2, and the Dining Set has a ProductID of 3. The rows in the join table would be . . .

ID | ParentID | ChildID | Quantity
 1 |        3 |       1 |        1
 2 |        3 |       2 |        4

So then the Product form would have a subform into which you could define this list of sub products.

 

[machumpion]

That is not what I proposed. There are no child products. A product is a product, and remains a product, and relates back to an order in exactly the same way. It just so happens that some products might be composed of some quantity of other products.

Flavor should not be a link. The "dining set" product might contain 1 table, and 4 chairs, each of which you might also sell as separate products. Lets say the table has a ProductID of 1, and the chairs have a ProductID of 2, and the Dining Set has a ProductID of 3. The rows in the join table would be . . .

ID | ParentID | ChildID | Quantity
 1 |        3 |       1 |        1
 2 |        3 |       2 |        4

So then the Product form would have a subform into which you could define this list of sub products.

so i redesigned the database to reflect the structure you suggested it it seems to be working. Thanks!

There is one thing I noticed

Since 1 dining set can have multiple components, when I query the sales dollar figure for dining sets, it repeats the Parent ID for each component that exists in the set. As a result, the sales dollar figure is repeated per component also and inflates the sale

i.e.
Dining Set - Table - 2 - $20
Dining Set- Chair - 1 - $20

in actuality, Sales is only $20. Is there a way to show all the components yet still reflect the true sales $?

 

[MarkK]

the sales dollar figure is repeated per component also and inflates the sale

Do not return pricing data for the child products in your query. You should only use the pricing from the parent row.

 

[machumpion]

Do not return pricing data for the child products in your query. You should only use the pricing from the parent row.

is there a way to display the children and exclude their prices but include it only for one row ?

 

[MarkK]

is there a way to display the children and exclude their prices but include it only for one row ?

How does this ever make sense to do? The pricing is in the parent row.

 

[machumpion]

How does this ever make sense to do? The pricing is in the parent row.

say i want to query the components used in each parent set AND determine the sales revenue from selling the parent set (order table). Currently, returning the children (Composite table) and revenue (order table) yields the Revenue value repeated for each child in the set. If I could somehow return the Revenue value only once yet still retain the rows showing the children, I can achieve my objective.

 

[MarkK]

If you post the db I'll write the query. But the tables in the query should be
1) the product table
2) the product composite table (linked to product via parentID)
3) the product table AGAIN (linked to composite via ChildID)
And you need to use outer joins (left and/or right join) so that if there are no child records, then the child fields are null, but the parent fields are still present. Then there is no problem distinguishing your parent and your child fields, and the costs won't get merged.
Hope this helps,