7 Replies Latest reply on Jun 10, 2016 3:48 PM by easyaspi

    One big set of relationships or multiple smaller groups on the graph?

    user17152

      I'm in the process of updating/optimizing a project that was designed on the fly and has grown rather complex as a result.  It's a custom project management system, so the Projects table is the heart of the solution.  Other "modules" in the solution include Trouble Tickets, Work Orders, Purchase Orders, Submittals, etc.  Each of these has a parent Project.

       

      Currently the system is designed so that there is a group of related table instances for each module, but these modules aren't connected.  In other words, there is a group of related tables for the Project screens, one for the Trouble Ticket screens, etc.  On the graph, the Trouble Tickets spider does not connect to the Project spider.  One of the table instances in the Projects spider is for child Trouble Tickets.  One of the instances in the Trouble Tickets spider is for parent Project.

       

      My question is this...when designing a more complex solution, is it better to separate different aspects into their own sets of relationships on the graph or is it better to keep things connected?  In the example above, would I be better off leaving things as is or should I consider making a relationship from my Projects spider to my Trouble Tickets spider and get rid of the Project's child Trouble Ticket relationship and the Trouble Ticket's parent Project relationship?  By connecting the modules this way, I could eliminate additional redundant relationships, but the end result would be a very complex Projects spider on the graph.

       

      What's the best practice in a situation like this?  Are there performance considerations?  Do many smaller spiders with fewer relationships perform better/worse than a few big ones with lots of relationships?  I'd appreciate any links to other discussions or opinions on this topic.  Thanks for the advice!

        • 1. Re: One big set of relationships or multiple smaller groups on the graph?
          mgostovich

          Rob,

          First of all, what version of FileMaker are you using? Is the DB hosted on FMS or is it a standalone? Are you the only user entering data or do you have multiple users entering data into the various modules? Are you using mobile devices?

           

          All of the above things can and should shape the design of your new system. With a little more info, we should be able to help you with this question.

           

          Michael

          • 2. Re: One big set of relationships or multiple smaller groups on the graph?
            user17152

            Thanks for the reply Michael.

             

            Currently we are running Server 12 and FMP 13.  When this optimization update goes live in a few months, we will upgrade everything to 15.

             

            We have roughly 50 users with not more than 10 active simultaneously.  They work in all the different modules and might have multiple windows open, ie: a Work Order, two Trouble Tickets, and a Purchase Order, plus the main Project record.  Currently we "officially" support FMP logins only, but some people use Go on iPads and the solution more or less works for them.

             

            Part of the optimization goal is to make the whole thing work in Web Direct.  It works quite well as is, but the interface is bloated and slow to load in the browser.  Ultimately we want one primary interface that works across Pro, Go, and Web Direct.  We're considering a few iPad Pros for our installers.

             

            We may develop a few custom Go interfaces for iPhone that would allow access to specific modules/functions...but that's down the road.  If and when (iPhone) Mobile happens for us, it will be about accessing information, not creating records.  In other words, an installer might pull up his or her Work Orders and mark tasks complete.  But Work Orders won't be created on an iPhone.  They might be created on an iPad Pro, though, hence the goal to create one primary interface that works across Pro, Go, and Web Direct.

            • 3. Re: One big set of relationships or multiple smaller groups on the graph?
              siplus

              For me, form follows function.

               

              I usually setup things with 100 users and 500'000 records per table in mind.

               

              Recently, in a solution with 50 users and a double hop relationship, performance went from unusable to normal by eliminating just that one field which came from the double hop, so I am all for many spiders with just no articulations on their legs.

               

              everything should be as simple as it can be, but not simpler.

               

              and btw, Get ( CurrentTimeUTCMilliseconds ) is my best friend.

              1 of 1 people found this helpful
              • 4. Re: One big set of relationships or multiple smaller groups on the graph?
                bigtom

                There is also the consideration of how wide your tables are and how many records they have. It has been my experience that having multiple tables for web direct can be slightly faster. Although all the data is processed on the server it is not as big of an issue as it is with FMP client. This just limits the amount of data traveling to the browser to only that specific table which has only the data you need to see at that time.

                1 of 1 people found this helpful
                • 5. Re: One big set of relationships or multiple smaller groups on the graph?
                  karina

                  Hey,

                   

                  We've created a big ERP system with over a hundred tables and a lot of relations.

                   

                  Each table has his own position in the relationship graph.

                  This way we prevented a spiderweb and you can very easily see how the relations are connected.

                  Here I have a small sample.

                  I always work this way, because you never know how big your solution is gonna be and it's also easy to use in calculations etc.

                  1 of 1 people found this helpful
                  • 6. Re: One big set of relationships or multiple smaller groups on the graph?
                    mgostovich

                    I am kind of in the same boat Rob. I have the same basic setup going on with FMS 12 and FMP 13. I started this DB a long time ago on FMP 5.5 and have migrated it along to the point that now it just feels slow and cluncky. I am working of the rewrite to FMP15 now and I am most of the way there and have actually done some data import as a test with limited success.

                    As to your original question, As I understand the structure you have and the needs going forward, I would recommend complete inter-connectivity of your tables. The reason is that when you start making the web direct and FMGo layouts you may need to connect to the entirety or your DB to get your end users the information they need

                    1 of 1 people found this helpful
                    • 7. Re: One big set of relationships or multiple smaller groups on the graph?
                      easyaspi

                      Rob,

                       

                      If I understand you correctly, what you are describing is commonly referred to as Anchor/Buoy, or A/B.

                      It is a methodology for organizing the graph to increase clarity and reduce the possibility of unintended relationships.

                      Karina's file is an example of A/B.

                       

                      Some people love it, others don't.

                      Personally, I couldn't develop any other way.

                       

                      Mark

                      1 of 1 people found this helpful