Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Implementing Table Inheritance in SQL Server

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2008-02-20 : 07:43:37

When designing a database, we sometimes come across situations where there are multiple types of entities that we are modeling, but we'd like them to all have certain attributes or relations in common. Using "sub-type" tables is a simple way to implement table inheritance in SQL Server.



Read Implementing Table Inheritance in SQL Server

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-20 : 08:18:04
good article!

i'd love it if you wouldn't be using the term inheritance though.
inheritance means knowing what the base class does and not getting a hold of its data.
maybe we need a new term for relational inheritance you're proposing...


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-20 : 09:12:22
this is inheritance where every member of base is public. or at least protected.

I don't see anything wrong with using the term though. there's no concept of access modifiers on columns like public/private so this is about as far as you could go with "inheritance" in sql server, it seems to me.

now you should write an article on "implementing polymorphism through views in sql server"


elsasoft.org
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-20 : 09:29:51
> implementing polymorphism through views in sql server

if you do that i'm quiting computer stuff...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

sorcerer
Starting Member

1 Post

Posted - 2008-02-21 : 00:07:13
Hi,
I have some newbie question here. In the People table, why did you create unique constraint instead of setting both PersonID and PersonTypeID as primary key?

Thanks
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-02-21 : 08:04:03
I know this is tangential to the main point of this article; however, I believe it is worth noting here. I've come to believe that Person is usually not the super-type you are after, rather it is Party. PartyTypes would be sub-typed by individuals or organizations. Individuals could be sub'ed by your Student, Teacher or Parent and Organization could'ed be sub'ed by department, team, PTA groups or whatever.

Jay
to here knows when
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-21 : 10:20:41
quote:
Originally posted by sorcerer

Hi,
I have some newbie question here. In the People table, why did you create unique constraint instead of setting both PersonID and PersonTypeID as primary key?

Thanks



Good question. You can do either or, it is really up to you. They technically are the same thing. I added a unique constraint since the primary key was already established and in place.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-21 : 10:22:42
quote:
Originally posted by Page47

I know this is tangential to the main point of this article; however, I believe it is worth noting here. I've come to believe that Person is usually not the super-type you are after, rather it is Party. PartyTypes would be sub-typed by individuals or organizations. Individuals could be sub'ed by your Student, Teacher or Parent and Organization could'ed be sub'ed by department, team, PTA groups or whatever.

Jay
to here knows when



Definitely a valid point. Of course, it all depends on what you are modeling. I thought after I wrote this that I should have picked a better example. A better one might be one I just implemented for a non-profit: We have general Contributions that all share the same data, but for certain contribution types extra data is needed. Thus, a base table of Contributions and sub-tables for those specific types.

But I was too lazy to change the example used in the article!

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

brackett
Starting Member

1 Post

Posted - 2008-02-21 : 14:25:09
quote:
Originally posted by jsmith8858

quote:
Originally posted by sorcerer

Hi,
I have some newbie question here. In the People table, why did you create unique constraint instead of setting both PersonID and PersonTypeID as primary key?

Thanks



Good question. You can do either or, it is really up to you. They technically are the same thing. I added a unique constraint since the primary key was already established and in place.

- Jeff
http://weblogs.sqlteam.com/JeffS




It really *wouldn't* be the same thing. A PK on (PersonId, PersonTypeId) would allow for duplicate PersonId's (as long as the PersonTypeId was different). That means you'd have to join on both PersonId and PersonTypeId from each of your subtype tables. Of course, you could get around that if you put a UC on PersonId...but then you're kind of back where you started.
BTW - I think the SubTypeId trick is pretty interesting - and actually solves the same problem in the original "uninherited" design. Good read!
Go to Top of Page

salvagedog
Starting Member

2 Posts

Posted - 2008-02-21 : 14:55:41
There is no new term needed for the technique described in this article, nor is it the writer's own proposal. This is standard super-typing and sub-typing, which is described by any good book on data modeling.

