I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server.
I found a recommendation for MySQL here (How do I map an IS-A relationship into a database?). Basically, it is:
- use identical primary keys between the parent table (Account) and the subtype tables (CatAccountDetails, DogAccountDetails)
- add a subtype discriminator column to the parent table and the subtype tables
- include both the shared primary keys and subtype columns in a foreign key constraint from subtype to parent type tables
Adding a type column that is constrained to a single value (e.g. a 'C' column in CatAccountDetails) feels just a little hackish, so I wondered if there is a feature in SQL Server to enable this scenario?
Also, if this is the way to do it, should I define something additionally to prevent this 'unused' column from degrading performance when my ORM (Entity Framework) requests a table join, like create an additional foreign key on just the PK?