What are the pros and cons of one master base table which all entities inherit from?

Question

I am using Entity Framework Database first approach to create my domain model.

I am considering creating a base table EntityBase with the base properties in:

 PK
 CreatedDate
 CreatedBy
 ModifiedDate
 ModifiedBy

 etc.

Using Table Per Type inheritance I will end up with a single table in the Database linked to all other entity tables:

EntityBase {
 EntityBase_PK => Identity PK
 CreatedDate
 CreatedBy
 ModifiedDate
 ModifiedBy
}

DerivedEntity1 {
 DerivedEntity1_PK => FK relationship to EntityBase on EntityBase_PK
 Property1 
 ...etc
}

DerivedEntity2 {
 DerivedEntity2_PK => FK relationship to EntityBase on EntityBase_PK
 Property2 
 ...etc
}

...etc

I believe this will work fine with Entity Framework, but I am concerned if this is good design from a Database point of view.

The obvious benefit I can see is that I get a unique PK for all entities across the whole database, but I am concerned that having every update hit the EntityBase table could be a performance concern as well as have impact with respect to table locking.

Thoughts?

Answer 1

The biggest problem will be performance. What you described is called TPT inheritance and until .NET 4.5 with next version of EF is out this will be the scenario with very inefficient queries.

In case of ObjectContext API it has one additional cons. You cannot have ObjectSet of derived type so you will end with single object set for all entities.

If you want nice OOP concepts define interface with your fields and implement that in all your entities but do not bind your entities with inheritance which span both conceptual and storage model. Even TPC is not very nice solution because it still requires unique primary keys among all tables => it leads to guids.

Answer 2

OO concepts rarely translate over to relational databases. As a DBA, I have spent more time cleaning up attempts to shoehorn OO concepts into a relational database than anything else (and not because I'm a purist, but because they didn't work.)

If you want unique PKs across all entities, look at guids (or sequential guids)

It's not going to hurt anything for those columns (CreatedDate, ModifiedDate etc) to be in each entity table (in fact, that's correct design) You will, however, see terrible query plans as you join back to your base table again and again.

In short, I'd advise against it.