Entity Framework inheritance: TPT, TPH or none?

Question

I am currently reading about the possibility about using inheritance with Entity Framework. Sometimes I use a approch to type data records and I am not sure if I would use TPT or TPH or none...

For example... I have a ecommerce shop which adds shipping, billing, and delivery address

I have a address table:

RecordID
AddressTypeID
Street
ZipCode
City
Country

and a table AddressType

RecordID
AddressTypeDescription

The table design differs to the gerneral design when people show off TPT or TPH... Does it make sense to think about inheritance an when having a approach like this..

I hope it makes sense...

Thanks for any help...

Answer 1

When considering how to represent inheritance in the database, you need to consider a few things.

If you have many different sub classes you can have a lot of extra joins in queries involving those more complex types which can hurt performance. One big advantage of TPH is that you query one table for all types in the hierarchy and this is a boon for performance, particularly for larger hierarchies. For this reason i tend to favour that approach in most scenarioes

However, TPH means that you can no longer have NOT NULL fields for sub types as all fields for all types are in a single table, pushing the responsibility for data integrity towards your application. Although this may sound horrible in practice i haven't found this to be too big a restriction.

However i would tend to use TPT if there were a lot of fields for each type and that the number of types in the hierarchy was likely to be small, meaning that performance was not so much of an issue with the joins, and you get better data integrity.

Note that one of the advantages of EF and other ORMs is that you can change your mind down the track without impacting your application so the decision doesn't need to be completely carved in stone.

In your example, it doesn't appear to have an inheritance relationship, it looks like a one to many from the address type to the addresses

This would be represented between your classes something like the following:

Address.AddressType
AddressType.Addresses

Answer 2

As Keith hints, this article suggests TPT in EF scales horribly, but I haven't tried it myself.