| Author |
 Topic  |
|
surfandswim
Starting Member
USA
4 Posts |
 Posted - 04/10/2008 : 17:02:20
|
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.
|
 |
|
|
Jemshaid
Starting Member
United Kingdom
1 Posts |
Posted - 04/16/2008 : 21:14:32
|
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.
|
|
|
|
Thrasy
Starting Member
USA
2 Posts |
Posted - 06/03/2008 : 13:40:27
|
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 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 06/03/2008 : 13:45:10
|
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
|
|
|
|
Thrasy
Starting Member
USA
2 Posts |
Posted - 06/03/2008 : 15:03:08
|
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 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 06/03/2008 : 18:31:44
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
|
|
mateia
Starting Member
1 Posts |
Posted - 02/04/2009 : 10:03:55
|
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 |
|
|
|
gregwmay
Starting Member
Australia
2 Posts |
Posted - 04/10/2009 : 23:01:51
|
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. |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 04/13/2009 : 10:32:59
|
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.
________________________________________________ |
|
|
|
TungTh
Starting Member
1 Posts |
Posted - 09/20/2011 : 12:33:16
|
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? |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 09/20/2011 : 13:11:29
|
| 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. |
|
|
|
apurice
Starting Member
USA
4 Posts |
Posted - 02/27/2012 : 13:44:33
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 02/27/2012 : 14:02:55
|
Try this:
alter table dbo.BusinessLines add ServiceID AS cast(2 as int) persisted |
|
|
|
apurice
Starting Member
USA
4 Posts |
Posted - 02/27/2012 : 16:21:05
|
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. |
|
|
|
Topic |
|
Article: Implementing Table Inheritance in SQL Server
