SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Implementing Table Inheritance in SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/20/2008 :  07:43:37  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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

Slovenia
11750 Posts

Posted - 02/20/2008 :  08:18:04  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 02/20/2008 :  09:12:22  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Slovenia
11750 Posts

Posted - 02/20/2008 :  09:29:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
> 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 Posts

Posted - 02/21/2008 :  00:07:13  Show Profile  Visit sorcerer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/21/2008 :  08:04:03  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 02/21/2008 :  10:20:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 02/21/2008 :  10:22:42  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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 Posts

Posted - 02/21/2008 :  14:25:09  Show Profile  Reply with Quote
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 - 02/21/2008 :  14:55:41  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 02/21/2008 :  14:57:08  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 02/21/2008 :  16:24:25  Show Profile  Visit jezemine's Homepage  Reply with Quote
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 - 02/21/2008 :  20:53:09  Show Profile  Reply with Quote
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

United Kingdom
735 Posts

Posted - 03/10/2008 :  06:50:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 03/11/2008 :  16:15:39  Show Profile  Reply with Quote
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 - 03/12/2008 :  04:30:25  Show Profile  Reply with Quote
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 Posts

Posted - 03/12/2008 :  12:10:24  Show Profile  Reply with Quote
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 - 03/12/2008 :  13:23:22  Show Profile  Reply with Quote
Many thanks bro!! much appreciated!
Go to Top of Page

UmarAlFarooq
Starting Member

1 Posts

Posted - 03/13/2008 :  21:07:39  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 03/13/2008 :  21:15:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
UmarAlFarooq -- you didn't read the article very carefully did you?

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

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 03/14/2008 :  08:12:22  Show Profile  Reply with Quote
Overkill? Yeah, who cares about transitive dependencies anyway?

Jay
to here knows when
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000