Compound Foreign Key - Validating Uniqueness Puzzle

[dmontano]

Hi all,

Need some help. I have table called PARTY RELATIONSHIP that records any of the three scenarios:

 

1) a relationship that a person has with another person (JOHN to JANE)

2) a relationship that a person has with an organization (JOHN to ACME INC)

3) a relationship an organization has with another organization (ACME INC to XYZ CORP)

 

In addition, the PARTY RELATIONSHIP table records:

 

4) the ROLE TYPE each person or organization plays in the relationship (HUSBAND and WIFE); AND

5) the PARTY RELATIONSHIP TYPE the ROLE TYPE combinations selected above create (MARRIAGE)

 

EXAMPLE RECORDS:

A: JOHN plays role of HUSBAND in MARRIAGE to JANE who plays role of WIFE

B: JOHN plays role of EMPLOYEE in EMPLOYMENT with ACME INC who plays role of EMPLOYER

 

WHAT WORKS

I am able to create these records. The relationships use FKs from their respective tables. There is no problem here.

 

WHAT DOESN'T WORK

I can not uniquely validate the combination of FK's to ensure there are no duplicated PARTY RELATIONSHIP records.
(I can validate via calculation in one "data entry sequence of FK selections", just not the second possible sequence)

 

WHY

The order or sequence in which a PERSON or ORGANIZATION is selected is left to the data entry person on data entry. This means a data entry person may create a PARTY RELATIONSHIP record in a field sequence selection such as:

 

Data Entry Scenario 1: JOHN, HUSBAND, MARRIAGE, JANE, WIFE

(I can validate this via calculation for uniqueness)

 

BUT, another data entry person may create another record (mistakenly) at a later date such as:

 

Data Entry Scenario 2: JANE, WIFE, MARRIAGE,JOHN, HUSBAND

