I wish to produce a hierarchical object model in ASP.Internet MVC, but I am unsure an amount be the easiest method to design database with this. I've got a Product base class with certain qualities like Title, Cost, OnHandQty etc. I've several inherited classes like Book, that has extra qualities like ISBN number, Author etc. A lot of my items will come under generic (base) Product class, however, many items will come under these derived classes (e.g. Book). I don't know what's the best methodology to map this to database. Must I create separate tables for every product type (including one for generic product)? Or perhaps is there much better way?
Please be aware that I am not necessarily asking about OR mapping. I understand how to produce classes from DB tables using Entity Framework. However in this situation I'm unclear about the database design itself.
If you are planning to make use of Entity Framework then you can examine out Inheritance with EF Code First by mortezam. He describes three methods you can use for representing an inheritance hierarchy:
- Table per Hierarchy (TPH): Enable polymorphism by denormalizing the SQL schema, and use a type discriminator column that holds type information.
- Table per Type (TPT): Represent "is a" (inheritance) associations as "includes aInch (foreign key) associations.
- Table per Concrete class (TPC): Discard polymorphism and inheritance associations directly from the SQL schema.
The concept (with Code First) is you define your classes and inheritance and allow the framework produce the database for you personally. This way you don't have to worry a lot concerning the database design.
You could also wish to consider utilizing an Object Database or among the NoSQL storage methods like Mongo DB which are more effective than relational databases if you have these type of 'jagged' classes.