Best way to model "Composite Skus"?

Question

I have a SKU record which keeps track of raw materials used, pricing, cost, etc. An example SKU would be a Hamburger (1 bun, 1 patty, 1 oz ketchup, costs $11 make, priced at $4, etc).

I need to add the concept of composite SKU's now, such as Burger and Fries Meal, which would have a Hamburger SKU, and Fries SKU, plus special packaging for the Meal pack. It is unlikely that the number of sub-items grows more than 5 at most.

I need to model this in my DB as well as my application. The user does not care if a SKU is composite or not except when they are actually doing initial setup, and composing what a Composite SKU consists of.

Here is a what I have considered:

Option 1: I considered giving SKU's an optional Parent SKU. Then the number of sub-items is scalable (1 to many), and the use of the SKU object in my application code would remain largely the same (especially in the user-facing part of the application), as I would still be using SKU's. A con is that because a SKU would be denoted as having a parent, thus I would need to create Hamburger SKU's for standalone sale, for Hamburger and Fries Meals, for Hamburger and Onion Ring Meals, etc. This is a pretty big con from a stock keeping perspective.

Option 2: Giving SKU's optional child items SKU.SubSku1, SKU.SubSku2, etc. (1 to N). The pro's of this are that it again allows me to change very little of my user-facing code. It would allow a hamburger to appear in more that one composite SKU, as well as in standalone. Supporting adding of sub-items in existing UI would be much easier this way as well. This doesn't require a join table. The con is that supported an increase in the number of sub-items would require a schema change.

Option 3: Basically the same as 2, but using a Join table (many to many). This would scale the best, but I am not sure scaling is really a problem, as like I said this is more of a Meal to Hamburger problem, not an Organization to Person relationship. This could be messy to deal with in my UX code, but we'll see.

Option 4: Create a CompositeSku model in either many-to-many or 1-N relationship with SubItems. This option could be broken up based on relationship. But the significance of this option is that it is a separate model from my normal SKU object. As this Composite SKU is from my data layer into my business/service layer, I could adapt it to appear as a regular SKU probably similar how I would adapt it for the previous options. The pro's would be sticking to more of a single use per model paradigm, and less idle fields (i.e., fields that are only used when SKU is a parent). The con's would be having to adapt an object to appear as a SKU, or writing a lot of code to handle Composite SKU's everywhere in the UX where currently normal SKU's are handled. Another big con is data retrieval and searching. I would be pulling data from an extra table, having to merge it into my normal SKU's data.

I am really leanding towards 2 and 3, but #2 feels like a bit of an anti-pattern. Is a 1 to N relationship (parent.child1, parent.child2) an anti-pattern?

If it matters, I am using Entity Framework, MS-SQL, and C#. UX makes heavy use of Kendo grids (which I feel like is influencing my design too much possibly).

Answer 1

Why do you need two different classes? It sounds like an "ItemSku" is just a "Sku" with one item, and a "CompositeSku" is just a "Sku" with more than one item. If you want the "total price" then just sum all the items attached to the Sku.

public class Sku
{
    public List<SkuItem> Items { get; set; }

    public int TotalPrice
    {
        get
        {
            return Items.Sum(item => item.Price);
        }
    }
}

There is no need for separate classes unless they need different behavior (and no, calculating the total price is not "different").

There isn't a parent-child relationship. It's a many-to-many. Once SKU can reference multiple other SKUs, and every SKU can be referenced by multiple SKUs.

Answer 2

An industry term for this is "a product bundle". Basically, marketing price optimization technics.(To sells more by packaging related products). Usually,it gets its own SKU number. From design perceptive it is BOM (Bill of material) or network type parent/child relationship verses hierarchical parent/child relationship. Implemented with two tables. One table will represent all SKUs (including a bundle) other table will store two relationship from SKU table. One for the parent and other for the child.

Answer 3

A SKU in my mind is not actually an item. It is a type of identifier used to identify some kind of item, and can belong to different types of items. That would make it an attribute, not an entity.

So for example in a manufacturing environment, you might have Part (which is an ingredient in your burger world) and an Assembly (a meal). Part and Assembly would be modeled as different entities, but both would have a Sku attribute/column.

In addition, the SKU might not necessarily be used as the key. You may want to use a surrogate key for internal joins (i.e. in the cross-join table that allows you to connect assemblies with their parts). That way if the SKU ever needs to be modified, you don't have to cascade update all the identifiers in the other tables. Also, it paves the way if you ever need multiple SKUs per item (e.g. sometimes there is a manufacturer SKU and a retailer's SKU).

That does make it slightly more difficult to search all SKUs, since they could exist in more than one table. But that is preferable to forcing different types of items into the same table, which can have only one set of constraints and would not perfectly fit either entity. And it does make it more efficient if a user, for example, only wants to search for SKUs that represent assemblies.

So to answer your question-- first, model the entities and their relationships-- meal, ingredient, and M:M join table-- then figure out where the SKU attribute needs to go, bearing in mind it could end up in more than one place.

Answer 4

A composite sku is no longer a sku.

what you are describing is a 'Package'

ie. i stock i have

10 burgers cost 1.99
10 fries cost 0.50
10 shakes cost 0.99
 5 alternate supplier burgers cost 1.25

you can buy a package

meal1 = burger + fries + shake at 4.99
meal2 = burger + fries at 3.99
meal3 = 2*fries at 10.99

note that the prices are unrelated. also a burger in a package might be any one of a selection of skus. In this case the cheaper alternate supplier burger.