Some comments, thoughts, and opinions are below. I hope that they may be helpful.
As always, feel free to ask questions. Also as always, @everyone, feel free to chime in with additional thoughts, better advice, etc.
Question while on this table: we could even do without the primary key as it plays no part whatsoever in finding or indexing, correct? Any searching would only be done on the foreign keys.
While I agree with your assessment that there currently is nothing that would break if the primary key were removed in the link table, I would maintain a primary key in the table based on the following personal considerations:
With the possible exception of perhaps a FMP “globals” table, having a primary key in every table is a convention that I, personally, find difficult to break.
Though I applaud developers thinking about overhead consequences in all that they do, in every system where I have been up against a performance problem, removing a primary key from a table would not have appreciably helped – there have always been much more glaring and important-to-address causes for poor performance.
I can imagine scenarios where having a primary key in the table would be useful.
What I meant to do from the beginning is to automatically assign the ‘counterpart’ of a relationship (hence the 2 description fields in the linktable in my original file). We could argue about ambivalence and use ’is spouse of’ instead of breaking down into ‘is husband of’ and ‘is wife of’. We could also use ‘has a family tie’ instead of breaking it down into all sorts of complicated family relationships like ‘is brother/sister/nephew/aunt/great-grandson of’, etc. I shall need to rethink the necessity of this complexity in my solution, but let’s keep it in place for argument sake.
Apart from the really complicated automated situations (the party is a male and therefor can only be ‘is brother of - and all other male roles), let’s consider relations in the professional environment. I’d like a type of relationship described for party1 to be reflected automatically in party2. Examples: when José employs Jordi, I’d like the Jordi record to show ‘is employed by’ José.
Great. The above two blocks make plenty of sense to me.
What would need to happen is when creating a record in the Linktable with
link_party_fk1 = 150 (José) and
link_party_fk2 = 180 (Jordi) and
link_relationship_fk = 5 (is employer of)
another record in the Linktable is to be created with the ‘counterpart values’:
link_party_fk1 = 180 (Jordi) and
link_party_fk2 = 150 (José) and
link_relationship_fk = 7 (is employed by)
Is this the correct way of thinking?
An excellent concrete example, and I agree with it entirely.
And if so, how could I achieve this? Switching the party’s primary keys could not be too difficult (although I would not know how to do this at the moment), but how could we determine the correct relationship (‘is employer of’ vs. 'is employed by’)?
Short answer: This would all be handled with scripting.
In my own work, it’s rare that I allow a user to directly create a new record. Instead, there is almost always a script that the user invokes which uses FMP scripting to create the desired record. Such a script not only creates the new record, it also ensures that any necessary related business logic is performed.
It is in such a script (or subscript of) where I would create both needed records, i.e. the record which represents the original relationship that the user (or some other system) has requested be stored, as well as the record that represents the same pairing of parties, but with the “inverse” relationship association.
At this point, I’ll start by assuming the following, and, of course, feel free to correct me if I am mistaken:
You are ok with the idea of using a script to create the records.
You have some sort of mechanism in place whereby such a script can be supplied two Party primary keys, and and one of the Relationship primary keys.
Scripting the creation of the two records in the Link table is within your FMP skill set.
The only overtly remaining puzzle piece is to determine the second required Relationship primary key, based on the three supplied pieces of information which your script already does have available to it.
Given the above, I see two possible scenarios to consider:
Every Relationship is in a 1-1 relation with its inverse, for example:
- Parent of <-> Child of
- Sibling of <-> Sibling of
- Is employed by <-> Employs
- Grandparent of <-> Grandchild of
The above is not true, and some Relationships have multiple inverse descriptions, e.g.
“Grandmother of” has multiple possible inverses: “Granddaughter of” and “Grandson of”
How I’d handle each of the above:
Case 1: Each Relationship has exactly one inverse, and we expect that this will always be the case.
In this case, I’d populate an additional field in the Relationship table, which stores the primary key of the related inverse Relationship record.
The Relationship table fields would look something like this:
Then, during the execution of the script that creates the two records in the Link table, given one of the Relationship keys, it is easy for us to consult the record in the Relationship table to determine the key that we need to create the second of the two Link records.
Case 2: It is possible for a Relationship to have multiple possible inverse Relationships.
In this case, I’d consider adding a table to store the relations between Relationship records.
(This would be a separate table beyond the Relationship table that we already have.)
The fields would probably look something like this:
Some sample data might look as follows:
Relationship table (pk, description):
2, “Is employed by”
3, “Is Grandmother of”
4, “Is Grandfather of”
5, “Is Granddaughter of”
6, “Is Grandson of”
7, “Is Brother of”
8, “Is Sister of”
9, “Is Cousin of”
InverseRelations table (pk, fk_target, fk_inverse):
1, 1, 2
2, 2, 1
3, 3, 5
4, 3, 6
5, 4, 5
6, 4, 6
7, 5, 3
8, 5, 4
9, 6, 3
10, 6, 4
11, 7, 7
12, 7, 8
13, 8, 8
14, 8, 7
15, 9, 9
Give the above two tables:
When our record creation script runs, it will have the wherewithal to determine a list of possible primary keys which would be appropriate for use when creating the Link record that stores the “inverse” Relationship record. The final selection of primary key to use might involve human intervention, or, in some cases it could possibly be determined programmatically based on other known information, e.g. the storage of gender data (as I believe you already mentioned).
I’ve sort of sketched out how I might approach this sort of solution, but as I’ll often acknowledge: these are not the only possible solutions – they are just avenues that I’d consider first, knowing few of the other considerations that are part of the bigger picture of your solution.
As an example: The choice to store inverse relation data in a table is a pretty traditional approach, but it is not the only possible solution – certainly lists of possible inverse keys could be stored in a single field within a single Relationship record. Doing so might make sense in some situations. In (many/most) other situations, I’d prefer to split such information out as rows in a table (as shown above).
And again: My acknowledgement that there are other ways should be taken as an open invitation for anyone else to chime in with experience, thoughts, better solutions, etc…
I haven’t included any details about how to look up data from a table (as would be needed to implement the scripting that I’ve touched upon). I only mentioned that it should be easy to do, and left the details to you to consider. If some elaboration would be helpful there, please let us know. I neither wish to post something which underestimates nor overestimates your experience with FMP and its world of scripting, context, relationships, getting at related data, etc…
I have not touched upon the topic of how I might proceed if I were unsure of which of the two above cases applies (regarding whether inverses are 1-1 or possibly 1-Many). I have a few thoughts about this, but am withholding them for now, as they may not be relevant or helpful at this time. I’ll be happy to follow up and share them, if that might be helpful.
I think that’s all for now. As always, I hope this is helpful.