(calculation used in first scenario won't "see" this as unique because of the sequence of FKs as a validation string will not show up as unique - even though the records are essentially the same thing.

 

MY CALCULATION

I am concatenating all FKs into a single string - this should be unique. I then drop this calculated result into another field (text) with auto-enter calculation to use the Filemaker built-in checkbox on that field configuration options to check uniqueness.

 

I am hoping their is a calculation that I can plug in that will test the two different ways in which the string I need to validate to would work.

 

PRIMARY and FOREIGN KEYS I HAVE

party_relationship_pk

party_relationship_type_fk

 

party_first_participant_fk

party_first_participant_party_role_type_fk

 

party_first_participant_fk

party_first_participant_party_role_type_fk

 

NOTE: 

I know I need date fields to ensure uniqueness in the validation, and to allow a particular relationship to cease to be in effect, and to allow the same relationship to be re-instantiated. I have left that out of this little puzzle as I felt it was not pertinent to the core problem.

 

I have tried to be brief and concise to the best of my ability.

 

Thanks in advance.

[comment]

Are your keys numeric?

[dmontano]

Hi Comment!

 

Hope all well, long time since I have been here. Always great to "hear" from you. Anyway - all keys throughout database are alpha-numeric. I can not change the alpha-numeric keys to numeric.

 

But, that doesn't mean I could not add a second set of "keys" for these types of relationship tables - more like calculation keys?

 

Thanks

[comment]

Let me describe briefly what needs to be done, skipping for the moment the "how" aspect. You need to have a text field that holds the foreign key pairs in known order (this is why I asked if they were numeric). So  both "JANE, JOHN" and "JOHN, JANE" must become "JANE, JOHN" while "WIFE, HUSBAND" and "HUSBAND, WIFE" must be unified into "HUSBAND, WIFE".

[dmontano]

You are correct. I tried for 2 hours last night trying to do just that and could not figure out how. It is not easier done than said - for me at least.

 

EDIT after I initially posted: What my calculation did was the take the "whole string" in one bite, and then the whole string again as a second bite but using the second possible sequence - and could not get that to work.

 

I gave it considerable thought as to if I had created an inappropriate relationship - and I am convinced it is not inappropriate. This type of relationship (which is a real-depiction by the way and not an abstract one) is reasonably wired this way by relying on the keys. 

 

It may be effectively argued that the lack of controlling the data entry input sequence is the problem and the solution lie there, but I don't buy into that - even if it would mitigate or resolve my immediate problem.

[comment]

I tried for 2 hours last night trying to do just that and could not figure out how.

 

If the keys were numeric, you could do simply:

Min ( fk1 ; fk2 ) & "|" & Max ( fk1 ; fk2 )

Since they are not, you have to do it more elaborately:

Case ( fk1 < fk2 ; fk1 & "|" & fk2 ;  fk2 & "|" & fk1 )

--

Sorry to be so terse, I'm sort of in a rush.

[eos]

It may be effectively argued that … but I don't buy into that

 

Then I guess it wouldn't really be effective …   

 

even if it would mitigate or resolve my immediate problem.

 

That's an odd attitude, IMO.

 

I would have argued exactly that – and suggested that you use global fields in a sort of “configurator” to select the involved parties and their relationship, assign their individual roles, and then perform a check on the uniqueness of that constellation before you actually create anything.

 

But the viability of that approach probably depends on how you've set up the workflow.

[dmontano]

Thanks ESO,

 

I appreciate your comments. It seems that you do suggest checking the "constellation" before creating the record through some form of validation.

 

Your suggestion of using globals in a configuration like manner sounds like a good approach with the validation done at that point. How would you validate the uniqueness?

[eos]

Thanks ESO

 

I assume that you mean me (in lowercase) … 

 

Anyway, you could either build a string from the globals that formally matches the string that you have in the join table, and match these two fields in a relationship – or just do without any calculations and create a relationship where the five foreign key fields are matched against their global counterparts in the “configurator”.

 

If this relationship yields a related record, the selected constellation already exists.

 

On a related note: while building such systems, I best liked the technique where a table only records the individual parties and their roles, and each set is connected either via another table; i.e. a marriage event, with two related records husband and wife. (Depending on the nature of events and what else you have to say (or not) about the individual event, you might even get by without the grouping table, by relating the parties directly.) 

 

Since you're creating two records, not writing into two fields, you can list the roles for any involved party by listing records via one relationship, not two. (And this should make reporting easier, too.)

 

Another advantage (which may or may not be relevant to your solution) would be that you could group any number of parties under the umbrella of a single event. (Group marriages, anyone? Cliques?)

 

Of course, this would make the validation a bit more complicated; maybe let's cross that bridge only if you decide to build it.

[Lee Smith]

Thanks ESO,

 

 

I assume that you mean me (in lowercase) … 

[dmontano]

Very good suggestions. Thanks “eos”.

 

However, in all the cases you discussed there is the still the ever-present issue that a data entry person may select JOHN in the first field, and JANE in the second field. Another data entry person at a later date may enter JANE in the first field, and JOHN in the second field. In that example there would be no relationship match as those keys are different from one another and building the relationship requires me to latch onto specific fields. [Then again, maybe I could build two TOs, each checking one of the two possible key arrangements to check both data entry possibilities...?]

 

Keep in mind the highly varied RELATIONSHIP TYPEs that will exist, such as: employment, marriage, partnership, etc., so any approach of modeling any particular RELATIONSHIP TYPE specifically defeats the purpose of having a PARTY RELATIONSHIP table in the first place (I think).

 

Imagine the fields such as: (I will only concentrate on two fields to illustrate)

 

RECORD # 1

FIRST PARTICIPANT NAME = JOHN (pk_001)

SECOND PARTICIPANT = JANE (pk_002)

 

RECORD #2

FIRST PARTICIPANT NAME = JANE (pk_002)

SECOND PARTICIPANT = JOHN (pk_001)

 

Assuming these two records indicate JOHN as HUSBAND, and JANE as WIFE, in a MARRIAGE: I would have two records that are of the same relationship.

 

In the above example it shows that a data entry person could create record #1 and all is fine and dandy. Then, data entry person a month later could record RECORD #2. The second record has JANE in the field that RECORD #1 has JOHN: those two keys don't match so uniqueness is valid. It is when we are also recording the second participant where the uniqueness of key values throughout the table for each of those fields is valid - but the records are saying the same thing. This is caused by the field names HAVING to be somewhat abstract (first participant, second participant).

 

It is entirely different when the field names are explicitly asking for only one clearly understandble value. It is reasonable for everyone to think that it doesn't matter which person to record as first participant - as long as the other participant is recorded in the other field.

 

Sorry if I am saying the same thing over and over and the issue is already clear and known. I find it difficult to articulate this.

 

To top it off, I am an intermediate at best. Every time I think I am building some steam up using Filemaker - I get derailed. Therefore, my posts reflect a desire to learn and not intended to be defensive nor hostile.

[comment]

If I understand eos's suggestion correctly, the user would enter the two foreign keys into global fields first, and a script would then populate the two "real" fields in known order - so in your example, pk_001 would always be sent to the first field, being the lower of the two.

 

Logically, there's no difference between the two suggestions. There will be differences in implementation costs, however.

[dmontano]

Well, I finally got home and tried the calculation method Comment suggested. Here is the calculation:

 

Case (

 

party_fk__participant_first < party_fk__participant_second ; party_fk__participant_first & "|" & party_fk__participant_second ; 

party_fk__participant_second & "|" & party_fk__participant_first

)

 

&

 

Case ( 

party_role_type_fk__participant_first < party_role_type_fk__participant_second ; party_role_type_fk__participant_first & "|" & party_role_type_fk__participant_second ; 

party_role_type_fk__participant_second & "|" & party_role_type_fk__participant_first

)

 

&

 

party_relationship_type_fk

 

Result: it works like a charm! Thanks a bunch.

 

I do have a few questions though:

 

1. Why the pipe symbol? Is this just to have a "known" delimiter symbol in case the resulting calculation ever needs to be parsed?
2. I am using two fields for this validation:

• Calculation field that holds the above calculation;
• Text field "auto-enter, calculated value" that populates the result of the calculation field. Then I set "validation" on this field to "unique".

The two fields seemed like bloat. So I tried just the one text field as such:

• Text field "auto-enter, calculated value" then entered the calculation into the calculation dialogue box, then I set the "validation" for this field to "unique". It works.

This may seem elementary, but am I missing something? I have been creating these two (calculation AND auto-enter text) field arrangements for quite some time to perform validation and can't remember what would have possessed me to do so. I thought it had something to do with the "auto-enter text field" not updating as you'd expect. Whereas in the two-field arrangement the first calculation field was going to evaluate no matter what, which triggered the second field to update. Maybe something to do with related tables having some fields in the calculation?

 

Also, I didn't try eos method, I think it's a bit above my skill level at this time. However, I really would like to build such configurations in the future. 

 

Thanks both Comment and eos - much appreciated guys!

[comment]

Why the pipe symbol?

 

To prevent a false positive match when concatenating say "12" & "345" and "123" & "45". This may not be a problem with your specific data format, but IMHO it's good practice = future-proofing in case you do decide to move to numeric IDs.

 

 

The two fields seemed like bloat.

 

Indeed they were.

[dmontano]

Awesome! Thanks again.