[thelist] Genealogy database schema

[Lonnie.Kraemer]

> > I'd appreciate any pointers as to how I might structure this in an
> > Access database and the queries needed to present it similarly
> > to what I have.
>
> each person has exactly two parents
>
> there are exceptions to this, such as biological parent versus
adopting
> parent, but you can start with two and extend the concept if you need
to
>
> create table person
>  ( id counter not null
>           constraint personpk primary key
>  , firstname text(50) null
>  , lastname text(50) null
>  , mother integer null
>           constraint motherfk references person(id)
>  , father integer null
>           constraint fatherfk references person(id)
>  )
>
> note that this is not fully normalized, but, for reasons that have to
do
> with ease of retrieval of both parents at the same time, this is a
more
> pragmatic structure
>
> select p.firstname
>      , p.lastname
>      , m.firstname
>      , m.lastname
>      , f.firstname
>      , f.lastname
>   from (
>        person p
> left outer
>   join person m
>     on p.id = m.mother
>        )
> left outer
>   join person f
>     on p.id = f.father
>
> strict lineage is simple, marriage is a lot more difficult
>
> rudy

Hey rudy. I'd hoped you'd reply, but frankly you've completely lost me
with that query (not surprise). Maybe I'm asking for more than my simple
mind can comprehend. Heck, it IS a complex order, yes?