3 Replies Latest reply on Apr 17, 2014 1:20 PM by philmodjunk

    Relationship Structure for Academic Department Database

    pmuhlrad

      Title

      Relationship Structure for Academic Department Database

      Post


           Hello everybody.  I'm a relative novice with FileMaker Pro, and want to build a database containing all of the members of an academic university department (e.g., Faculty, Graduate Students, Post-docs, Staff, etc.), their general contact information, their relations to each other (e.g., which grad students and post-docs "belong" to which faculty members), as well as their publications, grants, and grant applications.  Setting aside, for now, the publications, grants, and applications, I'm still struggling to set up the tables and relationships for the various types of department members.  On another forum, I was advised to create a single table for "People," which includes everybody, use different occurrences of the "People" table to represent "Grad Students" "Post-docs" etc., and have another table that contains a separate record for each "Role" (Grad Student, Post-doc, Faculty, etc.), which I'll use to join together the other tables.  But I can't seem to figure out how to pull it all together.  Could some kind soul please help walk me through this?  Many thanks.

        • 1. Re: Relationship Structure for Academic Department Database
          philmodjunk

               Any one helping you may be handicapped by the fact that we probably do not know enough about how such an organization works and thus could easily recommend a solution that creates a working database but perhaps not one that works well for what you want it to do.

               A single unified table of contact information, whether you call it "people", "personnel", "contacts" is certainly a good start as it allows you to set up a single table for recording the basic contact info for all students and faculty.

               

                    (e.g., which grad students and post-docs "belong" to which faculty members)

               That would appear to need these basic relationships:

               Faculty-----<Roles>------Students

               Faculty::__pkPeopleID = Roles::_fkFacultyID
               Students::__pkPeopleID = Roles::_fkStudentID

               Where Faculty and Student are both occurrences of your People data source table. A field in Roles can identify the nature of the relationship. ("Graduate", "Post-Do"c, etc.)

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Relationship Structure for Academic Department Database
            pmuhlrad

                 Thanks for your amazingly quick response!  I appreciate that I'm not giving all the intricacies of the relationships, and am happy to provide more.  In brief, a faculty member can have multiple students and multiple post-docs, but each post-doc and each graduate student works under a single faculty member.  Your notation makes sense to me.  I'll take some time to digest your answer, and then probably (undoubtedly) return for followup questions.  Thanks again.

            • 3. Re: Relationship Structure for Academic Department Database
              philmodjunk

                   Then you may not need the Roles table.

                   Faculty----<Students

                   Faculty::__pkPeopleID = Students::_fkFacultyID

                   may be all that you need with "role" a field in the Students (People) table.