9 Replies Latest reply on Feb 17, 2014 2:16 PM by mikebeargie

    DB structural help, linking data between two tables and viewing them as one

    djacobsmeyer

      Hey first time posting here. Forgive my lack of experience, I know just enough to make a mess.

       

      I am working on a project to track performance metrics for a group of people over time. I currently have two tables, the first is a list of the people on the team with some 'people' related attributes, and the second table is the metrics we track. I have a primary key and foreign key setup for each.

       

      Screen Shot 2014-02-07 at 12.20.08.png

       

      Here comes the question...

       

      My main layout is going to be an all in one leaderboard of sorts. I need to be able to see, in list format I think unless theres a better way, 4-5 fields from each table. I believe I may end up having to use a portal, which I'd be ok with. However I have yet to figure out how to link a specific persons metrics to their table in the Technicians table. I tried setting up a portal using the fields I want and it didn't link them, so I know the problem is more structural than design or layout oriented.

       

      I read about creating multiple TO's and self-join table and how that can give you more flexibility, but I'm not sure if that would help me or even specificly how to setup that up for this scenario.

       

      How should I modify my DB design to achieve what I'm trying to accomplish?

       

      Ten gold stars for the most helpful answer!

        • 1. Re: DB structural help, linking data between two tables and viewing them as one
          djacobsmeyer

          I ended up fixing the problem I had. I turned off prohibit modifying for the ID key fields, and it worked. I guess it makes sense.

          • 2. Re: DB structural help, linking data between two tables and viewing them as one
            mikebeargie

            Woah! That's like a mobius strip of relationships my friend. Welcome to technet! I hope I can help.

             

            If you're tracking metrics over time, you should have a one-to-many relationship between the Technicians (one) and metrics (many). Ideally, you'll be entering the metrics data over and over on a per-technician basis according to a schedule (IE one metrics record per user, per week). Then you can calculate some totals, averages and such on the technicians side. This way you could have your "leaderboard" of technicians without really having to display any related fields from the metrics table.

             

            So first I'd start by removing the MetricsID_fk - MetricsID_pk portion of your relationship.

             

            Then, create a metrics entry screen where you enter a new record of metrics, and select the PK of the Technician the metrics are for.

             

            Lastly, create your leaderboard screen based on your technicians table. Add some summary functions to calculate technician "totals", then display those fields on the leaderboard and sort accordingly.

            1 of 1 people found this helpful
            • 3. Re: DB structural help, linking data between two tables and viewing them as one
              djacobsmeyer

              Thanks so much for the welcome and help!

               

              I made the changes you suggested, and it does run a bit cleaner now. Thank you.

               

              Now that I have that worked out, I am looking to also add another feature of calculating those metric into point values that will pool together among technicians. My question is should I make a new table with point values that will pull the input data from the metrics, or can it be simplied all into the metrics table.

               

              This is my attempt to make a new table for points, but after I put my calculations in, the only results I get are zeros. Screen Shot 2014-02-14 at 21.11.47.png

               

              I'm sure I'm over engineering this again.

               

              Thanks!

              • 4. Re: DB structural help, linking data between two tables and viewing them as one
                mark_b

                It looks like your Metrics::TechniciansID_fk is set up as being unique. (graph looks like a one-to-one relationship).  If each technician is going to have many metrics, you will need to create multiple records using the same fk, so the fk can't be unique.  As for the Points, can you give an example.  Does the technician get points for having certain metrics?

                Cheers, Mark

                • 5. Re: DB structural help, linking data between two tables and viewing them as one
                  djacobsmeyer

                  Thanks Mark for digging deeper.

                   

                  How would you recommend making a change so that the TechniciansID_fk isn't unique? I thought I was ok in that regard, but if you have a better way I'd love to learn it!

                   

                  The point system is supposed to allow, for example, if the technician does a certain amount of appointments per hour then they get positive points, if lower than a certain number of appointments per hour they get negative points. Similar calculation structure for other metrics apply. I problem is figuring out how to draw a value from the Metrics::ApptsPerHr field into a calculation that will then assign a new point value into my points table. I set up two fields for each metric in the points table, thinking I needed on field to do the calculation and a separate one for the new value to be automatically entered in.

                   

                  Again, I can be the king of over-engineering. Thanks in advance!

                  • 6. Re: DB structural help, linking data between two tables and viewing them as one
                    mark_b

                    I'm not sure it actually IS unique, but looking at your relationship diagram it looks like one to one.  Here's how you tell (picture is worth 1000 words):

                    2014-02-15_16-16-02.png    The Left relationship line is single meaning one, the Right has Crow's Feet meaning many.

                     

                    2014-02-15_16-16-47.png     This one shows a "One-to-One" relationship.  The field on the side that has the "One" must be unique.

                     

                    2014-02-15_16-19-00.png  If you create a field with "Serial Number" checked, it will automatically be unique.

                     

                    2014-02-15_16-17-32.png  Or if not a Serial Number, you can check the "Unique value" option on validation to make that field be unique.

                     

                    Looking at your description, { if the technician does a certain amount of appointments per hour }, your statement is based on an attribute of the technician, so that calculation would be stored in the Technician table.  Your Points table might be used as a reference of how many metrics are needed to get points. (I could be all wrong there, because I still don't have a handle on what you want to accomplish.  I have an idea but not enough to make any clear statements.)  One other thing I noticed is the "TotalTechPointsEarned" in your Metrics table.  I would think that that field should be in the Technician table as a sum of points earned from the Metrics table.  You could do a "Self Join" (Metrics --- Metrics) to do the summary there, but I think that things that desribe some feature of the technician should be in the technician file.

                     

                    Since you are just beginning with Filemaker, I'd highly recommend that you get your hands on a copy of "Filemaker Training Series".  They walk you through the basics of relational database design and how to determine which fields go with what tables.

                    Good luck,

                    Mark

                    • 7. Re: DB structural help, linking data between two tables and viewing them as one
                      mikebeargie

                      Looking at his screenshot, there's a fork on the right side of the metrics to points relationship, however it is still improperly setup.

                       

                      Think of this in sentences:

                       

                      One technician has many metrics.

                      One metric has many points.

                       

                      If those statements are true, then your keys should look like:

                       

                      Technician Primary key (one) = Metric's technician foreign key (many)

                      Metric primary key (one) = points metric foreign key (many)

                       

                      You re-using the technician FK in the middle to relate to points seems not to be what you want.

                       

                      IMHO, every table you create should have the following fields:

                      -Primary Key (unique generated serial or UUID)

                      -Creation Timestamp (auto-enter)

                      -Creation Account (auto-enter)

                      -Modification Timestamp (auto-enter)

                      -Modification Account (auto-enter)

                       

                      Regardless of if you relate to the primary key or not, it should be present.

                       

                      I think you're confusing metrics and points. The total of the metrics can be calculated in the technician's table via the relationship to metrics. So in essence, your metrics ARE points. I've assembled this quick demo file to show something easy like this.

                      1 of 1 people found this helpful
                      • 8. Re: DB structural help, linking data between two tables and viewing them as one
                        djacobsmeyer

                        Thank you both so much for your help. Mike your demo file was very helpful in understanding your input, Thanks! I am pretty new at this, and I think I've bitten off more than I can chew. But, I'm not giving up.

                         

                        Let me explain a bit more about what I'm trying to accomplish. We are looking to make a game from our metrics to have what we do be more fun. There will also be teams of technicians that will pool their earned points together at the end of each week.

                         

                        Each technician takes appointments throughout the day. For each week we track how many appointments they take per hour, as well as some survey based results. The actual number that come from those metrics are not the final point values. We are looking to weight them differently. So assuming a perfect score for appointments per hour is 6, then we take the diffentiation from 3 appointments per hour, and multiply that by 10. The calculation we came up with to get the number of points from the original metric number is this: 

                         

                        If ( Metrics::ApptsPerHr ≥ 3 ; ApHr_Holding = (Metrics::ApptsPerHr  - 3) * 10 ; ApHr_Holding = (Metrics::ApptsPerHr  - 3) * 10

                         

                        )

                         

                        So an appointment per hour metric could come in at 4.4 --> but that then means that they earn 14 points for that week.

                         

                        Similar framework of calculation for the other metrics that then need to be transfered into a separate point value. Having said all of that, I could very well have some of these fields in the wrong tables. But metrics are separate from points.

                         

                        One Technician has many metrics

                        One metric has one point value

                         

                        At least, thats how I understand it to work based on what I want.

                        • 9. Re: DB structural help, linking data between two tables and viewing them as one
                          mikebeargie

                          You're starting to flesh out your database beyond the scope of your initial question. That's not a bad thing, but your goal should be to learn proper normalization and development techniques in your context, with most of the playing around done on your part as well. You just noted that one metric has one point value, but didn't answer why you would want to store it in a separate table? Metrics are separate from points, but per your calculation, metrics result in points, so I'd assume your point value calculation would be still in the metrics table. Now you've introduced a new table (appointments) as well. So how is that going to relate in to your database?

                           

                          There are usually no less than three ways to do something at any given time in FM (and other platforms as well). Choosing which way is most logical or preferred is usually a personal choice. IE, If I was tasked with this, I would probably remove the metrics table entirely and replace it with a virtual list style report table. That way I could generate a report in any timeframe (day, week, month, lifetime) and in any context (individual, team, or even by client). Making reporting data relational limits you to the confines of the related data. But that's also a viewpoint I've developed with 7 years of FM dev experience, and not necessarily an easy translation to type out in a forum.

                           

                          For normalization, Daniel Shanahan has a good series of youtube videos for FM:

                          http://www.youtube.com/playlist?list=PLPmnTFiU29hkqUXZULtKgYjOIzbJuwKr-

                           

                          Also, I'd start brushing up on ExecuteSQL(), it's invaluable for gathering statistic and summary data without any relationships.

                           

                          PS – the above questions were rhetorical on my part, I'm trying to get you to think for yourself a bit, but am glad to keep offering a bit of guidance. Figuring out things for yourself and having the "aha" moments is honestly the best way to learn.