Music Department Database

[ktalley]

Hello,

I am setting up a single database to manage our university's music department current setup of multiple databases. This is a ground up redesign and I'm needing some confirmation that I'm not doing anything the long way around.

FYI, I'm using the FM Starting Point database as a jumping off point (although I am copying it manually so that I learn more about filemaker) for basic setup, design and layouts.

The main conceptual trouble I'm having is with my Students table. We have hundreds of potential students every year that we track for recruiting purposes (and for their participation in a summer camp, honor band, etc.). I need a way to keep track of students that are potential recruits (current high school students) by their High School Graduation Year, current university music majors (and their associated requirements) and then graduates (alumni for donation purposes and future potential students they are teaching in public school).

Any of these students can take one of two basic paths:

HS Student (from one of hundreds of different schools) --> Our University Student --> Graduate (alumni) --> Public School Music Teacher

or

HS Student --> Other university's student

Obviously we want to follow the first student type all the way through while the second student type becomes unimportant to our database.

It seems the most logical to have a Student Table for high school students (summer camp participation, honor bands, etc.), a University Student Table for current university students and a Contacts table for everyone else. Some people will need to be moved from table to table, but it wouldn't be that many every year. Is this a good approach?

Second Issue:

We have a lot of letters/emails that go out when students become juniors and when they become seniors in high school (the form letter exist in our database). I need a way of easily sorting students that are coming up on graduating from high school. I was thinking of creating a separate table that contains the High School Graduation Year so that I can easy get the students we need to reach. Is this the best way of tracking students by graduation year? I could accomplish the same thing with a scripted find, but that doesn't seem as flexible. Is there anything else that should be added to this new table?

With this approach, when I add a new student record, I also add a record in the graduation table, correct?

I want to get my underlying structure correct so I don't have to redo a lot later.

Thank you for your assistance

kt

[comment]

Some people will need to be moved from table to table, but it wouldn't be that many every year. Is this a good approach?

No, it's not. You should either mark the student's status in a field of the existing table, or create a related record in another table. A lot depends on what else you are doing here: for example, if your students need to enroll in program/s, the very existence of an enrollment record identifies them as such.

I was thinking of creating a separate table that contains the High School Graduation Year so that I can easy get the students we need to reach.

Not sure how such table would help.

[ktalley]

Thanks for your reply.

We get information about students in a variety of ways. Many are associated with a summer camp (which I have an enrollment table for), some are just names and addresses given to us from band directors or guidance counselors, some are in an honor band and some come to a clinic. Many students attend several things. One goal is to get all the students who have the same graduating date (which, of course changes every year) from all our sources the easiest.

Ideally, I would have a layout that I could use a drop-down menu to select the graduating year and have all students we have contact information for be listed. Then I could break that list down further to instrument or voice type and send emails/letters to that subset. I can do this now through finds, but am curious if having the relationship do the sorting would be more efficient.

I currently have an "Event" table that associates a high school student with a particular music camp or honor band. I have this primarily so I can run roll sheets and put students in elective classes for each camp they attend. Should I just make something like a "General Contact" record in the Event table to catch the students that don't attend any real event we have but we still have information about and want to contact?

Does the distinction of being a high school student, a current university student or a contact (i.e., band director or alumni) become irrelevant and the information that is different about them goes in a separate table (or their "status")?

Thanks

kt

[comment]

I can do this now through finds, but am curious if having the relationship do the sorting would be more efficient.

I don't think so. It may be more convenient to view matching records in a portal, but it's no more efficient than performing a find.

Does the distinction of being a high school student, a current university student or a contact (i.e., band director or alumni) become irrelevant and the information that is different about them goes in a separate table (or their "status")?

I am not sure I understand what you are asking; let me ask you instead: other than the common data (name, address etc.) what information - if any - is collected from your students only or from alumni only?

BTW, why does the graduating date change every year?

[ktalley]

For example, this year we have a set of students that will graduate in 2012. We send them a certain set of letters/emails. The students who graduate in 2013, get a different set of mailings. Next year, the 2013 set will get the mailings that the 2012 group received and so on. So I guess the individual student's graduating year never changes, but our sorting does - easily done with a find though. I just was curious if this was similar to a class/semester situation, but I see now that it isn't.

High school students, especially those that audition for us, will have ACT Score, GPA, Parent's name, Music Director's name, High School, etc. Things that are completely unimportant once they get out of high school and into the university. The university students will have a set of music requirements and some will be student-workers at our camps or for the department. Once that student graduates from college, none of that is important anymore and where they end up teaching and living is what we track.

Perhaps I'm making it too complicated. I just didn't want a bunch of empty fields in every record that only apply to people at one, and only one, stage.

I've been through the filemaker self-paced training and have learned a lot. But since I can't see down the road what problems I might encounter by making a poor choice up front, I want to set it up right the best I can. Especially since my day job is being a musician

[comment]

Well, the simple solution is to have a few fields that may be empty for some types of records. In most cases, this simple solution is also the best one.

The other option is to have a satellite table for each sub-type, where the type-specific fields live, with a one-to-one relationship to the common super-type table. But this is not so easy to implement and I am not sure the effort would be worthwhile in the case you describe.

[haensz]

I agree. Sounds like you can use the one "Students" table and add a few new fields:

Status (values would be HS Student, Alumni, Other Univ Student, etc)

HS_GradYear (would only need populated if Students::Status field = HS Student)

This will let you filter or sort easily based on the Students::Status field. And you'll be able to handle the issue of which HS Students to start contacting as they enter their Senior year.

If you are worried about the table growing too large because of the # of HS students each year that enroll elsewhere:

1 -- Don't worry, FM 11 can handle it

2 -- If you really want, you could empty these "Lost Students" out periodically. Maybe you'd want to import them into a Lost_Students table and then clean up the Students table.

[Tom R.]

Hi

I'm not sure any of this will be helpful - I've been up way later than I should, so may not be thinking clearly. But:

Would it make sense, rather than thinking of HS Students, College Students, Faculty, etc. as separate things, to think of a person as just a person whose memberships in different groups might change over time? That way you could have a People table, and an Organization table. High Schools, Colleges, Honor Bands, Band Camps, etc. could all be separate records in the Organization table. Because a person can belong to many groups (over time or possibly at same time?) and a group can have many people, you would need a join table between the two - a Members table for example that would join one person to one group. Then in the Members table you could store data for enrollment date, graduation date, etc., position within the organization (1st Chair Tuba, Adjunct Professor of Music Performance, etc.) That way, from the Person perspective you could see all the organizations that person has belonged to, and from the Organization perspective you could see all the current or past members.

At any rate, just some food for thought. Good luck with your project.

Tom

(1st Chair Tuba, Texas All-State band 1987)