Design: How to account for a custom product in a transaction item table This question is related to Database Design: inventory and sales system?

So for example a burger is a Product which is made up of Component bun, meat, cheese, pickles etc. When this burger is sold it can be customized by adding/removing/modifying components. 

How is this customization accounted for as a line item? Would there be another table ProductCustomization with a reference to the Product and the Component and a column to indicate the type of customization? What about if you want to modify the Composition of a Component in the Product (ex. 3 pickles per burger).

An example of a customization would be ordering a burger with no pickles, or adding extra cheese.
Since first starting to type up an anser I have argued myself into, and out of, a set position. I think this question steps over the border from the science of normalisation into the craft of data modelling. The answer will be determined by how you (or, more importantly, your users) understand the semantics of the situation. Is "extra cheese" a new "thing" which is sold, just like "burger" is a thing which is sold, or do they see "extra cheese" as a simple modification of the component list and "burger" is still the product?

I'll take a different business area to show the difference more clearly. If two people each bought a Ford Focus but with different engine, radio, wheels, paint etc., would you think they had bought esentially the same "thing"? Chances are you would. Even though different specifications go into the factory, the item that emerges is thereafter treated as a single unit for sales, transport, handling, support and all other purposes. However, if one customer orders, say, a tow bar that would be a post-factory option and would be handled through different sales, distribution, stock keeping, and maintenance channels, so is likely to be seen by the business as a separate product.

So to the burger. If a burger with extra cheese is still a burger then model it as variations to the composition. If "extra cheese" and "no cheese" are seen by the users as products in their own right model them thus, with their own composition and price, which may be negative.

In term of line items, treating the variations as separate products is the easiest. Once set up in Product and Composition they will flow naturally through to the invoice and stock totals just like the standard products will. You may need to add a product dependency table to ensure the variations make sense ("no cheese" only applies to cheese burgers; can't order "cola with extra cheese").

For the scenario where there is a single product with variaitons I would model it thus:

Customer -&lt; Order -&lt; LineItem &gt;- Product -&lt; Composition &gt;- Component
                        |                                   |
                        +-&lt; ProductCustomization &gt;----------+


ProductCustomiation will have an AddRemove column to say whether its "extra cheese" or "no cheese". It will also have a price variation column which is used with Product.Price when calculating the LineItem.Value.

By making ProductCustomization bridge LineItem and Component I'm allowing for a customer to specify any variation she chooses. If the business only want certain combinations to be available these have to be modelled separately. I specifically do not link variations to Product as her ordering extra cheese does not changes the basic definition of what a burger is, and you do not want to be defining new Products for every possible combination of Components.

The price of a customization can be hard to pin down. Is cheese on a burger the same Component as cheese on a pizza? Is extra cheese on a 10" pizza the same value as on a 20" pizza? Where you store the price of a Component-as-variation will depend on the rules your business uses to determine this price.

Similarly with product/ component dependencies. If you're allowing complete freedom of customization it is easy. Once you start limiting, the combinatorial explosion can get really large quickly.