How to normalize / refactor e-commerce tables to Pocos when the data is much like EAV.

Question

In our e-commerce application, we have different kinds of products having different kinds of attributes, e.g., a shoe has different selling features than an edible cake. Our e-commerce site allows you to "build" your own products, selecting various attributes and values (like pink frosting on a cup cake which adds $.25 to the cost).

Our first construction looked something like this. This is a NIGHTMARE to maintain. I am looking for suggestions on how to assemble these tables (or objects) in a more normalized and realistic way.

We have a pretty standard product table with more fields than this but you get the idea:

public class Product {
    public int      id     {get;set;}
    public string   name   {get;set;}
    public decimal  price  {get;set;}
}

We have an options table that lists all the options that can be selected for a given option group (think of a single Drop Down List):

public class Options {
    public int      oId     {get;set;}
    public string   oName   {get;set;}
    public decimal  oPrice  {get;set;}  // Added to base price if selected
}

Then we have a many-to-many table tying both of these together, where the presence of a record ties an option to that product:

public class ProductOptions {
    public int     id       {get;set;}
    public string  oId      {get;set;}
}

We are trying to assign similar options to similar products, like all Cakes generally have the same flavors, all icing generally has the same colors, etc. so that when we change options, it changes for all related products.

Any suggestions will be most appreciated, especially in a DDD way.

Answer 1

From your description it sounds like a Product is a member of one or more ProductGroups and those ProductGroups can have options that apply to all the products in them.

So I'd add a ProductGroup table, add a ProductGroupOptions relationship table so you can apply an option to a ProductGroup, and a ProductGroupProduct relationship table so you can define which Products are in which ProductGroups.

e.g. Cake X is a member of ProductGroup "Cakes" which can have flavors A, B, C

Maybe you also need to group ProductOptions too so you can apply a set of product options to a ProductGroup or Product as a package.

e.g. Cake X is a member of ProductGroup "Cakes" which uses ProductOptionsGroup "Flavors" which includes "A", "B", "C".

You'll need to decide whether to also allow direct non-Grouped relationships between Products and flavors or whether it is in fact simpler to say that all interactions now happen through groups and if you need a single Product -> Option connection you just create a Group for each and accept that it only has one member.

Overall then you'd have something like:-

A Product belongs to one or more ProductGroups

A ProductOption belongs to one or more ProductOptionGroups

ProductGroups have many-many relationships with ProductOptionGroups

So in LINQ you could just do something like Product.ProductGroups.SelectMany(gp => gp.ProductOptionGroups.SelectMany(pog => pog.ProductOptions)) to get all of the options for a given product.