May 30, 2013 at 10:13 am
Here's the situation:
I need to structure a DB to facilitate "relationship discovery" between people and business entities. Simply put it should show from data collected people who own businesses, are executives/employees of businesses, businesses that are subsidiaries of other businesses, businesses that are partners of other businesses, and people who are somehow 'related' (eg, blood relative, through marriage, business associates, friends, etc.) I just need a little validation on my current design and possibly some guidance on how to handle the relationships in queries.
current Design:
tblPerson
PersonID - PK (Identity Col)
FName
LName
...
tblBusEntity
BusEntityID - PK (Identity Col)
BusName
EIN (Tax ID)
Addr
...
tblPersToBus - Bridge entity for many to many
PersonID -PK - FK to tblPerson
BusEntID - PK - FK to tblBusEntity
RelType
tblPersToPers - Bridge entity for self join many to many
lfPersID - PK - FK to tblPerson.PersonID
rtPersID - PK - FK to tblPerson.PersonID
RelationType
tblBusToBus - Bridge entity for self join many to many
lfBusEntityID - PK - FK to tblBusEntity.BusEntityID
rtBusEntityID - PK - FK to tblBusEntity.BusEntityID
Reltype
From there I will use the bridge tables to store the "transaction" records that define the relationships. The query question I have is if I want to show a persons 'network' of relationships between other people and businesses or a business' relationship 'network' between people and other businesses how do I accomplish this? I figure I will have to use both the "lf..." and "rg..." sides of the relationship bridge entity tables to get multiple levels out from the central network point (a person or business I am interested in.)
Any help is greatly appreciated
October 25, 2013 at 5:47 am
I HAVE FOUND THE ANSWER! Wish I could take credit for figuring it out myself but sadly I cannot. The link below has a great explanation for these types of problems.
October 27, 2013 at 9:58 am
Thanks for posting the link. It helped me too!
October 27, 2013 at 7:44 pm
Not exactly a newbie question I'd venture to say. Some of those graphs examples are pretty deep stuff.
Beware of the recursive CTEs that he's using. They may produce unexpectedly poor performance if you've got lots of nodes to traverse. There are alternatives.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 28, 2013 at 5:50 am
Good point dwain. Can you post up those alternatives and the performance considerations for node traversal? (Or a link to other posts discussing these related topics)
October 28, 2013 at 6:01 am
Pretty much any rCTE can be written as a set-based WHILE loop and often the performance of the loop will beat the rCTE.
While not a node traversal, this article has an example of that happening.
http://www.sqlservercentral.com/articles/Bin+packing/94399/
It is all in how well you craft the WHILE loop.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply