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).