Relationships in a Genealogy dB HELP!

[CGiven]

Greetings all,

I have been using FileMaker for years, and created basic databases over that time. So when I decided I was tired of Genealogy software that didn't meet all of my needs, I decided to write something myself in Filemaker. But I have quickly found myself over my head, and I am hoping I can find some helpful solutions out there.

My biggest problem seems to be with relationships. I have been fine using simple relationships, but never worked with anything this complex. I'd appreciate any suggestions or examples that might untangle these knots.

1) On an individual's record, I want to be able to display the following information linked to other individuals:

Father's Name

Mother's Name

Spous(es)'s Names

Child(ren)'s Names

I can create the one to one relationship via self join for Father's and Mother's, but then getting this to translate into spouses and children, with the multiple branches, is throwing me off. What is the best way to design these types of multilayered relationships?

2) The second issue is what I have started calling a cross-over portal because I have been unable to find a reference to this situation and without a name, have had trouble searching for it...

I have several occasions when I want a portal on each end of the relationship. For example, on the Individual Record, I want to show a portal listing the sources for that information (connecting to the Source Table). And on the Source Record, I want to be able to show a portal of all of the individuals referenced in the document.

An example for the documentation is having an individual with a portal showing the Tours that he has taken. But I also want to be able to look at the tour to see which individuals have taken it.

-----------

I am sure that in both of these cases I am not seeing something that is obvious to a more experienced developer. I was hoping a cry for help might point me in the right direction.

Thanks in advance for any ideas or suggestions.

Craig

[Ender]

Hi Craig,

My, you picked a tough one for your first post! I'll address the first issue here.

I'm thinking a structure similar to that shown in the attached ER Diagram (note that an ER Diagram is not exactly the same as the TOG (Table Occurence Graph) used to actually build the relationships in FileMaker.)

A Person could be a parent in one or more Unions. A Union has 2 parents and zero or more children. A child is a member of one Union.

The trouble you may run into when attempting this geneology model in FileMaker, is actually getting the data to display multiple generations. It's easy to show a spouse and children, but to show multiple generations all at once requires multiple copies of the table occurences, all linked in a big chain.

[BrentHedden]

I attempted to do this same thing a few years ago, and quickly found out that I was going way too deep to create a good program that I would be satisfied with. I'm sure using FM 8 would make things a bit easier nowadays.

Thinking it through a bit, you'll want to have several join tables to relate a person to another person through various means, like marriage, children, etc... One join table for each kind of relationship. This will make it easier to display the related info on an individual basis.

Not to add to your problems, but trying to figure out how one person is related to another through multiple chains (2nd cousin, great-great grandfather, etc) will be an interesting challenge. Not really necessary, but would be a good feature.

I've seen a few other geneology program written in Filemaker, so do an internet search for them. They might give you a push in the right direction.

On a side note - do you work with GEDCOM files at all? I created a utility that extracts the information out of GEDCOM files and separates them into individual records. I'll be more than happy to let you have it if you want - just let me know.

[efen]

Attached is a cut-down very rough and ready early file (version 6) of how I created family relationships for a genealogy database if it's any help.

(I have also used this for a keyword thesaurus with broader/narrower/related terms so some of the relationship names/field names may be confusing.)

Tree1.zip

[CGiven]

Ender - Thanks that got me on the right track. Still trying to work out the kinks, but it is moving along now.

Brent - The relationship thing is not a big concern. I'd like to figure it out sometime, but this is one of the features that I never used on the commercial software.

Efen - I'll take a look at what you have done. Hopefully it will give me some more insight. As I progress further I will post my work for others as well.

Thanks and keep the suggestions coming.

Craig

[CGiven]

Things are moving along with the Genealogy database. I have found some solutions to the two issues I originally had. I'll post what I have done so far, and what issues remain.

1) Thanks to Ender's suggestion, I created a separate Table called Family. I had to create multiple occurences of this table to handle the various relationships matching individuals as Husband, Wife, and Child(ren). I used three seperate IDs in Family (husbandID and wifeID matched up with individual IDs; a familyID in an individual record matches with a marriageID in the Famiy Table).

I created scripted buttons to add or connect to Husband, Wife or Child records, which would create a new individual with the appropriate links made.

*The hardest part has been scripting the ability to link fields to the related records. Go to Related Record only seems to work over the main Occurence which did not work for all fields. I got around this through a roundabout way by copying the husbandID, opening the individual layout in a new window and performing a find via the ID. This seems to work for now, but I am hoping to find a proper way of doing this.

*The other issue is that from an individual record, I have been unable to create a portal that would show the related spouses. I have to create two portals one for husband and one for wives. Is there a way to have the portal recognize the gender of the individual and modify the portal settings dynamically to show the corresponding spouses?

2) The cross-over portal is now working. I accomplished this by setting up a referenceID in the Sources table. I then setup the field as a Checkbox set with the "Include Other..." option set. The value list was setup to use values from the ID field in the main table, with "include related values" checked.

This then adds checkboxes only for those records which are related. The portal on the individual side shows all Source records with a corresponding checked box. While the portal on the Source side shows all individuals related to the check boxes.

*My challenge now is to create buttons to handle the addition of sources and connection of individual to existing sources. I'll need to figure out a way to get the script to dynamically add records to the related value list.

Once these are done, the source table will be duplicated as a Events table.

My next steps include:

• Creating a media table and linking to existing tables.

• Cleaning up the interfaces and linking them into a clean user environment.

• Build a front end window that uses an index of all records to provide a jumping off point.

• Begin building reports.

If there is anyone out there who would like to try it out in the near future, let me know and we can work something out.

Thanks,

Craig

PS - Brent, if you could forward me the GEDCOM utility, I'd like to try and incorporate that bidirectionally in the future. Thanks.

[Hellmut]

How do you link husbands and wives to each other, CGiven? I realize that it must have something to do with the family ID but I do not quite get it.