| 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
 |
|
|
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 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-20 : 09:29:51
|
> implementing polymorphism through views in sql serverif you do that i'm quiting computer stuff...  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
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 |
|
|
|
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.Jayto here knows when |
|
|
|
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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
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.Jayto 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!- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
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.- Jeffhttp://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! |
|
|
|
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. |
|
|
|
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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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/5014http://en.wikipedia.org/wiki/Data_modelinghttp://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.JayJayto here knows when |
|
|
|
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 |
|
|
|
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 |
|
|
|
conman
Starting Member
2 Posts |
Posted - 2008-03-12 : 13:23:22
|
| Many thanks bro!! much appreciated! |
|
|
|
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. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-03-14 : 08:12:22
|
| Overkill? Yeah, who cares about transitive dependencies anyway?Jayto here knows when |
|
|
|
Next Page
|
Article: Implementing Table Inheritance in SQL Server