5 Replies Latest reply on Feb 27, 2012 10:54 AM by philmodjunk

    Am I on the right track here? How can I solve this many to many problem?

    DanF

      Title

      Am I on the right track here? How can I solve this many to many problem?

      Post

      Second post today, I'm on a roll! Okay so I'm new to FileMaker and relational databases, thus I need help. First of all, looking at the attached screenshot, am I on the right track here, i.e. one contact can have many photos but not the other way around, and the same goes for shoots and photos? I'm just learning here, so comments are much appreciated. Ignore my field names, as I'm just exploring for the time being.

      Now for my dilemma. One shoot can have many locations, yes, but one location can also have many shoots! How do I deal with this and how does it apply in the real world? This for a photography business database, though it will hopefully eventually have many more tables. Like I said, just exploring for the moment while I learn Laughing

      Screen_Shot_2012-02-23_at_6.13.19_PM.png

        • 1. Re: Am I on the right track here? How can I solve this many to many problem?
          philmodjunk

          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----<Location_Shoot>----Shoots

          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

          • 2. Re: Am I on the right track here? How can I solve this many to many problem?
            DanF

            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.

            • 3. Re: Am I on the right track here? How can I solve this many to many problem?
              philmodjunk

              but it's important I do figure these things out one at a time.

              Then why haven't you set up the suggested join table between shoots and locations before moving on to this issue? Wink

              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.

              • 4. Re: Am I on the right track here? How can I solve this many to many problem?
                DanF

                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?

                • 5. Re: Am I on the right track here? How can I solve this many to many problem?
                  philmodjunk

                  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.