6 Replies Latest reply on Jul 13, 2015 11:53 PM by Atcha

    Use same link table for more tables



      Use same link table for more tables


      Before committing a huge sin against database design, is there a reason why I should not use one link table for multiple joins? I have a many-to-many relationship between two tables A and B and between B and C. Do I create two join tables or will one do (using aliases to set up the join)?

        • 1. Re: Use same link table for more tables

          Both options are possible, but whether this is a good idea or not depends on the intended function of your different tables and relationships--details not present in your initial post to this thread.

          • 2. Re: Use same link table for more tables

            Sorry. Obviously (at least to me) both tables (A and C) are part of the same database/solution and they share the same data (B), in this case a bibliography in which many a record will be linked to both tables. Now I could create a join table to link A to the bibliography and another completely different one to join C to the bibliography, or I could use a single join table (which is what I have done now) with 4 instead of 3 fields:







            • 3. Re: Use same link table for more tables

              Thanks. Since I am new to FM I figured it would be a good idea to check first with those with more experience before starting something that might cause problems later.

              • 4. Re: Use same link table for more tables

                I was not very clear. I mean a "bibliography join" table. So you'd have 4 tables. Each join would have one value in "bibliography id" (I'll call it "C_ID"), then one in either "A_ID" or "B_ID", but not in both. 

                So each join will only have 2 IDs functioning, "bibliography id" and one other. That way you can have a date field, or any other, if you want to say something about when/why/anything else. Yes, more records, but it keeps things simpler (especially easier to delete one if parent is deleted).

                I suppose you might want an auto-entered serial "bibliography join" ID also, "D_ID" (your naming :-); if it is needed from some reason; but perhaps not needed.

                • 5. Re: Use same link table for more tables

                  How does "A" differ from "C"? Frankly, it's better to use real names instead of letters. Real names imply intended function and this can help others see the "big picture" behind what you are trying to do.

                  • 6. Re: Use same link table for more tables

                    Oops, pressed the wrong button (best answer) for a reply. Ok, if it helps to explain exactly what I am trying to do, here goes. I am talking about a database of late medieval and early Renaissance printers and the devices they used to identify themselves with (still in use today). I have one table in which these devices are recorded, one in which printers are listed and discussed and a table with references to secondary literature. This last table is used in both the devices and printers tables. Often both contain references to the same publication. The relationship between the devices and printers tables is a many to many one, since printers can and in fact often had more than one device devices also were used by more than one printer since they were often handed down or sold to others.


                    Fenton, don't worry. I think I understood what you were talking about. My solution also contains four tables:



                    secondary references/bibliography

                    join table linking secondary references to both printers and devices