title: MySQL Family Tree: Hierarchy, Marriage ref, and Display
body: <p>I am building a Family Tree + History [PHP] website to accompany and showcase my mom's genealogy report that she had put together for her parents' Christmas gift. The tree at least will be based upon a MySQL database ('family') in which I am currently working with two tables: 'persons' and 'marriages'. I also have a third table, 'parentage', but have since deemed it to be unnecessary.</p>
  
  <p>The tables' structures are as follows:</p>
  
  <pre><code>PERSONS
  - 'personID' [tinyint, pk]
  - 'firstName'
  - 'lastName'
  - 'sex' [set{'M','F'}]
  - 'birthDate'
  - 'fatherID' [internal rel = 'personID']
  - 'motherID' [internal rel = 'personID']
  - 'birthPlace'
  - 'deathDate'
  - 'deathPlace'
  - 'birthRecord' (filename)
  
  MARRIAGES
  - 'husbandID' [internal rel = 'persons.personID']
  - 'wifeID' [internal rel = 'persons.personID']
  - 'date'
  - 'place'
  - 'record'
  </code></pre>
  
  <p>PK index in 'marriages' table consists of both ID cols. (*Please excuse my use of the sexist terms; it is simply for structural purposes -- just in case, as for one I am new to MySQL -- and also my family is pretty right-wing and conservative anyway, but it really doesn't matter and holds no significance and might as well be regarded as spouse1 and spouse2, if you prefer. I personally am not at all homophobic nor prejudice towards anybody. End disclaimer. Peace to all.)</p>
  
  <p>So... you see why I want to maintain a separate table for 'marriages': each marriage has its own data that must be included, and it would be silly to repeat the data for each marriage in the records for both spouses. I had a 'spouseID' column before in the 'persons' table, but then I needed to match each of the spouses' IDs manually also, and couldn't figure out how to link that column to the corresponding marriage column (in the 'marriages' table) anyway, so I dropped it.</p>
  
  <p>If anybody could enlighten me on how I might get both of the spousal ID columns in 'marriages' to display the persons' names instead or as well that would be grand.</p>
  
  <p>But primarily I need to know how I can link the two tables (possibly include a 'spouse' column in the 'persons' table which is somehow integrated with the 'marriages' table), and secondly any tips on how to go about displaying the hierarchical tree.</p>
  
  <p>Much appreciated.</p>
  
  <p>P.S. The tree is fairly extensive, spanning ~6 generations. There are about 45 'persons', and I think 16 marriages, for whatever that info might be worth...</p>