September 7, 2011 at 8:47 am
Good day,
I am developing a relational data model that must allow for a variety of relationships between a person and multiple other people and organizations.
The pattern I have so far does not constrain the choice of the 'related role' given a choice of the 'first party role'.
For example, if person A in the relationship is a "father", Person B should not also be "father".
I am looking for your thoughts or experience on how to handle personal/organizational relationships in the best way. I thought of having a table of valid relationships, but not sure how to go.
Can anyone share their thoughts, or point me to a reference?
As always, any help is much appreciated,
Steve
September 7, 2011 at 11:02 am
To constrain relationships the way you want, you could create an xref between relationship types and roles, defining which combinations were valid, then FK the Party Relationship table to the xref instead of directly to the role and relationship type tables.
For example, let's say you had a relationship type called "Parent/Child", and the roles "Mother", "Father", "Son", "Daughter". You'd have rows in the xref like this (though of course the actual values would be IDs FK'd to the appropriate lookup table):
Relationship Type-First Party Role-Related Party Role
Parent/Child-Mother-Daughter
Parent/Child-Mother-Son
Parent/Child-Father-Daughter
Parent/Child-Father-Son
Now, if the relationship type in Party Relationship is "Parent/Child", not only will the first party have to be "Mother" or "Father", the related party will have to be "Son" or "Daughter".
That said, I'm not sure this kind of a structure is the best way to go. If you're trying to capture any kind of relationship between two of any kind of entity, things could get really ugly and kludgy really fast. At the very least I think I'd model organizations separately from people, and likewise model org/org relationships and org/person relationships separately from person/person relationships.
September 7, 2011 at 12:19 pm
notoriousdba (9/7/2011)
To constrain relationships the way you want, you could create an xref between relationship types and roles, defining which combinations were valid, then FK the Party Relationship table to the xref instead of directly to the role and relationship type tables.For example, let's say you had a relationship type called "Parent/Child", and the roles "Mother", "Father", "Son", "Daughter". You'd have rows in the xref like this (though of course the actual values would be IDs FK'd to the appropriate lookup table):
Relationship Type-First Party Role-Related Party Role
Parent/Child-Mother-Daughter
Parent/Child-Mother-Son
Parent/Child-Father-Daughter
Parent/Child-Father-Son
Now, if the relationship type in Party Relationship is "Parent/Child", not only will the first party have to be "Mother" or "Father", the related party will have to be "Son" or "Daughter".
That said, I'm not sure this kind of a structure is the best way to go. If you're trying to capture any kind of relationship between two of any kind of entity, things could get really ugly and kludgy really fast. At the very least I think I'd model organizations separately from people, and likewise model org/org relationships and org/person relationships separately from person/person relationships.
Thank you for this.
I was thinking along the same lines. Some relationships are permanent (e.g. mother/son) while others are quite transitory (subject to arbitrary changes in business rules, status, etc). I think I will break out the types of relationships as you are suggesting.
Thank you again for your thorough response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply