1 2 Previous Next 15 Replies Latest reply on Jan 17, 2017 2:18 PM by Malcolm

    Modification or starting over?


      I work for an office that offers funding to students interested in conducting research with our institution's faculty members. The office has many faculty, who mentor many students through a variety of programs. Our office office has been tracking student participation in each program, but we would like to also know more about faculty involvement in each program. I'm not sure that our current FM database was built in a way that can support this. If you were to start from scratch, what would your tables be and how would you setup the relationships?

        • 1. Re: Modification or starting over?

          Data modelling is a nice exercise and helps you to get intimately familiar with your data … 


          My recommendation would be to study the section about database modelling in the FileMaker Help; also analyze your data flow. Check the above narrative (which is just the surface of the entire story) and isolate nouns (which are candidates for tables), their relationships and their interactions:


          • Student / Faculty – could be one table, or two. Could be a generic Person table with specific flags. There is usually not one "correct" way to do this.

          • Program

          • StudentInProgram – a join table derived from the description "many faculty, who mentor many students through a variety of programs". If you have a Personal table, this could be called (equally generically PeopleInProgram and adding a field to denote the role a person plays.


          Recording faculty participation depends … well, on faculty participation: is the relationship between the students and the faculty members in a program generic, or are there personal mentors, or both? This will tell you if you need another table, or if maybe a modification/extension of the join table will do.


          Another (or additional) way is to look at the reports you want to generate and ask where each of data is supposed to come from: do you have the tables and fields to store these data; do you have the correct data structure to create such lists and summaries?


          Also try to do this without the database, or even the computer; use a sheet of paper and try to model your data in a diagram.


          Hope that helps somehow. I realise this can be daunting at the first go, so don't hesitate to ask if you have more questions.

          2 of 2 people found this helpful
          • 2. Re: Modification or starting over?



            Thank you for your response. After re-reading your message a few times, I realized that I was creating TOs for the wrong tables. I have attached a screen shot of the solution that I came up with and would appreciate your (and anyone else's) feedback.Screen Shot 2016-11-22 at 2.31.54 PM.png


            I am currently unable to retrieve related data from the Student Data table in the Faculty Data layout even with the join table "student_JOIN_faculty." Let's say I want to pull the data from Student Data::student_FullClass in to the WISP program portal on the Faculty Data layout. I want this data from Student Data so that I know the names and class years of the students that an individual faculty member has worked via each program (e.g., WISP, Grants, Soph Jr Research, PresScholar). The portal in the Faculty Data layout is setup up to show related records from WISP interns 2 (a table occurrence to the Faculty Data table) when WISP interns 2::advisor_dartid = Faculty Data::advisor_dartid. Currently, Studnet_Data::student_FullClass remains blank in the portal even with the table "student_JOIN_faculty". Ideas?

            • 3. Re: Modification or starting over?

              The screenshot of the graph is helpful, but I would also like to see a screen shot of the "Tables" section of Manage Database.

              • 4. Re: Modification or starting over?

                Duplicate the Student Data table occurrence and link the duplicate  to "WISP Interns 2" using "student_dartid = student_dartid". You can then put the "student_fullclass" field from "Student Data 2" into the "WISP Interns 2" portal.

                • 5. Re: Modification or starting over?

                  Hi Bruce,


                  Apologies for the delay. I only work on this project when time allows. I thought I had figured it out, but I am still having issues pulling data from the Student Data table in to the Faculty Table. I can pull related student data if it exists in the various program tables, which are linked to the Faculty Data table as table occurences, but data that actually resides in Student Data is not as easily accessible.


                  I have attached a test version of the database that I am trying to build upon. I think that the issue now is with my join table not having any data in it, and therefore anything from the Student Data table isn't actually connected in any meaningful way to the Faculty Data table. I'm not sure how to match everyone up in the join table in our actual database, though, with almost 14,000 student records and over 1,000 faculty records without manually entering corresponding IDs from the program records.


                  What are you thoughts?



                  • 6. Re: Modification or starting over?

                    Unusable. Start over. A zillion too many redundant fields.

                    • 7. Re: Modification or starting over?

                      Are there any free online resources to assist with the data modeling process? I don't even know where to begin with figuring out the architecture for a new solution.



                      • 8. Re: Modification or starting over?

                        Have you looked at the resources here? There is the Users Guide, which discusses methods of relating tables in a very simple way.


                        The method is to plan, on paper is usually easiest, what information you need to track. You do this at a high level and, incrementally, zoom into the details.




                        In your data there are lots of fields named "field1", "field2", "field3". That's a sure sign that you really need an extra table to store the field. It may seem odd, at first, but it really is much easier once you get going.

                        2 of 2 people found this helpful
                        • 9. Re: Modification or starting over?

                          In addition, there are lots of tables where for instance there is a foreign key for the advisor; yet the table has a bunch of fields identical to the fields in the Faculty table. Same for student fields. Likewise for Department of the advisor or faculty person. Long calculation fields when the related department field should just be displayed.


                          If the existing file was kept and modified, about 80% of the fields could be deleted.

                          • 10. Re: Modification or starting over?

                            another great resource on this:

                            * http://www.nightwingenterprises.com/Resources/approaches_to_graph_modeling_en.pdf

                            "Approaches to Graph Modeling

                            9 Steps Toward Enlightenment With FileMaker Pro"

                            and this:


                            (and other great articles on this site)


                            • 11. Re: Modification or starting over?

                              I would use a real ERD tool to build and display the database.


                              On the Mac, a tool like SQL Editor will not only read/create an actual FileMaker database, but it presets a real db design environment.


                              SQLEditor for Mac OS X


                              If you design before build (think building a house, with blueprints first), you should have a much better result! And, yes, with a bad design, starting over isn't that uncommon either.


                              Here's a quick M:M diagram from SQL Editor with the DDL on the left. Once you get your diagram the way you want, SQL Editor will CREATE your actual FMP database! (And read from your existing one too):

                              These databases are built-in to this $79 tool:


                              There is no short-cutting the DB design phase.


                              HOPE THIS HELPS.

                              2 of 2 people found this helpful
                              • 12. Re: Modification or starting over?

                                Thank you everyone for your advice! Our scenario is a bit tricky because we are trying to capture snapshots in time of data when students apply to our various programs as well as finalized data when they graduate, which is the reason for many of the fields that initially appear redundant -  while many of the fields have the same name across different tables, much of the data in each table is actually different depending on changes that students make as they progress through coursework (e.g. intended major as a freshman, sophomore, junior, senior vs. what major they actually completed.


                                I've spent a lot of time on Lynda.com as well as YouTube and other websites learning about data modeling, but I have not been able to come up with a new model that works for us - I am sure that I am just missing something. I have not, however, come across the resources that you have recommended, so I will take a look at them. Thanks again!

                                • 13. Re: Modification or starting over?

                                  Hi Malcom,


                                  I keep coming back to a high level data modeling diagram that looks something like this:FM DB modeling.png


                                  The problem, I think, is that we want to maintain separate program tables and layouts because they are snapshots in time of a student's trajectory at our institution. Essentially, many students can participate in many programs with different faculty members; many faculty members can work with multiple students in multiple programs. It appears that "programs" is the central component, but the database that I am working with wasn't setup or designed with expansion in mind - what began as a simple solution for tracking only student data as it relates two programs is now working towards tracking both faculty and students as they interact with each other across potentially seven research programs. I thought that a new model would be in order, which is fine because I want to do some visual updating to the layouts as well, but I haven't been able to come up with a solid framework (A/B or otherwise) that works with our numerous many-to-many relationships. I'll keep working on it, though.

                                  • 14. Re: Modification or starting over?

                                    What are the risks of this model, where sssjrs_STUDENT_studentid, grants_STUDENT_studentid, wisp_STUDENT_studentid, and presscholar_STUDENT_studentid are all TOs of the Student Data table (not shown below)? Screen Shot 2017-01-17 at 12.55.44 PM.png

                                    1 2 Previous Next