I am working on a system that requires an extensible representation of human relationships, i.e. Families. I have created, in the past, both models that use Contacts joined via relationship(join) records and direct key based relations: MotherID, FatherID, SpouseID. Both can represent very complicated relationships. The schema for a join record system is very simple but has high maintenance issues because of the double joins, e.g. Father-->Son, Son-->Father. The direct key way is neat because with just a few bits of information I can discover any related contacts by identifying how two contacts share keys, but the schema gets large representing the many kinds of relations (SQL calls are one way to reduce the clutter).

So my question is what is the best physical model for a Relations DB that may one day contain a lot of records without bogging down, can be extended to handle new relationship types, and doesn't require crazy scripting to create/update relationships? What are the pitfalls? Is there another model I should consider?

I would certainly use a join table. As for the double joins, I don't quite see why they would be necessary, esp. in recent versions. Building the user interface might be challenging, though. A third table of allowed types may be required - so that users cannot make one side "father" and the other "sister".

---

See also:

http://fmforums.com/forum/showtopic.php?tid/171427/

Edited September 21, 201015 yr by Guest

How deep are your going with relationships? Great uncles? etc. Are you handling things like adoptions and divorces?

I think you should define the relevant relationships in a gender neutral way and only define the relationships that can't be deduced from other relationships. Births (parent/child) and marriages (spouses) may be it.

Sibling relationships (and grandparents, uncles, cousins, etc) can be deduced from parental birth records.

I would go with two records per relationship. I've done a little basic modeling recently and it was frustrating to have Contact1 ID and Contact2 ID and Relationship1 and Relationship2. That made viewing data in portals really annoying.

Comment, interesting discussion in your linked post. Learned a new word: syndetic. Will have to use at my next dinner party.

Still not convinced of not needing two joins and would like to know how to get rid of it if possible. Here's an example: Father and son. If I look from the father record I see a join with a relationship description of 'Son'. But if I look from the son record I see the same relation description of 'son' - obviously not desirable. With a double join father sees son and son sees father, each respectively through the two joins. This seems to handle the relationship well. Is there a way to get this asymmetrical relationship without double joins? How?

The relationships are probably only going to go to the level of grandparents but to me that would include the lateral relationships that develop in those three generations, cousins, uncles, etc.

I find myself very torn here. The keyed relationships based only on mother, father, spouse allows for extremely little input from the user. For example: A family with 30 members listed in a theoretical database. To add someone new from this family only requires entering those three bits of relation information. The system can then immediately show you all relations for that new member and conversely from all other members too. That's pretty powerful. Doing that with a join record based system would require creating many, many joins.

But then again a join based scenario is very extensible and different relationship types can be defined on the fly. Joins also 'feel' like the more proper way. But I'd like to have some opinions that go beyond feelings, especially since I've not really load tested either such system with large record counts.

The relationship's description could be FieldA = "Father" and FieldB = "Son". Then you would use conditional formatting to blank out FieldA when Individual::ID = Links::ID_A and vice versa.

There is something I should have asked from the beginning: is this a genealogical solution? Because if it is, then I would suggest using the established GEDCOM data model instead of reinventing the wheel.

Conditional formatting is indeed a clever way to interface this since it is actually 'conditional' to the current view.

This is not intended to be genealogical but I would be interested in learning more about GEDCOM. Has there been much work done in the FileMaker arena using GEDCOM? Any resources you'd recommend?

Has there been much work done in the FileMaker arena using GEDCOM?

Not that I am aware of. The main issue here (IMHO) is getting Filemaker to display the data in a meaningful way - i.e. a tree view. I've seen some valiant efforts in this direction, but it's not the real thing.

I would be interested in learning more about GEDCOM.

The shortest description, I think, would be here:

http://fmforums.com/forum/showtopic.php?tid/200606/post/314686/#314686 :

But there's no shortage of resources on the web - as I said, it's an established standard:

http://en.wikipedia.org/wiki/GEDCOM

Thanks for the input. Off to learn more about GEDCOM...