Sure there are. Just because you don't attach any importance to the
cross relationships and don't ever query to find them doesn't mean that
they're not there. In an org chart you could easily say that two
employees under equal managers in a department are "cousins". With my
model you have something like this:
Say you want to find out who my female cousins on my mother's side are.
Assuming you have a mother and a father field (my owners) for each
person, you need only to analyze the relationships like this:
{pseudo-code}
Select information from the peopleTable where gender is female and
mother or father is in (select mother and father from the peopleTable
where person is in a list of people whose parents are my mother's
parents)
{/pseudo-code}
************************************************************************
***
Sorry for the super long email. It's only 2 examples, but I kept
everything separated to show how I see the relationships working. I've
got a lot at hand, as I've been working on something that uses a similar
structure...
************************************************************************
***
Obviously, the code would be cleaned up and the queries
combined/optimized; you'd never have all these separate queries by
themselves, but I've kept them separate to illustrate my point:
TABLE STRUCTURE (roughly)
-------------------------------------------------------
|personid | momID | dadID | name | gender | dob | ... |
-------------------------------------------------------
<!---Assuming you have the person in ?'s id and have already gotten his
parents' IDs (piqMomID) and the entire row of data for him --->
<!--- get person in ?'s grandparents --->
<cfquery name="grandparents" datasource="#application.dsn#">
Select momID, dadID from peeps where personID = #piqMomID#
</cfquery>
<!--- using above query, get aunts/uncles... --->
<cfquery name="auntsAndUncles" datasource="#application.dsn#">
Select personid from peeps
where momID = grandparents.momid or dadID = grandparents.dadid
</cfquery>
<cfset auntsAndUnclesList = quotedValueList(auntsAndUncles.personid)
<!--- use aunts and uncles query to get female cousins --->
<cfquery name="femaleCousins" datasource="#application.dsn#">
Select * from peeps
where personid in (#auntsAndUnclesList#)
</cfquery>
<!--- show all female cousins --->
<cfdump var="#femaleCousins#">
Not good enough eh? How about if you want to show all my father's
cousins who ended up having their own children:
<!--- we have my father's id from my data record :: assume we've set it
to mydadsid --->
<cfquery name="myDadsParents" datasource="#application.dsn#">
Select momID, dadID from peeps where personID = #mydadsid#
</cfquery>
<cfset myDadsParents = mydadsparents.momid>
<cfset myDadsParents = listappend(mydadsparents, mydadsparents.dadid)
<!--- get my dad's grandparents --->
<cfquery name="myDadsGrandParents" datasource="#application.dsn#">
Select momID, dadID from peeps
where personID = mydadsparents.momID or dadID =
mydadsparents.dadid
</cfquery>
<!--- using above query, get aunts/uncles... --->
<cfquery name="myDadsAuntsAndUncles" datasource="#application.dsn#">
Select personid from peeps
where momID = myDadsGrandparents.momid or dadID =
myDadsGrandparents.dadid
</cfquery>
<cfset myDadsAuntsAndUnclesList =
quotedValueList(myDadsAuntsAndUncles.personid)
<!--- use aunts and uncles query to get female cousins --->
<cfquery name="mydadscousins" datasource="#application.dsn#">
Select * from peeps
where personid in (#myDadsAuntsAndUnclesList#)
</cfquery>
<cfset myDadsCousinsList = quotedvaluelist(mydadscousins.personid)>
<!--- get a list of all people who are moms --->
<cfquery name="getAllMoms" datasource="#application.dsn#">
Select distinct momid From peeps
</cfquery>
<cfquery name="getOnlyCousinsWhoAreParents"
datasource="#application.dsn#">
Select * from peeps
Where personid in (#myDadsCousinsList#)
and (
personid in (Select distinct momid From peeps)
OR
personid in (Select distinct dadid From peeps)
)
</cfquery>
<!--- display list of my father's cousins who ended up having kids of
their own --->
<cfdump var="#getOnlyCousinsWhoAreParents#">
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Jake McKee
Sent: Tuesday, March 01, 2005 8:54 AM
To: [email protected]
Subject: Re: Family Tree?
Yeah, except that there aren't cross relationships in an org chart. It's
really just up and down, not side to side. There are rare exceptions and
dotted lines, of course. But on a family tree, the part (at least in my
mind) that makes it tricky is the fact that there are 4 directions of
solid, constant relationships.
Jake
Ken Ferguson wrote:
>Think of it in another way: a family tree is just an org chart. There
>are tons of apps out there that present org charts. Grandparents may be
>the equivalent of executives, parents of managers, children of
>workers... It's also very similar to thinking of products within
>subcategories within categories within product lines.
>
>Like Clint says, it's just a matter of getting the relationships
>straight in the db and figuring out the hierarchy from there. I would
do
>something like linking every person's record with his owner or parent.
>So if I select the family tree for Ferguson, I'm going to start with
>someone at the top and I'll be able to get all of the people with
>him/her (or a couple) as their owner. You could have two "owner" fields
>to get mom and dad... It should be simple enough to follow that query
>down to little Lauren who, as the youngest Ferguson, has nobody calling
>her the owner. I would fall easily in line with my Brother, as we've
the
>same two owners... It's also now very simple to see, specifically who
my
>cousins are by querying for a list of people whose owners have the same
>owners as my owners (this would be much easier on a whiteboard).
>
>Do you get where I'm coming from???
>--Ferg
>
>
>
>
>-----Original Message-----
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>Behalf Of Clint Tredway
>Sent: Tuesday, March 01, 2005 8:26 AM
>To: [email protected]
>Subject: Re: Family Tree?
>
>this is done by setting up the people relationships correctly. I can
>think of 2 ways off the top of my head on how to do this, but it
>depends on your SQL knowledge as to which one I would use.
>
>The most basic way would be to have a main Person table and a linking
>table PersonPerson table. The linking table can store the link between
>people and the type of relationship (uncle,cousin,friend,etc)...
>
>The more advanced way would be to have a vertical table where all
>people are stored and you would loop through the table to get the
>relationships...
>
>Granted this is an high level desc but you should see what I am talking
>about.
>
>On Tue, 01 Mar 2005 08:12:48 -0600, Jake McKee <[EMAIL PROTECTED]>
>wrote:
>
>
>>All,
>>
>>Has anyone seen a CF app that allows you to build your family tree?
>>
>>If not, how would you go about structuring a database to allow an
>>application to build a family tree, and show the connections between
>>people... meaning how can I show that Joe is not only my cousin, but
>>Sue's uncle, and Jimmy's son... etc.?
>>
>>Thanks!
>>Jake
>>
>>----------------------------------------------------------
>>To post, send email to [email protected]
>>To unsubscribe:
>> http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
>>To subscribe:
>> http://www.dfwcfug.org/form_MemberRegistration.cfm
>>
>>
>>
>>
>
>
>
>
----------------------------------------------------------
To post, send email to [email protected]
To unsubscribe:
http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
http://www.dfwcfug.org/form_MemberRegistration.cfm
----------------------------------------------------------
To post, send email to [email protected]
To unsubscribe:
http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
http://www.dfwcfug.org/form_MemberRegistration.cfm
