title: How does one store 'packages' of multiple products within a database
body: <p>I have a site that provides an e-commerce service, basically allowing sellers in the industry to place their products up for sale to clients.  Sellers select the products they wish to sell and set the price.  They are then hands off.</p>
  
  <p>In a simplified form, I have the following relevant tables in my database for storing product and order information:</p>
  
  <pre><code>Product_Info
  --------------------
  ID (autonumber)
  name
  ...
  
  
  Order_Head
  --------------------
  ID (autonumber)
  CustomerID
  ...
  
  
  Order_Line
  --------------------
  ID (autonumber)
  OrderHeadID
  ProductID
  ...
  </code></pre>
  
  <p>This works great for simplified orders where customers choose any number of products and add them to their cart.  However, I'm now faced with the problem of adding seller created and managed 'packages', wherein sellers can group multiple products together into a single item and sell it at a lower price than the individual items would cost together.  For instance, if oranges costs $15 and apples costs $20, a package containing 2 oranges and 1 apple may only cost $35.</p>
  
  <p>The twist, and the part that has me stymied right now, is that I would very much like packages to be able to contain other packages.  For example, a seller could make an "assorted oranges" package containing 3 oranges.  They could then make an "assorted fruit" package that contains 2 apples and 1 "assorted oranges".</p>
  
  <p>How to manage that is confusing me both from how to list the products within a package when I could be referencing an ID from either the product table or from the package table, and from how to record the products in the order_line table since the productID could be pointing to either a product or to a package.  And, of course, this needs to be designed in an efficient manner so we're not taxing the database server unneccessarily.</p>
  
  <p>I'm primarily a web developer and haven't done much with e-commerce before.  Could anyone offer some direction as to an appropriate set of tables and table modifications to apply to the database?  I don't know why, as it doesn't seem like it should be that complicated, but this one has me stuck.</p>
  
  <p>For reference I'm using MySQL 5.1, connected to ColdFusion MX 7 (soon to be 9).</p>
  
  <h2>EDIT:</h2>
  
  <p>Thank you for the responses so far.  I will take a little time to think on them further.  In the mean time I wanted to clarify how the order process works since it appears to be more relevant than I may have originally assumed.</p>
  
  <p>My product works similar to Shutterfly.  Photographers post photos, and clients may purchase prints.  The photographers need tools to set all the pricing, and will often offer packages if it is from a professional shoot.  Fulfillment is done by a lab that my product submits orders to automatically, but they have no knowledge of pricing, packages, etc.</p>
  
  <p>Photographers also have the option of running specials to provide clients with x% off or BOGO deals, but I can handle that separately.  For right now I'm more concerned about an efficient and simple way to store the photographer defined packages, the client's image selection for each product in the package as they shop (currently stored in a order_line mirror table), and the eventual order details, so that they can be queried for display and reporting quickly and easily.</p>