I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server.
I found a recommendation for MySQL:
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?
The main problem is that
CatAccountDetails will have different columns from
DogAccountDetails. Cats have both different and far more properties than dogs in my application. Side note, 'cats' and 'dogs' represent three categories of website members in my current scenario.