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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

surfandswim
Starting Member

USA
4 Posts

Posted - 04/10/2008 :  17:02:20  Show Profile  Reply with Quote
Interesting article you've got there. We implement a structure like this in our product, Project Insight. We use classes in C# from a common object called "BusinessObject" which is the base that all classes inherit from. Each sub-class is a layer of the onion and has it's own properties and correlates to the same base table.

So for example, we have a class called ItemBase which has a table ItemBase which has a GUID, and tha is the Id of the BusinessObject class. There are procedures for loading the ItemBase data, which has the Id of itself, the Id of it's parent, and the type of item that it is, and other base properties, name, description etc. A subclass, say "File" inherits from "ItemBase" and we have a table File that correlates to that, with it's own set of procedures for load, create, delete, and save (and other loads). Each layer of the class handles the data that is unique to that layer, and each layer is responsible for loading itself, saving itself etc. They all share the same GUID id though.

We've implemented this structure very successfully using inheritence in C# as the model, and just used a similar strucuture in the database using standard relational database modeling to accomplish this.

What would be interesting, is if SQL Server could do this by default, without views, have the ability to automatically join the tables through the CLR by simply setting up the relationships ahead of time.

I know this would be alot to ask, but anyway, this structure that we setup works well for us. Of course that more complex the structure the more issues that need to be ironed out, but we've been using this structure for a long time so that helps us.

Go to Top of Page

Jemshaid
Starting Member

United Kingdom
1 Posts

Posted - 04/16/2008 :  21:14:32  Show Profile  Reply with Quote
very helpfull.just need a bit of help. in my project there is no personTypeId in peoples table and i can not alter the peoples table.
but i can create new tables and can change parent,student,teacher tables.
How can i detect the person(in my case loggedin user) is student,teacher or parent. do i have to search personID in these three tables one by one to get the person type. or there is any other way.

Go to Top of Page

Thrasy
Starting Member

USA
2 Posts

Posted - 06/03/2008 :  13:40:27  Show Profile  Reply with Quote
really?

I saw this "person is a person is a person" approach blow up an order entry application (not of my design, I was cleaning up the mess) in a very busy call center.

There was a lot of contention and blocking on the base "person" table because every new person was going in there no matter what kind of person they were.

I get nervous every time I hear OOP terminology in conjunction with a RDBMS.

you are the sum of your record collection

Edited by - Thrasy on 06/03/2008 13:42:31
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/03/2008 :  13:45:10  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:

really?

I saw this "person is a person is a person" approach blow up an order entry application (not of my design, I was cleaning up the mess) in a very busy call center.

There was a lot of contention and blocking on the base "person" table because every new person was going in there no matter their person.

I get nervous every time I hear OOP terminology in conjunction with a RDBMS.



Without any more details or a clear explanation, it is really impossible to guess what the true issue was. I doubt it was because of sub-classing a "Person" table. If you can provide details or examples to show otherwise, that would be really helpful. A poor implementation of a technique does not mean that the technique itself is poor, right?



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

Thrasy
Starting Member

USA
2 Posts

Posted - 06/03/2008 :  15:03:08  Show Profile  Reply with Quote
It's no skin off of my nose. I did not build it.

I am sorry but I have no code laying around. This was a couple of clients/projects/years ago.

But just from a conceptual level, do you not see where this could make the base "person" table a bottleneck in a busy OLTP system?

Personally I get a little squeamish every time some clever guy tries to remake the RDBMS with OOP principles.

EDIT: poor implimentation? that project lead was MSCS. Take it up with Bill.

Edited by - Thrasy on 06/03/2008 15:13:37
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/03/2008 :  18:31:44  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:

But just from a conceptual level, do you not see where this could make the base "person" table a bottleneck in a busy OLTP system?



Not really, no more so than a Customer table or a User table or the busy transaction tables or lookup tables or any other table(s) that is accessed frequently.

quote:

Personally I get a little squeamish every time some clever guy tries to remake the RDBMS with OOP principles.



Me, too. Sounds like that's what happened to the project you worked on. Luckily, in this case, there's nothing clever going on and no one is trying to remake a RDBMS with OOP principles; this is pretty standard, fairly simple and straight-forward stuff. The only time "OOP" principles is used is by using the name "inheritance" as a general label; this has nothing to do with implementing classes or anything in a relational database.

quote:

poor implimentation? that project lead was MSCS. Take it up with Bill.



Being certified has nothing to do with writing good code or designing good databases or applications. Never blame the tools, or the techniques, or even Bill Gates; blame the person who is actually responsible -- the programmer.



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

Edited by - jsmith8858 on 06/03/2008 18:33:49
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 06/04/2008 :  13:18:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by jsmith8858

blame the person who is actually responsible -- the programmer.



That's my daily mantra!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/04/2008 :  13:30:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Did I say "developer"? I meant DBA. It's *always* the DBA's fault!

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

mateia
Starting Member

1 Posts

Posted - 02/04/2009 :  10:03:55  Show Profile  Reply with Quote
Great article.

The danger to avoid is to allow a Student to have as PersonID another kind of Person (e.g. a Teacher) !

