SQL, MVC, Entity Framework
I am using the aforementioned technologies and ran into what I believe is the design problem I did.
I have a table of artworks in my database and I can add art (now I think of them as digital products) to my shopping cart + CartLine table. I have a system that adds art to galleries and user accounts, etc. Works great.
Now the client wants to sell T-shirts, mugs and pens, etc., "HardwareProducts", so I created the "HardwareProducts" table.
Now I have two different product types in two tables. I am using GUID as PC in both 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 problem is the database won't know which table to reference when I cast the LineItem object through my ORM to the interface.
In OOP I can see how you would have a base Product class and then a DigitalProduct class and a HardwareProduct class coming out of it, but how do you model it in SQL Server and Entity Framework, or is there another way
EDIT:
This is what I have in my test app at the moment, thanks to the comments below. The trick that did it for me was using an ORM simulator as pointed out by Stefan. This led me to this excellent article.
alt text http://img411.imageshack.us/img411/3568/32654541.jpg
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'm going to integrate it into my main solution and let you know if I have more results, but I think it looks good. NB It looks like the "Type" column mentioned is not really needed, in the end it was a pleasant surprise thanks to the clean solution provided by the ORM. thanks all
a source to share
This is a very cool scenario to show one feature of EntityFramework!
You can have one table product and a type column that defines the product type. Then in your Entity Data Model, you define the base Product entity and create 2 derived entities HardwareProduct and ArtProduct. You can add a condition to the mapping for your child like this:
EDIT: You should read when ProductTypeID = 1, but I'm too lazy to redo the screenshot right now;)
See "When ProductTypeID = 1"
There :)
a source to share
This is bad database design. You should have a base table with the identifier and common functions of all product and extension tables with functionality specific to different product types.
Example:
Product: ID, Description, UnitPrice, ProductType (artwork or hardware)
Work: ProductID, Year,
HardwareProduct Size : ProductID, other functions.
In the shopping cart you store the ProductID and quantity.
Since there may be as many product categories as possible, you should consider storing the design tables for the product category and another table that stores its values.
And a comment on your OOP solution. Having a DigitalProduct and a HardwareProduct class might be interesting in the beginning, but items in a store can have so many different functions that you can't translate them into different classes. The handle is color, the t-shirt is the size, the mug has capacity, so when you start thinking, there are more differences between a mug and a t-shirt, and then a cover and a t-shirt. So why is the mug in the same place with the T-shirt and artwork somewhere else?
You should definitely take a look at some open source ecommerce solution and see how it's done. Your client may want to start selling other types of products, and you need to think about developing a more versatile solution. Adding other classes will not be efficient.
a source to share