March 30, 2012 at 12:02 pm
Quick Please 🙂
Please tell me what key phrases will lead me to more information about why some tables contain FKs that reference PK in the same table. Is this a parent/child relationship for recursive queries? Is this common in data warehouses?
-
March 30, 2012 at 12:32 pm
Most likely its a parent/child relationship. I can't think of any other reason to do such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 12:35 pm
we have that in a few tables;
it's usually something like an Entity and need to to identify the "prime" entity,
an example might be a table of bank names, and which one is the "main" branch, for example.
Lowell
March 30, 2012 at 12:43 pm
Very common in an adjacency list.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 12:44 pm
Lowell (3/30/2012)
we have that in a few tables;it's usually something like an Entity and need to to identify the "prime" entity,
an example might be a table of bank names, and which one is the "main" branch, for example.
same here. master sub relation ships are common in our database and we use this technique. keeps things organized and consistent and prevents subs with no master.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 30, 2012 at 1:08 pm
Sure try the key phrase "foreign key". You act as if foreign keys to the same table are somehow mysteriously different from foreign keys to other tables, but they're not. Foreign keys indicate that there is a relationship between one record and some unique record. The fact they point to the same table derives from the fact that the objects in the relationship are the same type. Nothing magical or mysterious about it.
So your question really boils down to what kind of unique relationships involve the same types of objects. That's simply too broad to answer, but some examples include: employee/supervisor; spouses; and debit/credit (in some cases).
Note that potentially non-unique relationships, even if they involve the same types of objects, cannot use a foreign key to the same table. So siblings would require a secondary table with foreign keys to the main table.
Instead of trying to figure out what kinds of relationships a same-table foreign key can express, take the relationships that you need to express and figure out how to set up the foreign keys to express it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 30, 2012 at 1:37 pm
Search for t-sql Common Table Expression recursion
in google. All the info you need!
March 30, 2012 at 1:47 pm
aitchkcandoo (3/30/2012)
Quick Please 🙂Please tell me what key phrases will lead me to more information about why some tables contain FKs that reference PK in the same table. Is this a parent/child relationship for recursive queries? Is this common in data warehouses?
I've seen this in some very de-normalized dwh table where a child table includes a group (lets say) of three codes (like code1, code2, code3) pointing to the same CODES table. As always, problem is that one day you are going to need a fourth code, then... kaboom!
=================================================
EDIT: Sorry, I misred the question. You talking about self-referencing tables.
=================================================
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 30, 2012 at 1:54 pm
This can be kind of common...
If you have a table 'person'
in which you store Father and Mother who points on an other row in the Table.
There is a lot of application to this.
March 30, 2012 at 1:58 pm
a.jean89 (3/30/2012)
This can be kind of common...If you have a table 'person'
in which you store Father and Mother who points on an other row in the Table.
There is a lot of application to this.
and lots of other Master entity Sub entity relation ships. as stated above in several posts.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 30, 2012 at 3:23 pm
Heh... a Rose, by any other name, is still a Rose.
Unless I'm mistaken, all of the examples above all boil down to some form of parent/child relationship.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 4:54 pm
Jeff Moden (3/30/2012)
Heh... a Rose, by any other name, is still a Rose.Unless I'm mistaken, all of the examples above all boil down to some form of parent/child relationship.
I'm not sure if you responding to anyone in particular, but I believe you are mistaken. A parent/child relationship indicates a one-to-many relationship with the parent being the one side and the child being the many side, but you can also have one-to-one relationships. The most obvious is spouses.
You can't just view a one-to-one relationship as a degenerate one-to-many relationship, because a one-to-one relationship is typically reciprocal and one-to-many relationships are typically not.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 31, 2012 at 1:28 pm
drew.allen (3/30/2012)
Jeff Moden (3/30/2012)
Heh... a Rose, by any other name, is still a Rose.Unless I'm mistaken, all of the examples above all boil down to some form of parent/child relationship.
I'm not sure if you responding to anyone in particular, but I believe you are mistaken. A parent/child relationship indicates a one-to-many relationship with the parent being the one side and the child being the many side, but you can also have one-to-one relationships. The most obvious is spouses.
You can't just view a one-to-one relationship as a degenerate one-to-many relationship, because a one-to-one relationship is typically reciprocal and one-to-many relationships are typically not.
Drew
Heh... well, maybe not mistaken but I certainly do have a different point of view on what a "parent/child" relationship is. :hehe:
I agree that the direction of the relationships you've cited is "typical" but "typical" doesn't mean "never" nor even "infrequent". Of course, you already know that but allow me to continue so that you begin to understand the tangled mess known as "Jeff's brain".
Take a "net" for an airline flight schedule. Any city can be either the parent for outgoing flights to many children or the child for incoming flights from many parents. If an airline has just two service cities, one is the parent of the flight and the other is the child depending on which direction the plane is flying in. Even the marriage example given is a "parent/child" relationship in my mind because if you're looking for "the spouse of John" you have to first find "John" to find "Sally" so, technically, she's a "child" of that lookup process. And for a coffee machine "tree", the "parent" cup had better be in place before the "children" ingredients get there or you've just watered the "roots" of the person operating it. Heh... of course, you could say that the actual ground coffee is the "parent" because you wouldn't need any of the other stuff if it weren't for the coffee itself. It depends on whether you're looking at the process or the nodes themselves.
Sure, there are fancy terms like ancestor/descendent, superior/subordinate, and dozens of other combinations available from the thesaurus but things with relationships between nodes are still the "rose" that can be solved by "parent/child" algorithms. It's just like cars, trucks, and buses. By definition, they're all "automobiles" yet people call them by different names and have come to think of "automobiles" as "passenger cars". I think people have also come to think of actual nodes as "parents" and "children" because a very "typical" use of trees and hierarchies are DAGs (Directed Acyclic Graphs) where it's logical and even helpful to think of nodes in such a fashion. My take on the whole thing is the process. To exemplify and without implying any limits, when working with "cyclic graphs" or "nets" such as the flight patterns of airlines, the node you're currently using to find other related nodes is the current "parent" and everything connected to it is a "child" of the process unless the edge connecting the two is defined as mono-directional which could, by definition in some requirements, be a parent (which works very well for a version of the "Traveling Salesman Problem" I'm working on, by the way).
Back on subject and call it what you will... Can anyone think of a reason for having a "loop-back FK" that isn't there to support and constrain some kind of "parent/child" relationship in any of its various forms?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2012 at 11:06 pm
Jeff Moden (3/31/2012)
Back on subject and call it what you will... Can anyone think of a reason for having a "loop-back FK" that isn't there to support and constrain some kind of "parent/child" relationship in any of its various forms?
After reading and rereading your post several times to make sure I understood your definitions and terminology, I tried, and no, I could not think of a reason. It may be because I agree with your assertion though, and Drew may not.
Parent-child, as well as ancestor-descendent and superior-subordinate, are just manifestations of a more generic concept. If we break this down further and accept that at a given time all participants in a logical relationship must fall into one of two categories, either referenced or referencing, then we would arrive at the same conclusion, that process [sic] is relevant. As it relates specifically to a one-to-one relationship we will have a form of a parent-child relationship where roles are defined by the context in which we are being asked to find one of the two participants. This is also to say that each participant could play the other's role, e.g. we can find John via his spouse Sally (John is the referenced) or just as easily reverse the referenced and referencing roles when asked to find Sally via John.
I too find it helpful to think of nodes as parents when constructing queries to find related rows, regardless of whether its one-to-one, or some other type of relationship. This might be why I get brain-hurt when trying to read queries that make use of RIGHT JOINS 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply