Many-to-many: Join file vs multi-key

[cinolas]

Hi,

I just discovered multi-key relationships (where a relationship is based on a text field containing a list of values). I can't believe I've worked for so long with FMP without knowing about this ! I though that the only way of creating a many-to-many relationship was to create a join file!

So before I go ahead and implement this rather simple technique into my development, I would like to have your opinion and some answers

What are the advantages/disadvantages of join files ?

Are there limitations with multi-key relationships ?

Why doesn't FileMaker mention multi-key when it comes to many-to-many relationships ?

Is there anything I should be aware of before I go ahead and change the way I develop ?

I have a large db (20K records) that relates many-to-many with a smaller db (600 records). The join file has about 250K records ! Wouldn't I save space, time and network traffic by using a multi-key relationship instead of a join file ?

Why would anyone use a join file ?

Thanks to all !

[Kurt Knippel]

First of all a a join file and a multi-key are two TOTALLY different things, used for totally different purposes.

A multi-key will NOT create a many-to-many relationship. It has the possibility of creating a one-to-many OR a many-to-one relationship but it cannot create a many-to-many.

A multi-key is really the Filemaker implementation of relational operators other than the EQUALS which is the only one supported by the Filemaker Relationship. Multi-keys, while they can be used to create one-to-many EQUALs relations, are more powerful in creating one-to-many <>, <, <=, >, >= relations.

A join file is needed to make a many-to-many relationship, because you are relating many records in one file to many records in another file. The only way for this to happen is via some kind of map of which records are related to which records, thus the join file.

Multi-keys can also be difficult to manage, while join files are typically very easy to manage.

[cinolas]

Hi captkurt,

Thank you for your help.

I'm not sure I'm following you here.... I've attached two dbs to this message Employees.fp5 and classes.fp5. I have redone FileMaker's many-to-many relationship example from the 5.0 manual (page 8-15) using a multi-key field (ClassID in Employees.fp5) instead of a join file. It looks pretty much many-to-many to me ! An employee has more than one class and a class has more than one employee. Could you please take a look at this and tell me where I'm wrong, thanks.

"A multi-key is really the Filemaker implementation of relational operators other than the EQUALS which is the only one supported by the Filemaker Relationship. Multi-keys, while they can be used to create one-to-many EQUALs relations, are more powerful in creating one-to-many <>, <, <=, >, >= relations."

Now this is really interesting ! I've only ever designed dbs using FileMaker and so I have always been limited to equal relationships. I would be interested in learning how to make a <>, >=, >, < or <= relationships using multi-key fields. The only way I can see (without looking too hard) would be to fill a multi-key field with a range. But please fill me in !

I would also like to know how a join file is easier to manage than a multi-key relationship such as the one in my example (if the example is workable at all !). If you can give me an example of a situation where a multi-key based relationship can become a pain to manage, it would be greatly appreciated.

Once again, thank you so much for your help.

Example.zip

[Kurt Knippel]

You are correct in that relation operators such as the <>, <=, etc would be accomplished in Filemaker with some kind of calculated multi-key.

The real issue with using Multi-keys on both sides of the relationship is that you are actually relating many KEYS with many KEYS. You are just getting lucky and being clever and getting many records related to many records. Using a join file explicitly related many records to many records.

I am not a good enough mathematician or theorist to adequaately explain how this will end up hurting you in the long run, but since there isn't a single other RDBMS with supports this kind of construct, and even Filemaker does not make any mentions of it, there are obviously problems with it.

A join file is easier to manage because of 2 reasons: one Filemaker is a database which manages RECORDS and all of its functions are designed for this end, second the join file is a more logical and simpler construct making long term maintenance more simple.

Imagine every time you need to restore or recalculate your multi-key acrosss your 20000 records. Never needs to be done with a join file.

[Kurt Knippel]

Looking at you files, I noticed one more issue where a join file is better. How do you store information about the results of the class? An employee takes 10 classes how do you track he scored?

[cinolas]

Actually in my example there is only one multi-key field... ClassID in Employees.fp5. As far as being lucky or clever... I find this construct pretty straightforward and even logical ! But I agree that since no one uses this trick and FileMaker doesn't mention anything about it, I better avoid using it. I also agree that it's probably simpler to manage records than lines in a field.

So I will listen to your good advice and stay away from this sort of thing but I am still curious to know what the limitations and problems of this trick. I might do some testing and post some more on this. Anything you might find about this would be of great interest to me.

Thanks again.

[cinolas]

Well... This example was made to look like the example provided by the FileMaker manual and I don't think it can handle the employee's results; the database would obviously need to have one record per class in which the student is enrolled, for each student; which would nicely fit in the join file. So I guess for this specific example the join file is mandatory.

In my situation I have a database containing 20K travel Visas. Each of those visas has many Requirements. Reversely, a given requirement is common to many Visas. There is no score to keep track of, just a relationship.

Similarly, I have an Embassy db and a Holidays db. Each Embassy has many Holidays and each Holidays affect many Embassies. Once again I do not need to keep track of any information, just the relationship.

For this purpose I don't think I really need a join file since I do not need to keep track of any information regarding the specific relation of one Visa and it's requirements or Embassy and it's Holidays.

Tell me what you think !

[Kurt Knippel]

You say that there is nothing to keep track of just the relationship. I would argue that this applies only to "right now" and could easily change in the future. A change with a join file is simply adding a field. A change to the multi-key system requires a major rearchitecting of the system.

[cinolas]

I more than agree ! THAT would be a major pain. I don't think I'll ever have to keep track of any such info but do I really want to take a chance !

But besides this problem, I think the multi-key many-to-many structure is... well I won't say "good" but working anyway...

Thanks for all your help !