Re: Representating a family tree

On Sun, 29 Aug 2004 Guy Naor wrote

> Hi,
>
> Are there any built in or known structures I can use in Postgres to represent a family tree?

I'll go out on a limb here. Not really, but, ...

> The usual tree representations in SQL are for regular hierarchy of
> data, but on a family each node had usually 2 parents and a few child
> nodes. What will be the best way to represent something like this in an
> efficient manner?

Well, the way I have usually done this is a table of individuals, a
table of marriages, and one or more relationship tables.

Some people would reduce the number of relationship tables by including
an entry for the first child in the marriage record, and including an
entry for the next younger sibling in the individual record. I think I
prefer to use the relationship records for all relationships.

Further details would depend heavily on what you might mean by "marriage"
or "family". For instance, do you only want to deal with family of last
official record, or do you want to record the birth parents and maybe
even all adoption/foster relationships? Etc. These decisions would
determine the number and structure of the relationship records,
essentially.

Anyway, as far as I know, there is no family tree type structure in
PostGreSQL. (I can't think of any database except a custom database that
would have such a data structure, even though we use a simplification of
the family tree for all sorts of data models.) But PostGreSQL does have
tables, and you can use tables to model a family tree.

My JPY 2.

--
Joel <rees(at)ddcom(dot)co(dot)jp>