Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

My question is: based on the business need below, should I use Table-Per-Type, Table-Per-Class/Concrete-Type, or am I misusing inheritance in the first place? If TPT, will the query performance become unsustainable? This is my first time experimenting with Entity Framework, and after doing a lot of research, I have some concerns about table structure/queries/future performance.

Let's say I have a web app that orders documents. A convenient example might be an eBay-like marketplace where a person can either buy or sell items. A person can call customer service and request an official document of purchase history, sales history, or a combination document with both purchase and sales history. (So, for whatever reason, a customer service rep is generating the document rather than the person himself.)

I have a generic base class for the Document:

[Table("DocumentRequest")]
public class DocumentRequest
{
    public int DocRequestID { get; set; }
    public string RequestedBy { get; set; }
    public string AccountNumber { get; set; }
    public int FaxNumber { get; set; }
}

Under the logic of "Purchase History is a Document," I thought that inheritance was the way to go. Table-Per-Type seemed like a perfect fit to keep the DocumentRequests together while containing the specialized fields in a normalized subtype table.

The PurchaseHistory has a collection of purchases that the customer service rep has selected to include.

[Table("Document_PurchaseHistory")]
public class PurchaseHistory : DocumentRequest
{
    public ICollection<SelectedPurchases> Purchases { get; set; }
    public bool ChargesDisputed { get; set; }
    [...other hypothetical specialized fields....]
}

Similarly, SalesHistory is associated with a collection of sales that the customer service rep has selected to include based on whatever hypothetical criteria.

[Table("Document_SalesHistory")]
public class SalesHistory : DocumentRequest
{
    public ICollection<SelectedSales> Sales { get; set; }
    public bool NotarizeNeeded { get; set; }
    [...other hypothetical specialized fields....]
}

And there's the combination document:

[Table("Document_AccountSummary")]
public class AccountSummary : DocumentRequest
{
    public ICollection<SelectedSales> Sales { get; set; }
    public ICollection<SelectedPurchases> Purchases { get; set; }
    public bool ChargesDisputed { get; set; }
    public bool NotarizeNeeded { get; set; }
    [...other hypothetical specialized fields....]
}

I'm using EntityFramework 4.1 and I've been reading that TPT causes significant problems with the SQL it generates, even in version 5; would those problems apply to this simple inheritance? TPC has its own problems and seems like a messier approach, but I'm not sure. Or should I just skip the polymorphism, or am I maybe using inheritance wrong in the first place? Thanks for taking the time to read this, any and all tips are very appreciated.


Ok.... it's been a few days and the number of views has slowed down....

For now I'm going forward with TPT, because I feel that the normalization it provides is important and convenient. In an attempt to combat future problems with query complexity, I'm including a DocumentType descriminator in the generic base class:

[Table("DocumentRequest")]
public class DocumentRequest
{
    public int DocRequestID { get; set; }
    public string RequestedBy { get; set; }
    public string AccountNumber { get; set; }
    public string FaxNumber { get; set; }
    public DocumentType DocumentType { get; set; }
}

public class DocumentType 
{
    public int ID { get; set; }
    public string DocumentName { get; set; }
}

If I'm wrong, right, or if there are any fundamental problems -- I'd still very much like to know!

Cheers

share|improve this question

migrated from stackoverflow.com Feb 1 '13 at 17:28

This question came from our site for professional and enthusiast programmers.

1  
A little bit off-topic, but..... You shouldn't store the fax number as an integer, it should be a string (varchar in the database). The same applies to other fields such as phone numbers, postal codes etc. If you're not going to do any arithmetic calculation on a field (sum, average, ...) then you should store it as a string. –  Rui Jarimba Jan 30 '13 at 18:36
    
Never a bad time for good advice, thanks. –  RJB Jan 30 '13 at 18:38
    
It's a good question and a great fit here. A good answer would need someone with a good grasp of how inheritance works in the Entity Framework: TPT, TPC, contravariance / covariance, and how easy it is do queries without needing a DocumentType field (which is very bad practice in OOP). –  Quentin Pradet Feb 15 '13 at 9:18

2 Answers 2

up vote 2 down vote accepted

Designing DB structure based on principles of OOP (applied to mapped entities) is usually not a good approach since it introduces complexities that significantly slow down DB performance. Even with ORMs you still need to think carefully about common DB usage patterns, potential DB size and payload, indexes and joins...

In your specific case AccountSummary contains definitions of both PurchaseHistory and SalesHistory, and it suggests that you may use just one table for all these entities with enumeration field (DocumentType) specifying what kind of DocumentRequest you are dealing with. Yes, it will denormalize the DB structure, but it may well be explained by performance gains (you don't have to join tables each time you want to read the entity).

I'm not an expert in Entity Framework, but I believe you can define mappings so that the class hierarchy is mapped to one table based on DocumentType enum (leaving unused fields as null), otherwise just create a single class DocumentRequest that maps 1:1 to DB table and add checks to prevent incorrect object configuration.

share|improve this answer

I am just working on a project where they designed a database based on OOP. Lots of queries needs to be joined. Performance is slow. When I was posted on this project I thought I missed something in my years of relational database design experience. Now I am diving in this technology I have come to the conclusion I missed nothing. If you design a database OOP style the chances are great you forget a lot about indexes, performance etc. and end up with a non-performing database. Because you hide your database with OOP its not transparant anymore how this database works. So my lesson from this project was to stick with my old-school ERD's nothing wrong with that. They perform very well :-)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.