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?

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.

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.

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.

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?

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.

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.

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.

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.

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!

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

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.

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)?

Don't make the "time series" a difficult issue. Handle it like an invoice. You have a student-course record ( an enrolment ) and you then attach line items for each thing that happens. If a student enrols in a course, you create a student-course record. The first line item is the date of enrolment and the note "enrolled". If a student quits a course, you create a new line item for the student-course record with the date they quit and the note "quit". If they complete the course, you create a line item with the date of the completion.

You have a fairly complex environment that you have to describe in the database. The picture you've shown may be suitable but I don't know what the tables are that sit between Faculty and the Student TOs. Are they programs? If they are you may want to generalise a bit further. You want to avoid unnecessary duplication and you want to ensure complete representation of the data.