Re: Relationship(s) for human family structure

On Aug 17, 10:29 am, woger..._at_jqpx37.cotse.net wrote:
> I have a database with individuals in it, and I'd like to store their
> 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
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

http://www.fht.byu.edu

(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 stepsibling  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 Received on Tue Aug 21 2007 - 18:21:03 CEST