| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-13 : 10:19:26
|
| sean writes "i am a new user of sql server and was wondering how i would show inheritance in the design of the tables in my database. eg i have a user table which has common fields that need to be linked to 2 other tables employee and customer. i want these two tables to inherit the common fields without having to retype in the fields.can this be done?thankssean" |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-13 : 13:44:19
|
| NoMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-13 : 13:51:30
|
| why would you want the same data in 3 separate tables? Do some searches on normalization to learn how you can organize your data more efficiently in the database.-ec |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-13 : 14:49:33
|
| can this be done? Strictly speaking, no, databases have no concept of inheritence. Are you trying to design a persistence layer for an object oriented middle tier? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-13 : 14:54:08
|
| What are you guys talking about...it's not automatic, but a trigger would do....But yeah...I'm against the redundancy thing....All you need to do is a join......Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-07-13 : 15:05:49
|
| you need to rethink your approach in relational data terms as opposed to OOP. your 3 entities (user, customer, employee) should indeed share common data, and you should indeed not have redunancy. The way to handle this in a RDMS is to establish relationships between those 3 entities.if you wish to say "a user is an employee", then you would first set up a table called Employees with a primary key (say, EmpID). Then, you would then establish a 1-1 relationship between Employee's and user's by making the primary key of the users table relate to the EmpID of the employee table. this way, you cannot create a user unless you first create an employee (and establish all of the relevant info in that table).- Jeff |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-13 : 18:31:41
|
| What are you guys talking about...it's not automatic, but a trigger would do....Well, ghee....that would be even more typing. :)The answer is still NO to the actual question as he asked it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-13 : 19:22:17
|
| I agree with jsmith8858, sean. While it may seem convenient to look at relational models as analogous to inheritance models, they are in fact quite different in theory, behavior, and practice. A relationship in a relational model doesn't confer any meaning to the table on the other end of the relationship, in the way that an inheritence relationship does. The tbl_Customers table is not able to be treated in any polymorphic sense as a tbl_Persons table, just because it has a foreign key referencing the tbl_Persons table. All that means is that there is a relationship, much like a containing relationship in your domain models. IS A 'is a' fallacy in the relational world. However, that doesn't mean that there is no concept of reuse in the relational model. Reuse can be implemented as a foreign key relationship whereby the Customers table implements a foreign key constraint referencing the Persons table. But you still have to tie the two together in a join in order to retrieve ALL the relevent columns for a Customer, because querying the Customer table won't automatically return all the 'inherited' tables, because there is no inheritence. YOU have to specify which foreign key relationships are representative of an inheritence relationship, or in other words, which other tables to you have to join the the Customers table in order to produce enough data for a complete Customer 'object'. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-14 : 09:35:46
|
quote:
Originally posted by AskSQLTeam
i want these two tables to inherit the common fields without having to retype in the fields.
OK Derrick....that's the questionquote:
can this be done?
Yes with TRIGGERS....SHOULD it be done?OK Derrick...now it's time to chime in....Brett8-) |
|
|
|
|
sql server and inheritance