can't be more than one relational path between any 2 tables

Discussion created by pauld2001 on Apr 16, 2016
Latest reply on Apr 16, 2016 by coherentkris

Yes I’m a newbie, but convinced there must be a right way to do this.


I am trying to design a database for videotapes used to make a documentary.   I’m first creating a Physical Model  with a table for Camera Masters, Protections Copies and Interviewees.  I believe I have correctly established their respective relationships in the graph table.  These tapes will be stored in several locations in boxes or on shelves. (These locations are not permanent, so I think makings  changes to location parameters will best be handled by making location a table.)


It might be worth noting that there is "1 Camera Master (CM)  to many Protection Copies (PC)" so to that extent they are different entities with that kind of relationship *but* at the end of the day they are all tapes that require locations on shelves.


I was attempting to create one Location Table with each specific location (think shelf or could bd box) represented by a code, that would be the Primary Key (unique) in this Loc. table and the Foreign Key in the Camera Master and Protection Copies table.


However in Filemaker when I attempt to connect the Protection Copies location field (FK)  to the (the PK location code) Location Table, *after having done the same already* with the Camera Master table… I get this error msg "There cannot be more than one relational path between any two tables in the graph." It forces the creation of a location table 2.


I thought one could create a single location table that could work for all the tape Entities, these two and others (possibly added later).  What is the error in this design?  There is no reason a CM can't live on the same shelf as the PC of another interview, they need not be segregated.

I should just add that many  tapes can live on one shelf (it could just as well be called a box). Have I explained this correctly? As you might guess I’m a newbie but highly motivated to get this right.  To recap every record in each table has a unique code and I use these codes to link the tables. So in a camera master record we see a code for an Interviewee, that code “points” to the full Interviewee record in the Interviewee table.That one work

Thanks in advance for any help.