Class Table Inheritance: do child tables need a primary key

database-designdomain-modelinheritanceorm

I'm using the Class Table Inheritance architecture with my application and have read Fowler. When mapping my database tables to classes in code, I've realized I have no need for an "ID" property on my derived classes, and, in fact, it can cause issues if it has the same name because it hides the base class property.

Do my child tables need to have their own surrogate primary keys, or should the foreign keys relating them to their parent record also serve as the primary key for the child records? Fowler doesn't get that specific on CTI. I'm used to always having surrogate keys, so a departure from this convention doesn't feel quite right and if there is a good reason to stick to it, I'll take it.

Best Answer

Yes in general when using Table per Class Inheritance, the derived table has the same primary key as the base table.

I personally prefer table per-hierarchy when possible because it doesn't require an extra join. It does create unused columns in the table which might be cumbersome with large hierarchies, but many modern databases support this scenario adequately (e.g. SQL Server as of 2008 has support for a Sparse Column indicator that lets the DB optimize storage accordingly)

Related Topic