7 Replies Latest reply on Jun 22, 2016 5:24 PM by littleluce

    Table Occurrences and Many-to-Many relationships

    littleluce

      I'm having some trouble structuring my database. It seems to be getting more complicated than it needs to and I'm hoping that multiple table occurrences can fix my problem, but I'm not sure if I would be using them properly.


      The basic structure of my relationships is Clients > Programs > Episodes > Outputs > Tapes. I have added another table under Programs called “Sources”, and I’m about to add a 3rd table under clients called “Hard Drives”. It seems to me that the Tapes, Sources, and Hard Drives table should actually be only 1 table because each record in those tables is assigned to a unique barcode. The problem however, is that there is no one-to-many relationship that would apply to all 3 types of items that have barcodes.

       

      If I combined these tables in a single Barcodes table and used 3 different foreign key fields to make each Table Occurrence a child of it's proper table in my main structure, would that be bad database structure?

       

      For example, I would have a field _fkClientID to connect one table occurrence to the Client's table, so that all items with a matching ClientID field, but but another field _fkProgramID for the records that need to be children of my Program table, and another field for _fkOutputID, etc.

       

      The main advantage of this would be that when I'm searching for barcodes, I only have to search 1 table, then I can more easily check which parent table the record is related to, and I could more easily ensure that users did not enter duplicate barcodes into different tables. Will this work or am I missing something?

        • 1. Re: Table Occurrences and Many-to-Many relationships
          TorstenBernhard

          Take a look here: http://hbase.net/2015/11/16/join-tables-in-filemaker-pro/.

          Douglas Alder from HomeBase Software provides an excellent overview for this topic.

          • 2. Re: Table Occurrences and Many-to-Many relationships
            littleluce

            Are you suggesting that I would need to create 3 join tables to connect everything to a unified Barcodes table? I tried that and I'm being prompted to create extra table occurrences because Clients, Programs, and Output are already related to each other.

             

            Also, the relationship between Client and Barcodes is not many-to-many, nor is the relationship between Program and Barcode or Output and barcode. Each barcoded item would have to choose to be under either Client, Program, or Output.

            • 3. Re: Table Occurrences and Many-to-Many relationships
              Mike_Mitchell

              littleluce wrote:

               

              If I combined these tables in a single Barcodes table and used 3 different foreign key fields to make each Table Occurrence a child of it's proper table in my main structure, would that be bad database structure?

               

               

              No. In many cases, this is the correct approach. Think of a join table as a place where each record represents a unique combination of parent records. If you have a three-way join where this is true, then multiple keys is perfectly fine.

              • 4. Re: Table Occurrences and Many-to-Many relationships
                clayhendrix

                Has your question been answered? When I read your question, I thought of a solution.

                • 5. Re: Table Occurrences and Many-to-Many relationships
                  littleluce

                  What is your suggestion? I'm still using 3 separate foreign keys in my barcodes table

                  • 6. Re: Table Occurrences and Many-to-Many relationships
                    clayhendrix

                    Thanks for the quick reply.

                     

                    1.) Do you still have separate tables for Sources, Tapes, and Hard Drives?

                    2.) What is the table structure in which you are using the 3 separate foreign keys?

                    3.) How is the database performing speed wise? How is it performing in meeting your informational need?

                    4.) Could you send me a screen shot of your relationship graph? (It's ok, if not)

                    5.) Are Tapes and Hard Drives both storage methods?

                    6.) Do you put products and raw material on Tapes and Hard Drives?

                     

                    Just six simple questions, lol. Sorry about that. I'm just trying to wrap my head around what you are trying to accomplish.

                    • 7. Re: Table Occurrences and Many-to-Many relationships
                      littleluce

                      1. No, I don't have separate tables for sources, tapes, and hard drives. The only real reason against that was because barcodes are unique across all of the tables and I wanted to provide users a way to scan the barcode once and have it search all 3 tables

                       

                      2 & 4. I've attached a screenshot of a sample of the relationships in my graph (the full graph would have been too hard to explain). Basically I have 1 barcodes table with 3 fields used as foreign keys. One to relate to clients, one for programs, and one for outputs. I use a calculation field that looks at the values (or empty values) in these fields to give me a "type" that classifies the barcode as either a source, tape, or hard drive.

                       

                      Screen Shot 2016-06-22 at 5.16.59 PM.png

                       

                      3. Speed hasn't been an issue for me just yet. There are currently about 2500 records in the barcode table.

                       

                      5 & 6. No, tapes are either blank stock, or contain the final video content for TV shows. Hard drives store various files, but I was not looking at indexing the contents of hard drives in this solution.