9 Replies Latest reply on Jan 31, 2014 2:16 PM by philmodjunk

    entity relationship diagram

    kenne120

      Title

      entity relationship diagram

      Post

           Is there somewhere I could submit my relationships diagram for other more experienced users to critique? I am new to FileMaker, and while I think I have a solid idea of how to relate all the data that I need to work with, it would be helpful to run the diagram by some more experienced eyes who could catch potential issues that I might miss due to my inexperience. 

           Thank you!

        • 1. Re: entity relationship diagram
          philmodjunk

               You can upload graphic images such as a screen shot of Manage | Database | Relationships directly to your next post to this forum provided you use the controls below Post a new Image and use a file format from the list of formats listed next to the controls for selecting an image to upload.

               But I suggest that you not just upload an image of the ER. How you need the database to function for you is crucial to developing an effective data model and that info won't be in your ER diagram so please also provide a narrative description of what you want to accomplish to help others should they choose to analyze your ER diagram.

          • 2. Re: entity relationship diagram
            kenne120

                 The database I have been asked to create will organize information about students who have completed programming through our office. (We are a teacher education/enrichment program based within the college of education at a large state university.) The students in our database are all current or past students of the university, and all of them have a unique 9-digit student ID number issued through the university. (UID)


                 Our office runs many different types of programs for these students, so one necessity is that we track the dates and programs that an individual student has participated in. Once these students have completed our programs, and graduated from the university, we want track if they are hired into the public school system as teachers, and if so, what school they are at, what subject/grade level they teach, and what years they are actively teaching at the school. (ex. A student may teach at a school from 2011-2012, but then take a different job at another school for 2012-2013.)

                  

                 Ideally, I would like to have a layout that displays all the necessary information on an individual student (programs participated in, contact information, test scores, schools they’ve taught at), AND a separate layout that would display contact information for an individual school (address, phone number), plus a portal that would show which of our students have worked at that school, what year, grade level/subject, etc.

            • 3. Re: entity relationship diagram
              philmodjunk

                   The overall data model looks sound. Each table clearly represents a specific set of entities and you can clearly see one to many, many to many etc relationships that are consistent with the table names and your description of the how this system will be used.

                   But I'd rework the match fields that you are using.

                   

                        The students in our database are all current or past students of the university, and all of them have a unique 9-digit student ID number issued through the university. (UID)

                   If I am reading field names correctly, it appears that you are storing this value in Students::_kp_UID. I would put this in a different field in the Students table and use an internally generated Identifier--such as an auto-entered serial number--to link records in Students to other tables. That change is just a few percentage points safer than using an identifier from an external source as your primary key. If the values from your external source change or one is accidentally entered incorrectly, you can have issues that you can avoid if you use the internally generated value instead.

                   Even more importantly, the School Name field is best not used as the primary key for records in the school table nor as a match field to the StudentEmployment join table. An internally generated student ID field would make for a safer option and also avoids possible issues with schools that might not have unique names.

              • 4. Re: entity relationship diagram
                kenne120

                     Thank you for the feedback! I am presenting a new draft of this database at the end of the month, and am going to use an auto entered serial number for each Student and School.  

                • 5. Re: entity relationship diagram
                  philmodjunk

                       For your Student to Student Employment to School many to many relationship, you may find that this demo file is a source of useful ideas:

                  https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                       If you are using FileMaker 12 or newer, you can use Open from FileMaker's File menu to open this file and produce a copy converted to the .fmp12 format.

                  • 6. Re: entity relationship diagram
                    kenne120

                         Correct me if I'm wrong: it would be possible to generate auto entered serial numbers for records, but to only allow certain privilege sets to view that serial number field. 

                         (In my case, I am thinking this database layout would work best if just the Full Access users could see the auto entered serial number for Schools and Students. The Read Only and Data Entry Only users would, ideally, not be able to see this field- in order to minimize confusion.)

                    • 7. Re: entity relationship diagram
                      philmodjunk

                           I don't see why any user of any access level would benefit from seeing the serial number field. This is a "behind the scenes" field that need not be placed on any layout even though it is used to link records in your database.

                      • 8. Re: entity relationship diagram
                        kenne120

                             Ok even better! I wasn't sure if I could hide it from everyone's view or not. 

                        • 9. Re: entity relationship diagram
                          philmodjunk

                               The serial number doesn't need to be visible, but many value list set ups for selecting a school or student will require that the field be present on the layout. This does not, however, have to be the case:

                               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7