For this reason, the absolutely necessary trick is indeed to set the FK as PersonID + PersonTypeID !

create table Students
(
PersonID int primary key,
PersonTypeID as 1 persisted, -- student
EnrollmentDate datetime,
foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID)
)


Edited by - mateia on 02/04/2009 10:41:07
Go to Top of Page

gregwmay
Starting Member

Australia
2 Posts

Posted - 04/10/2009 :  23:01:51  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




You may need to use unique constraints if you have multiple levels of "inheritance". For example: Person-Teacher-Employee and Person-Teacher-Temp (for want of a better example). In this case the Teacher table has a parent (Person) and two children (Employee and Temp).

I found it best to have ID the only primary key on all three tables, but then to have unique constraints (one at the Person-Teacher level, the other at the Teacher-Employee and Teacher-Temp levels).

This still prevents you from adding, say, a Student to the Temp table.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/13/2009 :  10:32:59  Show Profile  Reply with Quote
I personally can't stand this one-to-one-or-zero sub-table approach. I too have seen some pretty messed up and inefficient databases created this way, including one that I am dealing with right now. The overhead of maintaining the relational integrity is unnecessarily burdensome. I see absolutely no need for this type of design given the more efficient data storage of today's database engines.
No one-to-one tables, please...give me null columns any day of the week.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

TungTh
Starting Member

1 Posts

Posted - 09/20/2011 :  12:33:16  Show Profile  Reply with Quote
Thank you for the very helpful article.

I have a related problem with this implementation that I want to have a different auto increased ID range for Teacher, Student...

How can we deal with it?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 09/20/2011 :  13:11:29  Show Profile  Visit robvolk's Homepage  Reply with Quote
Why do you need different ID ranges? If they're internally generated then there's no genuine significance to their values. There's no easy way to do such a thing using this structure, and it actually contradicts the intent of table inheritance. Either use separate tables for Student, Teacher, etc., or add a separate column for your special ID range and calculate that ID in a procedure, trigger or user-defined function.
Go to Top of Page

apurice
Starting Member

USA
4 Posts

Posted - 02/27/2012 :  13:44:33  Show Profile  Reply with Quote
Thank you so much for the article! It has solved a bunch of problems I was about to panic. :D This article added a kind of 3rd dimension to my DB designs. Its amazing. :)

I have a problem though... I meticulously followed the instructions, I'm on SQL 9 (2005). And I'm getting the following situation
INSERT INTO dbo.Services (ID, Name)
SELECT 2, 'Business Lines, Long Distance and Broadband'

-----
Command(s) completed successfully.



alter table dbo.BusinessLines add ServiceID AS 2 persisted
GO

-----
Command(s) completed successfully.



alter table Orders add constraint Orders_AltPK unique (ID, ServiceID)
GO

-----
Command(s) completed successfully.



alter table BusinessLines add foreign key (OrderID, ServiceID) references Orders(ID, ServiceID)
GO

-----
Msg 1778, Level 16, State 0, Line 4
Column 'Orders.ServiceID' is not the same data type as referencing column 'BusinessLines.ServiceID' in foreign key 'FK__BusinessLines__4F12BBB9'.
Msg 1750, Level 16, State 0, Line 4
Could not create constraint. See previous errors.


Seems like SQL 2005 is complaining about creation a foreign key relation between an int type column and a computed type column, which is a kind of strange, because BOL says that its OK.

Can somebody help me to figure out where to dig...?

Edited by - apurice on 02/27/2012 13:45:30
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 02/27/2012 :  14:02:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
Try this:

alter table dbo.BusinessLines add ServiceID AS cast(2 as int) persisted
Go to Top of Page

apurice
Starting Member

USA
4 Posts

Posted - 02/27/2012 :  16:21:05  Show Profile  Reply with Quote
Neah, it didn't work... Thanks though.

Interesting thing. I just copy/pasted DDL statements from the article into the Studio and ran them. Everything worked perfectly. Now it seems like I may not create a FK to a computed column once tables have been created.
Go to Top of Page

thiscode
Starting Member

1 Posts

Posted - 11/24/2013 :  09:01:53  Show Profile  Reply with Quote
quote:
Originally posted by brackett

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!



Why should an Unique on (PersonId, PersonTypeId) avoid a duplicate PersonId, as long as the PersonTypeId is different?

I think it *is* the same thing. And if you implement the "Identifying Relationship" Pattern you *have to* use PK instead of Unique.
Go to Top of Page

du-it
Starting Member

3 Posts

Posted - 05/27/2014 :  05:23:01  Show Profile  Reply with Quote
An interesting part would be how certain enumerated values could be inherited from tha parent (table). For instance, a Person has a status which may be one of VALUE_1, VALUE_2.
A child table C1 can have either the values from the parent table or additionally one of VALUE_3, VALUE_4, hence, VALUE_1, VALUE_2, VALUE_3, VALUE_4 are valid...but only in the child table C1.
In a child table C2 only the additional values VALUE_5, VALUE_6 are valid, hence, VALUE_1, VALUE_2, VALUE_5, VALUE_6 are valid...but only in the child table C2.

Is this possible? How?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.2 seconds. Powered By: Snitz Forums 2000