So here's a question I've been trying to wrap my mind around for a
bit.

If I were to try and store an organic (disordered) hierarchy in a
database what would be the best starting point and how would one find
the optimum storage arrangement.
Here are a couple of examples.

Imagine if you will, storing the genealogy for a culture where a
person could have multiple mothers and fathers in addition to multiple
siblings.
My initial answer would be a unique identifier for each person and
then a separate column for each type of relationship, i.e. Mothers,
Fathers, Brothers, Sisters, Aunts, Uncles, Grandparents etc.
Is there a better way?

How about a social networking database, what would that look like?
Again my initial thought is a unique identifier per account, and then
a column for each parent (each person above you), and each child
(those who were invited by you) and each friend (those with whom
you've found a common interest.

A final example would be a user defined grouping system. For instance
a user we'll call him Joe creates a group called Blue Team, and adds
his colleagues to that group. While another user we'll call Jane
creates a group called downsized and adds the Blue Team along with
other groups. How would a person on the Blue team know that they are
now a part of the downsized group as well?

Thanks in advance for the answers folks!

Sincerely,

On Mar 15, 3:04*am, 2DevOrNot2Dev <inquisit... (AT) mailinator (DOT) com> wrote:
Okay I think I spent more time on this than I should have. But I
wanted to play with the Hierarchical query. Here's a possible solution
in Oracle. Please ignore the weird naming convention I adopted. This
is just a quick and dirty design. (It definitely is NOT a final
design.)

SQL> /****
This may actually be a simple design I think.
Consider the user defined grouping system.
You have these entities: USERS and GROUPS
Users have names and other info. Groups have names and other info.
Give them each a surrogate for the primary key (mainly because we are
doing
this quick and dirty).
I added a small rule: each user is their own group. You see why
below.

You have a hierarchy entity, let's call it GROUP_GROUPS,
representing groups that belong to a higher group.
This includes the PK from the groups table twice,
once as the group identifier (the parent group),
and once as the member identifier.
The groups have groups as members, that's why users are their own
group.

****/
SQL> DROP table broup_broups
Table dropped.
SQL> drop table broups
Table dropped.
SQL> drop table busers
Table dropped.
SQL> create table busers (
bname varchar2(30),
bid number(10) primary key
)
Table created.
SQL> insert into busers values( 'ADMIN', 100)
1 row created.
SQL> -- a placeholder user
SQL> insert into busers values( 'ed', 101)
1 row created.
SQL> insert into busers values( 'fred', 102)
1 row created.
SQL> insert into busers values( 'jed', 103)
1 row created.
SQL> insert into busers values( 'ted', 104)
1 row created.
SQL> insert into busers values( 'joe', 105)
1 row created.
SQL> insert into busers values( 'moe', 106)
1 row created.
SQL> insert into busers values( 'toe', 107)
1 row created.
SQL> insert into busers values( 'michelle', 108)
1 row created.
SQL> insert into busers values( 'jane', 109)
1 row created.
SQL> commit work
Commit complete.
SQL> create table broups (
brame varchar2(15),
brid number(10) primary key,
ownerid number(10) references busers(bid)
)
Table created.
SQL> -- all users are their own group
SQL> insert into broups values ('ADMIN',110,100)
1 row created.
SQL> insert into broups values( 'ed', 111, 101)
1 row created.
SQL> insert into broups values( 'fred', 112, 102)
1 row created.
SQL> insert into broups values( 'jed', 113, 103)
1 row created.
SQL> insert into broups values( 'ted', 114, 104)
1 row created.
SQL> insert into broups values( 'joe', 115, 105)
1 row created.
SQL> insert into broups values( 'moe', 116, 106)
1 row created.
SQL> insert into broups values( 'toe', 117, 107)
1 row created.
SQL> insert into broups values( 'michelle', 118, 108)
1 row created.
SQL> insert into broups values( 'jane', 119, 109)
1 row created.
SQL> --- > a user we'll call him Joe creates a group called Blue Team,
SQL> insert into broups values ('Blue Team',120, 105)
1 row created.
SQL> --- another group
SQL> insert into broups values ('group ED',121, 101)
1 row created.
SQL> --- While another user we'll call Jane
SQL> ---> creates a group called downsized
SQL> insert into broups values ('downsized',122, 109)
1 row created.
SQL> --- a couple more groups
SQL> insert into broups values ('girls', 123, 100)
1 row created.
SQL> insert into broups values ('yellow haired', 124, 108)
1 row created.
SQL> --- groups have groups as members
SQL> --- that's why users are their own group
SQL> create table broup_broups (
brid_grp number(10),--- references broups(brid),
brid_mbr number(10),--- references broups(brid),
PRIMARY KEY (brid_grp, brid_mbr)
)
Table created.
SQL> -- the group ED is created
SQL> insert into broup_broups values (121,113)
1 row created.
SQL> insert into broup_broups values (121,114)
1 row created.
SQL> insert into broup_broups values (121,112)
1 row created.
SQL> insert into broup_broups values (121,111)
1 row created.
SQL> -- the girls group...
SQL> insert into broup_broups values (123,118)
1 row created.
SQL> insert into broup_broups values (123,119)
1 row created.
SQL> --- > a user we'll call him Joe creates a group called Blue Team,
and adds
SQL> ---> his colleagues to that group.
SQL> insert into broup_broups values (120,113)
1 row created.
SQL> insert into broup_broups values (120,114)
1 row created.
SQL> insert into broup_broups values (120,115)
1 row created.
SQL> -- Joe himself
SQL> insert into broup_broups values (120,116)
1 row created.
SQL> insert into broup_broups values (120,117)
1 row created.
SQL> insert into broup_broups values (120,123)
1 row created.
SQL> -- and the girls group.
SQL> -- and who's blond is it's own group
SQL> insert into broup_broups values (124,118)
1 row created.
SQL> insert into broup_broups values (124,116)
1 row created.
SQL> insert into broup_broups values (124,114)
1 row created.
SQL> --- While another user we'll call Jane
SQL> ---> creates a group called downsized and adds the Blue Team
along with
SQL> ---> other groups.
SQL> insert into broup_broups values (122,119)
1 row created.
SQL> -- jane is a member
SQL> insert into broup_broups values (122,120)
1 row created.
SQL> -- Blue team member of downsized
SQL> insert into broup_broups values (122,121)
1 row created.
SQL> -- group Ed member of downsized
SQL> insert into broup_broups values (122,123)
1 row created.
SQL> -- girls group member of downsize
SQL> COMMIT WORK
Commit complete.
SQL> select bb.BRID_MBR , b.BRAME, bb.BRID_GRP, c.BRAME
from broup_broups bb, broups b, broups c
WHERE bb.BRID_MBR = b.BRID
AND bb.BRID_GRP = c.BRID

BRID_MBR BRAME BRID_GRP BRAME_1
---------- --------------- ---------- ---------------
113 jed 120 Blue Team
114 ted 120 Blue Team
115 joe 120 Blue Team
116 moe 120 Blue Team
117 toe 120 Blue Team
123 girls 120 Blue Team
111 ed 121 group ED
112 fred 121 group ED
113 jed 121 group ED
114 ted 121 group ED
119 jane 122 downsized
120 Blue Team 122 downsized
121 group ED 122 downsized
123 girls 122 downsized
118 michelle 123 girls
119 jane 123 girls
114 ted 124 yellow haired
116 moe 124 yellow haired
118 michelle 124 yellow haired


19 rows selected.
SQL> --- Your last question refers actually to how to form the query:
SQL> ---> How would a person on the Blue team know that they are
SQL> ---> now a part of the downsized group as well?


SQL> ---- groups that Blue team is member of:
SQL> select level, c.BRAME, bb.* ,b.BRAME
from broup_broups bb, broups b, broups c
where b.BRAME = 'Blue Team' AND bb.BRID_MBR=b.BRID
AND bb.BRID_GRP = c.BRID
connect by prior bb.BRID_MBR = bb.BRID_GRP

LEVEL BRAME BRID_GRP BRID_MBR BRAME_1
---------- --------------- ---------- ---------- ---------------
1 downsized 122 120 Blue Team


1 row selected.
SQL> --- OR to rephrase: show all members of the "downsized" group
SQL> select level, c.BRAME, bb.* ,b.BRAME
from broup_broups bb
, broups b
, broups c
where
bb.BRID_GRP = c.BRID
AND bb.BRID_MBR=b.BRID
start with bb.BRID_GRP = 122 --- the downsized group ID.
connect by prior bb.BRID_MBR = bb.BRID_GRP
order by level

LEVEL BRAME BRID_GRP BRID_MBR BRAME_1
---------- --------------- ---------- ---------- ---------------
1 downsized 122 119 jane
1 downsized 122 120 Blue Team
1 downsized 122 123 girls
1 downsized 122 121 group ED
2 Blue Team 120 113 jed
2 Blue Team 120 114 ted
2 Blue Team 120 115 joe
2 group ED 121 111 ed
2 group ED 121 113 jed
2 girls 123 119 jane
2 girls 123 118 michelle
2 group ED 121 114 ted
2 group ED 121 112 fred
2 Blue Team 120 116 moe
2 Blue Team 120 117 toe
2 Blue Team 120 123 girls
3 girls 123 118 michelle
3 girls 123 119 jane


18 rows selected.
SQL> --- that are also members of the "Blue team" group.
SQL> select downsized.*
from broups bt,
( select level, c.BRAME, bb.* ,b.BRAME MBR_NAME
from broup_broups bb
, broups b
, broups c
where
bb.BRID_GRP = c.BRID
AND bb.BRID_MBR=b.BRID
start with bb.BRID_GRP = 122 --- the downsized group ID.
connect by prior bb.BRID_MBR = bb.BRID_GRP
) downsized
where downsized.brid_grp = bt.BRID
and bt.BRAME = 'Blue Team'

LEVEL BRAME BRID_GRP BRID_MBR MBR_NAME
---------- --------------- ---------- ---------- ---------------
2 Blue Team 120 113 jed
2 Blue Team 120 114 ted
2 Blue Team 120 115 joe
2 Blue Team 120 116 moe
2 Blue Team 120 117 toe
2 Blue Team 120 123 girls


6 rows selected.

So it is tricky to navigate, but the group of groups relation
(broup_broups) is the core.

You may not be able to keep everything in a hierarchy (so the CONNECT
BY will no longer work) but just keep in mind that the group of groups
relation basically can form a matrix. You may have to program a
function to walk the matrix which can be tricky due to possible loops.
in such a free wheeling system.

You might just make it a group of users relation. In that case, adding
a group as a member of another group would cause the application code
to add all the members of the sub group to that parent group in the
group of users relation.

There is a price for flexibility, but rewards as well.

good luck.
Ed

What you described is not a hierarchy; it is a general directed
graph.

The best way I know to model a graph in SQL is an adjacency lit
model. If it was a tree, I would probably use Nested Sets.

There are several "social network" databases on the market. I happen
to like Cogito (www.cogitoinc.com), but that might be in part because
they had me do some consulting a few years ago

SQL is not always the answer (yes, Mr" SQL uber alles" actually said
that!). It is not for text and documents, GIS, graphics, spatial, or
social data. "The pumpkin is largely a failure as a shade tree." --
Mark Twain

You might want to have a look at the GEDCOM 5.5 data model (last page
of the spec) to get some ideas.
http://www.familysearch.org/Eng/Home...faq_gedcom.asp

I would not recommend GEDCOM 6.0 as this is weired and redundant in
many ways and nobody really implemented it.

Talking about genealogy, you have "Individuals", "Couples" (husband,
wife) and "Offspring" what would make the relationship between the
couple and the individual (child - parents) with the relationship_type
(birth, adoption, foster, etc.). This makes your three base tables to
work with. Then you need to ask the right questions. What are siblings
for you? The same father and mother or half-siblings with just the
same father or mother.

As Joe said, an adjacency model was looking best for this. You could
query the ancestor and descendant list by using CTEs.

With regard to some other relationships (for example domestics),
GEDCOM suggest some "Associates" structures and a "Groups" structure
(from Gedcom 6) which would fit into different tables.

The issues by using SQL for genealogy are that you can hardly define a
lot of constraints as this data is not so fixed in its structure as
business data is and also try to find a good primary key to define an
individual that lived 400 years ago (when you found one, let me know).
Bottom line would be that you can not use the full power of SQL for
such data, but I would not go so far and say do not use it (I did for
my private genealogical research). Just look at other genealogical
applications which do not use SQL. Once you hit volumes of some
thousands of individuals, most programs fail miserably as they do not
have efficient data storage and retrieval, no good indexing, no query
optimization etc.. Reporting is also often poor but quite important as
genealogists would like to print things out - and every genealogist
want a different report in a different layout :-).

brgds

Philipp Post