Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

Thanks!

share|improve this question
1  
The "type" column can be defined as a computed (but constant) column in SQL-Server. I think it has to be PERSISTED though so it can participate in the Foreign Key constraint. – ypercube Mar 24 at 10:23
Is the main problem that CatAccountDetails will have different columns from DogAccountDetails? – Aaron Bertrand Mar 24 at 14:25
Yes, Aaron, thanks for asking. 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. – shannon Mar 25 at 15:24
I'm about to implement this as a persisted column. I hope that approach is compatible with EF. Also, I still wonder if SQL will recognize that it has an index available when merely passed an ID by EF on the derived class table, for example. I can think of a lot of lame examples where I'm suspicious the performance will break. – shannon Mar 26 at 2:48

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.