AnsweredAssumed Answered

Help creating joint table

Question asked by jefflambert on Jun 20, 2016
Latest reply on Jul 23, 2016 by clayhendrix

Hi, total newbie here so bare with me please:-)

 

I finished watching the essential training on filemaker pro 15 on lynda.com but I still have some question which weren't answered so I figured I should join this community and see if I could get some help.

 

My boss has done a database for our company like 15 years ago. We updated the database made of multiple files to FM 14, but it's a real mess and he asked me if I could fix it. I'm a professionnal retoucher, not a programmer, I have no other knowledge of databases either. So my question might seem really simple to you guys. In the cours on lynda, it was really emphasized that you should NOT have many to many relationship between your tables so I'm trying to figure out how we should make the joint table but since our need exceeds what was shown in the class, I don't know how to proceed.

 

Here's what we would like. Sorry if it's in french, it's color coded though;-)

whatweneed.png

 

Here are my two attempts at creating my joint tables

 

solutionA.png

solutionB.png

 

The other question I have is concerning the primary key vs the foreign key. Can the foreign key be in multiple tables? For exemple, I have the ClientIDfk in both Dossiers and ClientsEmployé, is this OK, or should I create another foreign key just for those relationship? My solution A would be simpler but can I "group" my need for joint table like that or I need to split them like Solution B? If I have to go with Solution B, would I have to also create an other relationship between Clients Employé and Dossiers Employés? Can I join two joint table like this? Would it be necessary for what we need?

 

Thanks for any advice!

Jeff

Outcomes