All looks good until you get to the many to many relationship for shoots to locations. What you have won't work as you would have to enter identical information into Fk_Location01 and FK_Location02 before it could match to any record in Locations.
Many to many relationships almost always require a Join table and that's what you should use here.
Locations::PK_LocationID = Location_Shoot::FK_LocationID
Shoots::PK_ShootID = Location_Shoot::FK_ShootID
You can place a portal to Location_Shoot on a Shoots layout to list all locations used for that Shoot. You can put a portal to Location_Shoot on a locations layout to list all Shoots that used that location.
Here's a demo file on many to many relationships you may find helpful: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
Thanks a ton!!! Okay here's another. I've started adding more tables, thus making things more complex. Again, just playing around while I learn, but it's important I do figure these things out one at a time.
In this case I have a situation where I trip can be connected to multiple contacts, and a contact can be linked to many trips. What, then, do I do when I want to pull in shoot information tied to a specific trip? I'm pretty confused about what should be linked to what. Oh and the Photos table should not be where it is. Basically all of this stuff is linked together and I can't make sense of any of it. Shoots are related to Contacts but also trips, photos, a location, etc. In the trip log I'll want to pull up information regarding what contact, shoot, photos the trip is tied to. Etc. etc. I think you get the idea.
but it's important I do figure these things out one at a time.
I think you are circling around the main issue which is to identify the core table to which your other tables will all link--either directly or indirectly.
Clearly, you need a table where 1 record = 1 Trip. Trip Log seems to be set up for that purpose.
From there, you need a link to shoots. Can there be more than one Shoot for a given trip? Or is it one trip = one shoot?
The link between contacts and photos, BTW, could be a valid relationship if you intend to list the person photographed in a given picture in contacts. Of course if there are many people in a photograph, you need another join table.
Actually I implemented a join table elsewhere. Apparently my problem is not with not knowing FileMaker, but rather with not understanding database design. For example, I don't understand how I pick a central table and why I would pick that over another. In this case it'll be contacts or shoots. But how do I choose?
So what is indirect linking? Does this mean I could have table two between table one and three, but table one can access and display data from table three because one is linked to two and two to three?
You may not have a central table. I'm just describing the process of understanding how all the different tables interact with each other in relationships and matching that structure to the actual processes (work flow) that you'll need to support with your database design.
Often, you find there's one table that serves as the starting point for you work flow each time and you often will find that a specific table (not necessarily the table that's the "starting point") to which almost all other tables need to link to in order for you to do what you need to do.
Linking indirectly is just my way to describe a relationship--such as with a join table--where Table A Links to Table B by linking to another table or table(s) that eventually link to table B.
In this case, if your photo shoots are always associated with a "trip" it would seem that you start your process with creating a new "trip" record and then, as you fill in the details, you'll create related records in each of your other tables. If so, then you'll find that a record of Trips will be a central table in your design--meaning that you'll end up with lots of links to it from your other tables.