Relationship(s) for human family structure
woge...@jqpx37.cotse.net
kin relationships also.
A quick, clumsy way of doing it would be to have three columns: two
for individuals, and the third expressing their relationship (e.g. 'a
is sibling of b', 'a is parent of b', etc).
But that's clumsy and has redundancies.
Does anyone know of a clean, efficient way to store this information?
(I assume it would involve modelling a tree or graph.)
TIA
Neo
Below is an example script to do the above with dbd, a lightweight,
memory-resident db which stores data as a network of nodes where each
node is equivalent to an AND gate. It removes redundancies
automatically.
(new 'john 'person)
(new 'mary 'person)
(new 'bob 'person)
(new 'wife 'relationship)
(new 'husband 'relationship)
(new 'brother 'relationship)
(new 'sister 'relationship)
(set john wife mary)
(set mary husband john)
(set mary brother bob)
(set bob sister mary)
(; Get john's wife's brother)
(; Following expression gets bob)
(get (get john wife *) brother *)
For related example, see www.dbfordummies.com/example/ex120.asp
Neo
> kin relationships also.
> A quick, clumsy way of doing it would be to have three columns: two
> for individuals, and the third expressing their relationship (e.g. 'a
> is sibling of b', 'a is parent of b', etc).
How about the following?
T_Person
ID Name
1 john
2 mary
3 bob
T_Relation
ID Name
1 husband
2 wife
3 brother
4 sister
T_Relationship
P1_ID Rel_ID P2_ID
->john ->wife ->mary
->mary ->husband ->john
->mary ->brother ->bob
->bob ->sister ->mary
Query to find name of john's wife's brother? Something similar to
below:
SELECT name FROM T_Person WHERE ID = (
SELECT P2_ID FROM T_Relationship
WHERE
Person1 = (SELECT P2_ID FROM T_Relationship
WHERE P1_ID= (SELECT ID FROM T_Person WHERE name =
"john")
AND (SELECT ID FROM T_Relation WHERE name="wife") )
AND Rel_ID = (SELECT ID FROM T_Relation WHERE name="brother"))
Rob
You have described one of the biggest problems w. relational database
management as currently practiced. Your requirement for a "kinship
relationship" is not unlike many relationships wherein an entity
participates in many different relationships (I have a father, a
mother, etc.) and in some cases, multiple instances of the same
relations (I have 2 brothers, etc.) Further, when there are two (or
more) related kin like father and mother, they may be related
(husband, wife, or spouse) or not (divorced), and that
2nd relationship has bearing on the 1st.
The LDS (Mormon) Church is attempting to build a global, Google-grade
family history database and holds an annual workshop. See
(The LDS kinship database effort is further complicated by polygamous
family histories.)
In short, there is no known "clean, efficient way to store this
information" in the relational model that satisfies all constraints.
If you have a highly restricted universe (no divorce or remarriage),
you might be able to solve your representation problem relationally.
For example, suppose you have 5 tuples in 1 (person) or 2 (parent,
child) relations corresponding to father "A" and childs (children!)
B,C,D and E. Suppose A is father to all 4 childs B,C,D and E by two
marriages: B and C offspring of the first marriage, D and E offspring
of the second.
How can you represent the parent-child relationships so that the step-
sibling relationship is preservered? (I.e., so that B and E have the
same father, but are not siblings.)
Here's another example (attributed to Mark Twain): I was married and
had a son. He married and had a daughter. My wife died. I married my
son's daughter. Now I am my own grandfather.
Try resolving that relationally!
Rob
Tegiri Nenashi
> In short, there is no known "clean, efficient way to store this
> information" in the relational model that satisfies all constraints.
> If you have a highly restricted universe (no divorce or remarriage),
> you might be able to solve your representation problem relationally.
There are 2 relations, which can be organized as trees:
Biological_Mother( parent, child )
Biological_Father( parent, child )
That is all to it. You can derive siblings informations from these
base tables. You can introduce extra relations, like
Marriage( individual1, individual2 )
but the assertion that "no known clean, efficient way to store this
information in the relational model" is clearly BS.
Neo
> He [bob] married and had a daughter [sue].
> My wife [mary] died.
> I [john] married my son's [bob] daughter [sue].
> Now I [john] am my own grandfather.
> Try resolving that relationally!
Below dbd script models and resolves above. To verify, copy and paste
into dbd's input box and click submit button. Gender neutral
relationships (ie parent, spouse) are used. (Note: check parent in
view menu beforehand, to display parent relationships. By default
relationships that move up hierarchy are disabled)
(new 'male 'gender)
(new 'female 'gender)
(new 'john 'person)
(set john gender male)
(new 'mary 'person)
(set mary gender female)
(new 'bob 'person)
(set bob gender male)
(new 'sue 'person)
(set sue gender female)
(; Note: parent already defined in db)
(new 'spouse 'relationship)
(set john spouse mary)
(set mary spouse john)
(set bob parent john)
(set sue parent bob)
(set john spouse sue)
(set sue spouse john)
(; Get john's grandfather via wife's side of family)
(; Gets john himself!)
(and (get (and (get (get john spouse *) parent *)
(get * gender male)) parent *)
(get * gender male))
DBD MINI-TUTORIAL:
Unlike the CODASYL network data model which is infact a hybrid
relational/hierarchal data model, dbd represents things with a network
of nodes where each node implements the fundamental unit of
computation: the AND gate or a switch. A user models a thing by
creating a node and relating it to existing nodes in db. Each db is
ntialized with nodes for basic things such as symbols and common
classes. Besides GUI/API, a user communicates with the db via
expressions. The general syntax of an expression is "(func [inp1]
[inp2] ...)". Each expression begins with a "(" and ends with
corresponding ")". The first element after "(" is the function. The
renaming elements, if any, are function inputs. An element of an
expression can itself be a subexpression. Expressions starting with
"(;" are comments. The expression "(new)" creates a new node in the
db. The expression "(it)" refers to the last node created by "(new)".
To relate a node, say to a string (sequence of symbols), use the
expression "(set (it) name 'john)". Note that a string is indicated by
preceeding it with a single quote. Once a node has been named by a
string, it can be referenced by its name. See following example:
(; Create a node for a person to be named by the string 'john)
(new)
(; Relate new node to its name, the string 'john)
(set (it) name 'john)
(; We can now reference new node by simply by john)
(; Now we want to make john an instance of person)
(; Create new node for person)
(new)
(; Relate its name as the string 'person)
(set (it) name 'person)
(; In order to view item in GUI, relate new class to tree root)
(set db item person)
(; Set john is an instance of person)
(set person instance john)
(; The following expression is equivalent to that above)
(; The subexpressions are solved for *,
prior to executing the main expression)
(set (get * name 'person) instance (get * name 'john))
(; create a person/doctor named mary using shortcut method)
(; Note that the new function's
input1 is the name of the new node
and remaining inputs classify it)
(new 'mary 'person 'doctor)
For more details, see www.dbfordummies.com
Neo
> child) relations corresponding to father "A" and childs (children!)
> B,C,D and E. Suppose A is father to all 4 childs B,C,D and E by two
> marriages: B and C offspring of the first marriage, D and E offspring
> of the second.
> How can you represent the parent-child relationships so that the step-
> sibling relationship is preservered? (I.e., so that B and E have the
> same father, but are not siblings.)
Below dbd script models above and queries for B's biological siblings.
(new 'male 'gender)
(new 'female 'gender)
(new 'arnold 'person)
(set arnold gender male)
(; arnold's first marriage)
(new 'sally 'person)
(set sally gender female)
(new 'spouse 'relationship)
(set arnold spouse sally)
(set sally spouse arnold)
(; arnold's children from 1st marriage)
(new 'billy 'person)
(new 'charlie 'person)
(set billy parent arnold)
(set charlie parent arnold)
(set billy parent sally)
(set charlie parent sally)
(; arnold's second marriage)
(new 'tina 'person)
(set tina gender female)
(set arnold spouse tina)
(set tina spouse arnold)
(; arnold's children from 2nd marriage)
(new 'dean 'person)
(new 'elizabeth 'person)
(set dean parent arnold)
(set elizabeth parent arnold)
(set dean parent tina)
(set elizabeth parent tina)
(; Get billy's silbings)
(; Gets charlie, dean, elizabeth and charlie again)
(!= (get * parent (get billy parent *)) billy)
(; Get billy's biological silbings)
(; Gets charlie)
(!= (and (get * parent (and (get billy parent *)
(get * gender male)))
(get * parent (and (get billy parent *)
(get * gender female))))
billy)
For related thread, see "Network Example: Sibling of Opposite Gender"
JOG
Aye, if it can be stated as a proposition, it can be stored in a
relational database. However, I think we might concede that the
variations in recursive querying between different RDBMS
implementations (and the non-existence of such a mechanism in some)
can be a hindrance.
Neo
True, but some set of propositions can't be stored in as systematic a
manner (ie without NULLs and redundancy) as with a node-based database
like dbd. Try to represent the following 3 propositions involving
three persons:
john hates john
mary knows (john hates john)
sue knows (john hates john)
For a bonus, include the following:
bob knows (john hates)
-CELKO-
The Mormons do. They will send you software that matches their
classification system and gives you access to their databases. They
include incest and polygamy because they have to for their religion to
work.
One of their beliefs is that you can give baptism to the dead and get
all of your ancestors into heaven as a Mormon. But you have to be able
to identify them via family trees. They have saved Hitler, but try
not to mention it in the press. And they have also offended many
Orthodox Jews who consider any non-Jewish ceremony to be desecration
of a corpse.