1

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.

The tables' structures are as follows:

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'

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.)

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.

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.

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.

Much appreciated.

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...

user163831
  • 1,031
  • 3
  • 13
  • 25
  • Your structure is relationally sound. The only issue I see is that you can not easily do a query of marriages and find a specific marriage for a person, without querying both the husbandID and wifeID columns. I personally would move that relationship to a related child table of marriage. You actually have the same issue with Persons. At any rate, in your queries you simply need to utilize an inner join from marriages to persons for each of the relationships and that will add all the columns of persons to the row. – gview Dec 23 '12 at 22:19
  • I don't know what you mean by "move that relationship to a related child table of marriage." How exactly would I structure that? (and with 'persons'.) I am trying to look into all these terminologies and techniques but in the end all this sporadic and sparsely relevant information I come across does me no good, I need examples relevant to the task at hand (and that I can work with with what I'm working with at present so as to understand). – user163831 Dec 24 '12 at 11:50
  • I don't see what issue you mean to say that I have with 'persons' either, none of this is very clear to me... – user163831 Dec 24 '12 at 11:53
  • 1
    FWIW, in the general case a family tree isn't a hierarchy, it's a network. And it can have loops. This might not apply to your project, but it's good to know anyway. – Mike Sherrill 'Cat Recall' Dec 24 '12 at 12:27
  • 1
    What loops do you mean? Like in a suffix tree? Can you explain? – Micromega Dec 24 '12 at 12:52
  • Loops: distant cousins may legally marry. If they have children, these children then have more than one parentage path to particular ancestors. There are also sadder, criminal circumstances that create loops. – Ross Presser Apr 04 '16 at 18:07

3 Answers3

1

Straightforward SELECT-JOIN query on the same table twice gets all the marriages as a list.

SELECT m.*,
      h.firstName as husbandFirstName, h.lastName as husbandLastName, 
      w.firstName as wifeFirstName, w.lastName as wifeLastName 
FROM marriages m
LEFT JOIN persons h ON m.husbandID = h.personID
LEFT JOIN persons w ON m.wifeID = w.personID;

For a list of children of a male, you pass the father's ID in as such:

SELECT p.firstName as fatherFirstName, p.lastName as fatherLastName, 
     c.firstName as childFirstName, c.lastName as childLastName
LEFT JOIN persons c ON c.fatherID = p.personID
WHERE p.personID = $fatherID;
Philip Whitehouse
  • 4,293
  • 3
  • 23
  • 36
1

Here's the suggestions I made in my comment in an ERD Diagram.

ERD

gview
  • 14,876
  • 3
  • 46
  • 51
0

Not an answer at all, but I also have been thinking of a family tree, but my thinking is more general. That is, a table of individuals as you have (less the fatherID and motherID fields, and a separate table on relationships.

The latter would allow for marriages, divorces, and children.

Now this would certainly show some heavy CPU usage at query time, as well as tax my understanding of joins. But right now it seems to address my needs, for one.

user1032402
  • 410
  • 1
  • 3
  • 11
  • Hi there, am also working on a family tree using PHP, MySQL, JQuery, and Google Org Charts. Please contact me at manolo.webmaster at gmail dot com if you'd like to exchange ideas – Manny Ramirez May 24 '23 at 18:40