1 2 Previous Next 20 Replies Latest reply on Oct 22, 2015 5:45 PM by WilliamSpangler

    Is many to many that bad?

    dyb

      I'm trying to learn some basic stuff here. I set up a small database with 2 tables – "Glyphs" and "Images". I wanted to view images that have the same name as a glyph through a portal. The glyph name was unique so there was no problem and i got a one to many relationship. But then i wanted to also view related images that don't have the same name yet are chosen manually as related. I ended up with this:

       

      Screen Shot 2015-10-22 at 13.40.19.png

       

      All i can say is that It works but i'm afraid of the many to many relationship between the "Corelation" and "Relative Images" tables. I could use IMAGE ID as a match but that would mean i need a lot of records in "Corelation" table.

       

      Now it works like this:

      If Glyphs::GLYPH UNIQUE NAME = Images::GLYPH UNIQUE NAME MATCH then show these images on the Glyphs layout through a portal

      If Corelation::GLYPH ID MATCH = Glyphs::GLYPH ID AND Corelation::GLYPH UNIQUE NAME MATCH = Relative Images::GLYPH UNIQUE NAME MATCH then show these relative images on the Glyphs layout through a portal.

       

      It works as expected and i have many images that are directly related to one glyph but i also have many images that are indirectly related to many glyphs.

       

      Do i need to fix that?


      ( I highlighted the unique fields in the database )

        • 1. Re: Is many to many that bad?
          Mike_Mitchell

          Assuming I'm reading this correctly, it looks like you're using a blend of the two methods commonly used to define a many-to-many relationship. GLYPH UNIQUE NAME MATCH is a return delimited list of names, right (sometimes called a multi-key)? Under normal circumstances, "Correlation" would be a join table.

           

          So what I would suggest is that you choose one of the two methods and restructure this. Either use multi-keys and a multi-predicate relationship to replace the Correlation table (join Glyphs to Relative Images both by Glyph ID and by Glyph Unique Name Match), or go with a simple join table as Correlation (one record per combination of Glyph and Image). This will be easier to maintain and far easier for someone else to come behind you and understand.

           

          HTH

           

          Mike

          • 2. Re: Is many to many that bad?
            beverly

            many-to-many should be through a join file, if there are "many" many.

             

            the relationship "cartesian" can be "bad" (IMHO), if there are "many" many as well.

             

            beverly

            • 3. Re: Is many to many that bad?
              Mike_Mitchell

              Agree with Beverly. The generally accepted method for a many-to-many relationship is a join table, precisely because it's way more scaleable than the multi-key.

              • 4. Re: Is many to many that bad?
                jormond

                Bad isn't the right word. It is really about what you are doing with those relationships.

                 

                If you need to see records that are specific to the parent record, truly "related", then yes, many-to-many is bad.

                 

                If, however, your relationship is for something else...let's say to see all records that have the save value in a field, then it's not so bad. As long as you understand and remember that the relationship can't be used the same way as a 1-to-many or a Join table.

                • 5. Re: Is many to many that bad?
                  richardsrussell

                  Other people have given you good specific advice, but I'd like to follow up with a general observation. If you open up your tool chest, you'll find a well-worn hammer and screwdriver and crescent wrench, because they're standard tools, applicable to a wide variety of situations, so you use them all the time, and therefore they're highly valued. But if you dig way down to the bottom, you'll run across a left-handed framistan. You hardly ever need it at all, so it looks as if you'd only yesterday pried it out of that hard-plastic shell they annoyingly come in these days. But there will eventually come a time when the hammer and screwdriver and crescent wrench are inadequate for a particular task, and only a left-handed framistan will do, which is when you'll be glad you've got one in your toolbox. (This is how I managed to make my peace with FMI's continuing support for repeating fields, which I see as basically a kludgy legacy of the bad old pre-relational days.)

                   

                  That said, it doesn't seem from your problem description that you truly need a left-handed framistan here, and you're probably better advised to go with the good old crescent wrench (IE, a join table).

                  • 6. Re: Is many to many that bad?
                    dyb

                    GLYPH UNIQUE NAME MATCH is a return delimited list of names, right (sometimes called a multi-key)?

                     

                    I don't know if i can explain it right and i might have complicated the naming of the fields...

                     

                    The GLYPH UNIQUE NAME MATCH is actually a name of the glyph represented on the image.

                    Many images in table Images are "tagged" with the same GLYPH UNIQUE NAME MATCH.

                    Every Glyph in table Glyphs is described with it's unique name.

                    Every Corelation has unique CORELATION ID and only one value in GLYPH ID MATCH and GLYPH UNIQUE NAME MATCH.

                     

                    This way i only need 1 corelation record to display for example 100 of related images in a portal.

                    I enter this corelation through a portal and it's usually 1 or 2 records and it's easy and fast to do.

                    If i do a join table (i did it before) i would need to create 100 record in Corelation table to do this.

                     

                    Should i go for a join table and somehow automate this process? There's 15000 images and around 250 glyphs and each glyph has at least 1 additional relation – i will end up with over 30000 corelation records. Don't you think 500 would be better?

                    • 7. Re: Is many to many that bad?
                      dyb

                      That's a beautiful answer richardsrussell and i'm seriously considering framing it and hanging above my desktop!

                       

                      I'm serious.

                      • 8. Re: Is many to many that bad?
                        siplus

                        M to M is not bad.

                         

                        Anything is bad if it does not deliver what you wanted in the first place and good if it does.

                         

                        It also becomes bad if it needs a manual or common sense to work as intended.

                        People don't read manuals and often forget to pack their common sense when journeying.

                         

                        It becomes particularly bad if under any circumstance it can lead to messing up data integrity.

                        • 9. Re: Is many to many that bad?
                          dyb

                          It becomes particularly bad if under any circumstance it can lead to messing up data integrity.

                           

                          What can go wrong?

                          • 10. Re: Is many to many that bad?
                            sreese

                            dyb wrote:

                             

                            It becomes particularly bad if under any circumstance it can lead to messing up data integrity.

                             

                            What can go wrong?

                             

                            I can't resist:

                             

                            Biblical Proportions - YouTube

                            • 11. Re: Is many to many that bad?
                              beverly

                              I'm not sure how many-to-many messes with data integrity. Can you be specific on your assessment? Even with "multi-line" keys, you're not CHANGING DATA, but simply making "joins" for display purposes.

                               

                              beverly

                              • 12. Re: Is many to many that bad?
                                richardsrussell

                                There was a post here a couple of weeks ago about a tangle of relationships that all had "Delete a record in this table if a related record is deleted in that table" turned on. The mere joining of tables doesn't cause loss of data integrity, but when you lose thousands of multiply related records by trying to delete just one, yeah, that can be an event of (as SReese observes) biblical proportions.

                                • 13. Re: Is many to many that bad?
                                  siplus

                                  Dear Bev, the "data integrity" is related to the "Anything" from the 2nd paragraph, not to the M&M from the first.

                                  • 14. Re: Is many to many that bad?
                                    sreese

                                    Beverly,

                                    I imagine he just means if there was any fashion that the data could be messed up. It could lead to a massive headache.

                                     

                                    Myself, I think the best way to do many to many is a join.

                                     

                                    Richard,

                                    I've seen that happen before. Its one of the main reasons I never check that check-box unless it is a one to many relationship where only the one can delete the many. Then again, at this point in my career I don't let anyone actually delete anything. I just flag it as deleted and call it a day. If anything has to be deleted I use a script to do it. Normally this is things like data from external sources that changes from week to week.

                                     

                                    Siplus,

                                    That is what I was thinking you meant.

                                     

                                    Have a great day,


                                    Scott

                                    1 2 Previous Next