[thelist] Genealogy database schema

Fox Files foxfiles at bellsouth.net
Tue Jan 7 08:54:01 CST 2003


Lonnie.Kraemer" <lwkraemer at directvinternet.com>
> For about 1 1/2 years I've been gathering genealogical information for
> my family (families). Currently I have it all hard-coded with deeply
> nested <OL> lists. I'd like to database this information, but have not
> been able to figure out a relational schema to do so.
Here is a group of Access code...
I am trying to morph into MySql.  can anyone translate .
I seems to be adaptable to genealogy...and would probably be close to Rudy's
offering.
Rudys post is included below the Access code:

SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name, SiresSire.Name,
SiresDam.Name, DamsSire.Name, DamsDam.Name, SiresSiresSire.Name,
SiresSiresDam.Name, SiresDamsSire.Name, SiresDamsDam.Name,
DamsSiresSire.Name, DamsSiresDam.Name, DamsDamsSire.Name, DamsDamsDam.Name
FROM (((((((((((((TblHorses LEFT JOIN TblHorses AS Sire ON TblHorses.SireID
= Sire.ID) LEFT JOIN TblHorses AS Dam ON TblHorses.DamID = Dam.ID) LEFT JOIN
TblHorses AS SiresSire ON Sire.SireID = SiresSire.ID) LEFT JOIN TblHorses AS
SiresDam ON Sire.DamID = SiresDam.ID) LEFT JOIN TblHorses AS DamsSire ON
Dam.SireID = DamsSire.ID) LEFT JOIN TblHorses AS DamsDam ON Dam.DamID =
DamsDam.ID) LEFT JOIN TblHorses AS SiresSiresSire ON SiresSire.SireID =
SiresSiresSire.ID) LEFT JOIN TblHorses AS SiresSiresDam ON SiresSire.DamID =
SiresSiresDam.ID) LEFT JOIN TblHorses AS SiresDamsSire ON SiresDam.SireID =
SiresDamsSire.ID) LEFT JOIN TblHorses AS SiresDamsDam ON SiresDam.DamID =
SiresDamsDam.ID) LEFT JOIN TblHorses AS DamsSiresSire ON DamsSire.SireID =
DamsSiresSire.ID) LEFT JOIN TblHorses AS DamsSiresDam ON DamsSire.DamID =
DamsSiresDam.ID) LEFT JOIN TblHorses AS DamsDamsSire ON DamsDam.SireID =
DamsDamsSire.ID) LEFT JOIN TblHorses AS DamsDamsDam ON DamsDam.DamID =
DamsDamsDam.ID ORDER BY TblHorses.Name;
****
Rudy's post:
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






More information about the thelist mailing list