SQL, MVC, Entity Framework

Question

Hi

Im using the above technologies and have ran into what I presume is a design issue I have made.

I have an Artwork table in my DB and have been able to add art (I now think of these as Digital Products) to a shopping cart + CartLine table fine. The system I have that adds art to galleries and user accounts etc works fine.

Now the client wants to sell T-shirts, Mugs and Pens etc, 'HardwareProducts' so I have created a 'HardwareProducts' table.

Now I have two different product types in two tables. I use GUID's as the PK's in both the HardwareProducts table and Artwork table. When a customer adds an item to their cart I store the GUID in the ProductID column in the CartItems table.

The issue is the database will not know which table to reference when I bring the LineItem object up through my ORM to the front end.

In OOP I can see how you would have a base class of Product, and then a DigitalProduct class and HardwareProduct class drived from it, but how do you model this in SQL Server and the Entity Framework please, or is there another way?

EDIT:

This is what I have in a test app at the moment thanks to the below comments. The trick that did it for me was utilising the ORM simular to what Stephane pointed out. It lead me to this excellent article.

Allows:

int prodCount = _entities.Product.OfType<ArtWork>().Count();
IEnumerable<LineItem> lineItem = _entities.LineItem.Include("Product");
int artWorkCount = lineItem.Select(p => p.Product).OfType<ArtWork>().Count();

ArtWork prod = new ArtWork();
            prod.Price = 2;
            prod.ProductName = "atlast";
            prod.Downloads = 3;
            prod.GalleryID = 1;
            _entities.AddToProduct(prod);
            _entities.SaveChanges();

I am about to integrate it into my main solution and will let you know if I have anymore findings, but I think all is looking good. NB It appears the Type column which was mentioned, was not actually needed in the end which was a nice surprise thanks to the clean solution the ORM provided. Thx all

Answer 1

That is poor database design. You should have on base table with ID and common features of every product and extension tables with features specific for different types of product.

Example:

Product : ID, Description, UnitPrice, ProductType(Artwork or Hardware)
Artwork : ProductID, Year, Size
HardwareProduct: ProductID, other features.

In cart you store ProductID and quantity.

Since there can be propably more categories of product, you should think about table storing parameters specific to product category and another table that stores its values.

And a comment to your OOP solution. Having DigitalProduct and HardwareProduct class may be interesting at the beginning, but items in store can have so much different features, that you will not be able to translate it to different classes. Pen has color, t-shirt has size, mug has capacity, so when you start to think, there is more difference between mug and t-shirt, then between artwork and t-shirt. So why is mug in the same place with t-shirt and artwork somewhere else?

You should definitely look at some open source e-commerce solution and look how it is done there. Your client may want to start selling other types of product and you have to think about designing more universal solution. Adding another classes will not be efficient.

Answer 2

This is a very cool scenario to show off one feature of EntityFramework!

You could have a single table product, and a type column that defines the type of product. Then in your Entity Data Model you define a base entity Product, and you create 2 derived entities HardwareProduct and ArtProduct. You can add a Condition on the Mapping for your child Entity like so :

EDIT : You should read When ProductTypeID = 1, but I'm lazy to redo the screenshot now ;)

See the "When ProductTypeID = 1 "

There :)