Code-First TPT, TPC, or incorrect inheritance?

Question

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

Answer 1

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.