2

I want to create a hierarchical object model in ASP.NET MVC, but I'm not sure what would be the best way to design database for this. I have a Product base class with certain properties like Title, Price, OnHandQty etc. I have several inherited classes like Book, which has extra properties like ISBN number, Author etc. Many of my products will fall under generic (base) Product class, but some products will fall under these derived classes (e.g. Book). I am not sure what is the best methodology to map this to database. Should I create separate tables for each product type (including one for generic product)? Or is there any better way?

Please note that I'm not really asking about OR mapping. I know how to create classes from DB tables using Entity Framework. But in this case I am confused about the database design itself.

Harsh Shah
  • 667
  • 5
  • 22
  • Similar question whose discussion you might find useful: http://stackoverflow.com/questions/561576/polymorphism-in-sql-database-tables – Jollymorphic May 27 '11 at 19:51

1 Answers1

1

If you are going to use Entity Framework then you should check out Inheritance with EF Code First by mortezam. He explains three strategies that can be used for representing an inheritance hierarchy:

  1. Table per Hierarchy (TPH): Enable polymorphism by denormalizing the SQL schema, and utilize a type discriminator column that holds type information.
  2. Table per Type (TPT): Represent "is a" (inheritance) relationships as "has a" (foreign key) relationships.
  3. Table per Concrete class (TPC): Discard polymorphism and inheritance relationships completely from the SQL schema.

The idea (with Code First) is that you define your classes and inheritance and let the framework create the database for you. That way you don't need to worry so much about the database design.

You might also want to think about using an Object Database or one of the NoSQL storage strategies like Mongo DB which work better than relational databases when you have these kind of 'jagged' classes.

Dan Diplo
  • 25,076
  • 4
  • 67
  • 89