10 Replies Latest reply on Sep 22, 2016 7:33 PM by ChrisJohnston

    Can a join table hurt?

    ChrisJohnston

      In a database I am creating, I want the option of being able to connect content in my DEMO:: table with other related TOPICS::. At this point, I really cannot see an actual need yet. However, I am imaging that it will become useful later. My question is if I make a join table to leave the option of having this many to many setup, could it be something that gives me a problem if never used? I am thinking if un-used the worst it will be is a more complex relation to get the same thing as a one to many would accomplish. Is my thinking correct? Thanks

        • 1. Re: Can a join table hurt?
          mikebeargie

          Your thinking is not correct, if you setup a many-to-many join table, it will always BE a many-to-many, even if you only use it as a one-to-many.

           

          Same thing with the quasi "one to one" in filemaker (really a one-to-many with only one "child" record since you can't explicitly limit to one record on the right side).

           

          If these sentences are true:
          "A DEMO can have many TOPICS"
          "A TOPIC can apply to many DEMOS"

          Then just set up the many-to-many relationship now.

           

          However if these are true:
          "A DEMO can have many TOPICS"

          "A TOPIC can only be part of one DEMO"

          Then you need the one-to-many.

           

          Alternatively, if you don't really *need* a table full of topics (EG to store information about a topic record), you could just create a value list. Then you're not dealing with any table of topics at all.

          • 2. Re: Can a join table hurt?
            fmpdude

            Not disagreeing with Mike, but in my experience what turns out to often be the case is that I DO need the 1:M BOTH WAYS. So, if you don't plan for it with your join table up front, you can be left with a messy and time consuming data migration task later when something that might not have been clear up front turns out to be needed (that is, the join table working both ways).

             

            I can't count on two hands the number of times I was absolutely sure that I'd only need "two" values only to have a third, fourth, ... come in later. Thus, I had to create a new table (1:M) and migrate all the data from the "one" side to the new many side and implement (in FMP) a portal.

             

            A join table isn't a big deal so just implement it if that's how your data is (per Mike's example). If you only use it 1:M (that is one side of the join table), so what?

             

            The bigger problem happens if you have A LOT OF DATA. In that case, lots of joins will tend to slow down queries. In these cases, people sometimes de-normalize for report/query performance -- at least that's what I do on Oracle.

             

            HOPE THIS HELPS.

            • 3. Re: Can a join table hurt?
              beverly

              The bigger problem happens if you have A LOT OF DATA. In that case, lots of joins will tend to slow down queries. In these cases, people sometimes de-normalize for report/query performance -- at least that's what I do on Oracle.

               

              ditto. In MS SQL and MySQL and in FMP. Sometimes a de-normalize method is necessary, even!

               

              beverly

              1 of 1 people found this helpful
              • 4. Re: Can a join table hurt?
                ChrisJohnston

                Mike Beargie wrote:

                 

                Your thinking is not correct, if you setup a many-to-many join table, it will always BE a many-to-many, even if you only use it as a one-to-many.

                 

                Thanks for the reply. I think that I worded what I meant wrong. I am not thinking it ever becomes a one to many. I am just asking if you never have multiple records using it on either side will it create problems? Can a join table hurt?...That is if it never serves it purpose. I was not so keen on the terminology for if you only have one join.

                • 5. Re: Can a join table hurt?
                  ChrisJohnston

                  fmpdude wrote:

                   

                  So, if you don't plan for it with your join table up front, you can be left with a messy and time consuming data migration task later when something that might not have been clear up front turns out to be needed (that is, the join table working both ways).

                  I think this point is described what I was thinking about. So I guess the answer it won't hurt to have it....? If you never really use it. Thanks

                  • 6. Re: Can a join table hurt?
                    ChrisJohnston

                    Thank you all for the great help as always.

                    • 7. Re: Can a join table hurt?
                      beverly

                      OK, worded this way, and taking your OP...

                      If you have a join table and never need to have many-to-many, is that ok?

                       

                      Do I have that correct?

                       

                      A join table can be one-to-one when it's helpful to have other attributes (fields) in the join table that work for the one (on either side) that might not really fit in either directly or even needs to fit in both sides.

                       

                      If a join table is many-to-many and uses this (other attributes that may fit both sides), then it may be that there would be redundant data (or maybe not).

                       

                      Something that helps me: put it down on paper with example data and think about what reports are needed and where does the data need to be stored to be useable by those reports. This is where non-normalized might be correct!

                       

                      beverly

                      to partially quote Pirates of the Caribbean with a Substitute( quote ; "pirate" ; "developer" ):

                      And secondly, you must be a pirate for the pirate's code to apply... And thirdly, the code is more what you'd call "guidelines" than actual rules.

                       

                      • 8. Re: Can a join table hurt?
                        fmpdude

                        Exactly!

                         

                        If your data is actually M:M, sooner or later you'll probably want to look at the other 1:M (the other direction). Do yourself a favor now and plan for it. Folks may pontificate about points one way or the other, but in my experience almost every time I've ever said "that will never happen" (like I'd never need a 1:M table, for example), it does.

                         

                        And, nobody here is going to help you do a data migration to a M:M design if they're wrong.

                         

                        My two cents from the road of lessons learned.

                         

                        HOPE THIS HELPS.

                        1 of 1 people found this helpful
                        • 9. Re: Can a join table hurt?
                          ChrisJohnston

                          Yes it does, Thank You

                          • 10. Re: Can a join table hurt?
                            ChrisJohnston

                            I am glad to navigate by the pirates code... I am hoping to be able to sub in the word developer some day. Thanks