The person example is classic; the writer could not have picked a better one. Also, contrary to one assertion above, it would be extremely unusual to require a super-type above person. In the course of several hundred design projects, I've never seen the need to do that.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-21 : 14:57:08
quote:

.... Of course, you could get around that if you put a UC on PersonId...but then you're kind of back where you started.



yes, that's my point.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-21 : 16:24:25
quote:
Originally posted by salvagedog

Also, contrary to one assertion above, it would be extremely unusual to require a super-type above person. In the course of several hundred design projects, I've never seen the need to do that.



huh? what about Mammal? and above that, Vertebrate. then Organism. At the very top, the much revered Object type, base of all that is derived.




elsasoft.org
Go to Top of Page

salvagedog
Starting Member

2 Posts

Posted - 2008-02-21 : 20:53:09
quote:
Originally posted by jezemine

quote:
Originally posted by salvagedog

Also, contrary to one assertion above, it would be extremely unusual to require a super-type above person. In the course of several hundred design projects, I've never seen the need to do that.



huh? what about Mammal? and above that, Vertebrate. then Organism. At the very top, the much revered Object type, base of all that is derived.




elsasoft.org


In the scientific world, yes, point taken. But unusual for those of us designing business systems.
Go to Top of Page

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-03-10 : 06:50:52
quote:
The person example is classic; the writer could not have picked a better one. Also, contrary to one assertion above, it would be extremely unusual to require a super-type above person. In the course of several hundred design projects, I've never seen the need to do that.

I'm with Jay on this. In my experience, this is a pretty common requirement. Both individuals and organisations may make purchases, enter into legal agreements, etc.

Mark
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-03-11 : 16:15:39
quote:
Originally posted by salvagedog

There is no new term needed for the technique described in this article, nor is it the writer's own proposal. This is standard super-typing and sub-typing, which is described by any good book on data modeling.

The person example is classic; the writer could not have picked a better one. Also, contrary to one assertion above, it would be extremely unusual to require a super-type above person. In the course of several hundred design projects, I've never seen the need to do that.



Yeah, I beg to differ. The concept of a Party isn't new...

http://www.tdan.com/view-articles/5014
http://en.wikipedia.org/wiki/Data_modeling
http://www.dmreview.com/issues/20020701/5339-1.html (Search for Party ... it's in there)
And take a look at the ACORD standard in the financials space.

Don't confuse logical and physical modeling exercises. While I don't advocate a Party relation in every physical ERD, ignoring the concept when doing the logical modeling would be a huge mistake.

Jay

Jay
to here knows when
Go to Top of Page

conman
Starting Member

2 Posts

Posted - 2008-03-12 : 04:30:25
Thanks for this article - I have been trying to find information regarding these types of models for a while.

The thing is, I have a framework which I developed that manages the data layer - both physical and logical. It allows for the inheritance model that is in the article which I find very useful.

In some situations, my application's logical model requires a number of levels of inheritance. My question/concern deals is related to performance when querying database when you are dealing with many levels of inheritance (is there a limit before you start to notice a difference?). If anyone has any experience in this, it would be greatly appreciated to spread some light...

thanks
Go to Top of Page

TroyK
Starting Member

1 Post

Posted - 2008-03-12 : 12:10:24
Here's a link to my article which covers the same topic, but gives a little bit more background on the mathematical underpinnings of the technique: http://www.sqlservercentral.com/articles/Database+Design/61530/

TroyK
Go to Top of Page

conman
Starting Member

2 Posts

Posted - 2008-03-12 : 13:23:22
Many thanks bro!! much appreciated!
Go to Top of Page

UmarAlFarooq
Starting Member

1 Post

Posted - 2008-03-13 : 21:07:39
How about 3 bit type columns Parent, Student, Teacher. That way a person can be one or more. A teacher can have his/her kid in the same school so what would they be?

I think the multi-table design for this requirement is vastly overkill.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 21:15:27
UmarAlFarooq -- you didn't read the article very carefully did you?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-03-14 : 08:12:22
Overkill? Yeah, who cares about transitive dependencies anyway?

Jay
to here knows when
Go to Top of Page
    Next Page

- Advertisement -