We had a lively (what else would you expect from NYC) round table discussion at a NYFMP.org meeting on 2018-09-20, discussing FileMaker Join Table Architecture.
This topic comes up a lot...therefore...
Attached is the diagram (slightly cleaned up) that we riffed off of.
Please note that these are 2 ERDs, not RGs (I like Anchor-Buoy).
Feel free to join in with your thoughts. Do you like...
...and why?
It depends on the particular case. Generally speaking, separate join tables provide greater flexibility.
Oh! Both are excellent.
(Edit: both plus and minus have been converted into bullet points. Very hard to correct from my phone )
Pattern 1:
Pattern 2:
Pattern two is more abstract than pattern one. You get all the advantages that flow from abstraction. The disadvantages arise from having to specify when and how to handle the specific objects. In my experience, the simpler the RG, the more code is required.
As an anecdote about abstraction, I have seen a mature, complex, single table solution (CRM, Products/Services, Invoices). It had a reasonably large record set (~120,000). It was blazing fast. Everything was done in scripts.
I have a tendency for the first but am not beholden to it.
The first better fits normalization. The second may be more advantageous from a selective denormalization point of view… and FileMaker, from an implementation point of view, is a great example of where you may want to perform this selective denormalization.
add me on the "depends"-side
in a classical setup like actor - role - movie I'd do it in one table since all columns are always used.
a different setup where one column might remain empty in 30-50% of all rows I'd tend to use two or more tables.
as @bdbd pointed out it also has an impact on normalization: something worth discussing in an extra thread ( or board, or forum?) and treated different by these SQL-Geeks doing database designs all the time not like us FM allrounders 
Depends on circumstance. Sometimes I don't do join tables at all — I'm not averse to using an old-fashioned multikey if I'm not needing to store any additional info about each of the the unions between LocalTable and multiple iterations of RemoteTable X.
Hi Alex, welcome to the soup. @tonywhitelive, multi-key is solid contender for "join table without infrastructure."
Hey Allan. Good to see you here man. How have you been?
Decent, overall. I sort of rode the FileMaker Cafe ship down with the captain. Mostly I wasn't fond of that FmForums place and didn't know where else to go. Good to see a familiar "face" here!
