[thelist] Genealogy database schema

[rudy]

> 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