1

I'm considering creating a class table inheritance schema as described here: http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

However my reason for wanting this seems different than what most people are after. Most people use this pattern for sharing columns which are common to distinct types e.g. putting a FirstName column in a common Person table rather than duplicating it in both the Employee and Customer tables.

But in my case, there wouldn't actually be any shared columns except for the key. I just want a common key to be used across each of my child tables such that it could be referenced as a foreign key. For example, if I want each person to have one or more phone numbers, I could just have a PhoneNumber table which references the ID from the common Person table; I don't want to have separate EmployeePhoneNumber and CustomerPhoneNumber tables.

Given that there are no shared columns (except for the key), is class table inheritance the right pattern to use? Or is there another pattern which does what I need better?

[EDIT]

After some more Googling, it looks like Polymorphic Assoctiation is the proper term for what I want and it is often implemented in this fashion. I'll leave this question open for a bit longer in case anyone has anything to say about this.

jjoelson
  • 5,771
  • 5
  • 31
  • 51
  • IMO if there are no shared columns, I can't think of any benefit to using class inheritance. And what, two people can't share the same phone number? – Tab Alleman Sep 29 '14 at 18:16
  • @TabAlleman Well the key is the one shared column. Sure the PhoneNumbers table could be many to many; class table inheritance still allows us to avoid having separate CustomerPhoneNumber and EmployeePhoneNumber many-to-many tables in this case. Instead of using class table inheritance to share columns, I'm using it to enable polymorphism. The question is whether a better pattern exists for this. – jjoelson Sep 29 '14 at 18:23

0 Answers0