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.