Restructuring Existing Database

[ktalley]

Hello,

I need some assistance overhauling an existing database. I've inherited a database with one table and I am wanting to know the best way to split it up to do what I want.

The database is for a summer music camp. Here is the current setup:

Table 1 - Faculty Member Information

Name

8:25 Class Name

8:25 Class Location

9:30 Rehearsal/Sectional Name

9:30 Rehearsal/Sectional Location

11:00 Rehearsal/Sectional Name

11:00 Rehearsal/Sectional Location

1:00 Class Name

1:00 Class Location

etc.

Lots of other information not really relevant (fax, cell number, salary, email...)

What I want to do is to be able to create several layouts to display different schedules and ease data input from year to year and any changes that might occur in the current year.

Only one faculty member will teach each class and that class will only be in one location. I would like a way to select a faculty member, class and class location (using drop-down value lists to reduce errors) and have it update both individual faculty member's record and the class record.

I'm thinking I need create a table for each class such as below with a relationship using "Class Name" between table 1 and table 2

Table 2 - 8:25 Class

Class Name

Class Location

Class Instructor

Is this the best way to do this?

I would like to have a master schedule layout that will show all classes, locations and faculty members that will dynamically update as I change who teaches what. Currently, this is being done manually in a Word document every year. Is this even possible in Filemaker?

Sorry if I'm naive about this. I just don't know where to start and I've been searching and trial/error for days with little to show for it. Thank you for your help!

kt

[comment]

Do you need to keep previous years schedules?

[ktalley]

No, I don't need to keep the previous year's schedule. Thanks

[comment]

OK, that makes it much simpler. You should start with two tables, Faculty and Classes:

Faculty:

• FacultyID (number, auto-enter serial number)

• Name

... more fields describing the faculty member...

Classes:

• ClassID (number, auto-enter serial number)

• FacultyID (number)

• Name

• Location

... more fields describing the class...

Define a relationship between these two, matching on FacultyID. That should take care of most of stuff, except the time/s at which a class is held. I don't think I fully understood this point (sorry, should have asked about that too): is your schedule repeating daily, weekly, or...? And is each class held only once in the cycle, or can it have several instances (e.g. Tuesdays and Wednesdays at 11:00)?

[ktalley]

Thanks for your reply. I'm probably not explaining this very well. I'm caught between what has been done in the past and what I really want to do.

I've setup two tables and created a serial-number FacultyID field in the faculty table and a ClassID in the class table as you suggested and matched FacultyID in both tables. Should I have them be able to create records in the related table?

I think I will need a table for each class (8:25 Class Table, 9:30 Class Table, 11:00 Class Table, etc.) as they are a different set of classes for each time block. The same classes are held each day at the same time; location and instructor are also the same.

At the end of all of this I want to have 2 different types of layouts:

1. A layout that will list the faculty member and the classes they are teaching including times and locations.

2. A master schedule that will include all the classes, locations and teachers. Since all the classes are the same every day, each class will only be listed once.

In future years, I would just change who's teaching what and not have to enter everything again in multiple locations.

Which table do I store the data, and where should a portal link be used to display the information from the other table? If a faculty member who is teaching a class changes, where do I change that and will it dynamically update the layouts? Apparently I overestimated my understanding of relational databases...

This is what I have now:

Faculty Table

FacultyID: 1

Name: Bob Smith

8:25 Class: Do I enter class name here or use a portal to 8:25 Class Table?

8:25 Class Location:Same question as Class Name

9:30 Class:

9:30 Class Location:

etc.

8:25 Class Table

ClassID: 1

Name: Drum Set Fundamentals

Location: Fine Arts 102

FacultyID: Do I enter anything in this field?

Faculty Name:Enter Bob Smith? Do I enter this here or use a portal to Faculty Table?

9:30 Class Table

Same as above except different name, location and instructor

Is that clear on what I want to do? Thanks for your patience

kt

[comment]

You definitely do NOT need a table for each class. If the same classes are held each day at the same time, then you just need to add a StartTime field to the Classes table, and you're done - apart from creating some layouts to ease data entry and reporting.

To clarify again: there should be no fields in the Faculty table that describe a class; similarly, except for FacultyID, there should be no fields in the Classes table that describe a faculty member. If you need the teacher's name, you get it directly from the related record in Faculty.

To show the courses assigned to a faculty member, place a portal to Courses on the layout of Faculty.

To view the entire schedule, go to a list layout of Courses and sort them by StartTime.

[ktalley]

Thank you very much. I have a much better idea of what is going on now. I should be able to make the database much more efficient. However, I think I'm in way over my head with what ideally I would like to do with the layout/schedule.

I have attached a word document that shows one of the schedules I would like to use. Is there any way Filemaker could fill in those fields in this way? Scripting or merging data with a word document? I just don't know what might be possible.

The problem is I have schedules for each band, for each faculty member and for the camp as a whole. The data that changes from year to year has to manually be updated in the current schedules. It would be great if I could have layouts that would be dynamically updated to present the changes from year to year since the format is basically the same and the names just change. The data is all in the database, just getting presented seems to be the problem.

Oh well, that's why I pay my office workers to do the tedious items.

Thanks for your help.

schedule.zip

[comment]

Filemaker is not as flexible as Word, but see if this could serve as a starting point (I borrowed some data from you since I was too lazy to make up my own...).

CoursesSchedule.zip

[ktalley]

Thank you for taking the time to do that. That is very helpful and gives me a lot of ideas where to go from here.

I appreciate your help!

[ktalley]

Okay, I'm up for the challenge of getting my inherited database streamlined for this summer's band camps and need some additional advice. The information earlier in this thread and suggestions from comment were of great help for last summer's camp. I would now like to take this one step further and mix in the camper information.

I currently have a database file for Faculty and one for Students in Filemaker 9.

The faculty database has only one table with information about each faculty member including needing to reference what classes they teach (location and time) as well as the names of students in each class. I've moved the classes table to the student database as it seems more appropriate there.

The student database has the following tables:

Students (name, instrument, address, etc. plus 2 different classes from which they can choose)

Auditions (location, room and facultyID for auditions - only happens once)

Elective Classes (location, room and faculty for 2 different classes - same two classes for 4 days at the same time for each student)

Each table has a serialized, unique ID.

Questions I have:

- Student workers will enter information from mailed-in applications into the Student database. Is there a way that I can have a drop-down menu of the names of the elective classes they can choose from during data entry that will "enroll" students in each class section? (I can get a list various ways, but none seem to work the way I want that will link to the class ID and show location and teacher information).

I assume I need a "join" table between the classes and the student tables. I've looked at some examples that will allow enrollment into a class from a class table, but not from a student table.

- In addition to the 2 classes, each student will be in two band rehearsals each day (there are 5 bands based upon how they audition) and one sectional rehearsal. So that would be 3 additional classes they will be in every day.

We currently have a code system we enter after auditions that allows us to sort through scripting to run sectional and band rosters after the students have auditioned. For example, 1st chair Flute in the top band would be assigned the code H A1 (1st letter=instrument, 2nd letter=band, number=chair) while 3rd chair trumpet in the 4th band would be assigned U D3 after the auditions. This works fine, and keeps the bands in "score order" (a must for band geeks) but I'm wondering if this is the best way? It would be ideal if we could link a faculty member to each section for each band (some faculty will teach different sections as they sometimes happen at different times).

- One faculty member will teach each class. I need to be able to pull information to create a faculty teaching schedule (already done with suggestions from last year) AND a list of students in each class that the faculty member teaches. I've been able to do this with some creative scripting, but want to know if there is a proper way to do it.

I hope this makes sense. I am willing to scrap what we've got now and start over if needed. There has been a lot of compartmentalization and duplication of information in the past due to multiple people needing to work with similar data at the same time. That is going away since we are going to be running Server 11 and we can now use more information in less places.

Thank you for reading through all of that!

[comment]

I've moved the classes table to the student database as it seems more appropriate there.

It would be best to keep all your tables in the same file.

The student database has the following tables:

Students (name, instrument, address, etc. plus 2 different classes from which they can choose)

The Students table should have only data that describes students. Their chosen classes should be in another table, joining Students and Classes.

I assume I need a "join" table between the classes and the student tables. I've looked at some examples that will allow enrollment into a class from a class table, but not from a student table.

The arrangement:

Students -< Enrollments >- Classes

is symmetrical - you can create enrollment records from either side.

We currently have a code system...

Don't see why you need this, since you have (or should have) a dedicated field for every component of the code. In any case, you should use the meaningless serial IDs for relationships.

One faculty member will teach each class.

If that's true, then you only need a FacultyID field in the Classes table. However, if the class is taught by Adam this year and by Betty the next, then the above is no longer true - and you need a join table for the assignments.

I think I will need a table for each class (8:25 Class Table, 9:30 Class Table, 11:00 Class Table, etc.)

I don't think so. A class is a class - the time a class is being given is merely an attribute of the class (i.e. a field in the Classes table).

[ktalley]

Thanks for the quick reply. I know I should have everything in one file; I'll just have to overcome my fear of data corruption with more backups.

I do have the Students -< Enrollment >- Classes relationship, except for some reason it's showing up as as many:many from Classes to Enrollment.

All the classes, regardless of time or location are listed in the class table. I'm probably missing something basic. Attached is my current relationship graph. (I currently have extra fields in some of the tables as I'm experimenting)

bandcamp1.tiff

Are there other fields that need to be in the Enrollment Table?

I thought it would be a good idea to have locations as a separate table, but I'm thinking I should just move location to a field in the Classes Table.

When I am entering data in the BC_Student table, how do I select a class for them to enroll? Is there a way to show both faculty assigned to the class and the location after they are assigned to the class? I'm not sure what I should be referencing in my layout.

Faculty do change from year to year, so I will need a join table there.

Apparently I know only enough to be dangerous. Thanks for your patience!

[comment]

I do have the Students -< Enrollment >- Classes relationship, except for some reason it's showing up as as many:many from Classes to Enrollment.

The most likely reason for this is that Classes::ClassID is not defined to auto-enter a serial number.

Are there other fields that need to be in the Enrollment Table?

The year could be useful - in case a student needs to repeat a class.

I thought it would be a good idea to have locations as a separate table, but I'm thinking I should just move location to a field in the Classes Table.

It depends: if location's name can change, it would be better to have a Locations table, and use a LocationID field in the Classes table.

When I am entering data in the BC_Student table, how do I select a class for them to enroll?

First, set the relationship Students -< Enrollments to allow creation of new records in the Enrollments table. Place a portal to Enrollments on the Students layout. Create a new enrollment by selecting a value for the Enrollments::ClassID field placed in the portal.

[ktalley]

The most likely reason for this is that Classes::ClassID is not defined to auto-enter a serial number.

Yep, that was the problem.

First, set the relationship Students -< Enrollments to allow creation of new records in the Enrollments table. Place a portal to Enrollments on the Students layout. Create a new enrollment by selecting a value for the Enrollments::ClassID field placed in the portal.

That worked great. I want to double check one more thing. For the "join" table for faculty, since the faculty teaching the classes will change from year to year, I need to create a new table and not use the join table from the Students -< Classes ?

Now I have this:

BC_Students -< Enrollment >- Classes

Faculty -< Faculty_Enrollment >- Classes

I can now enroll students by class ID (I would rather be able to use text, but I can provide the students doing the data entry a list of the class ID numbers to enter) and see the faculty teaching it.

I still haven't wrapped my head completely around the join table, but I know where to start reading now.

I think I have enough to work with for now, but I'm sure I'll be back with some additional questions. Thank you so much for your help

[comment]

I need to create a new table and not use the join table from the Students -< Classes ?

That's correct: the Enrollments table joins Students and Classes, the other table joins Faculty and Classes.

I can now enroll students by class ID (I would rather be able to use text

You can do both, if you define a value list to use values from Classes::ClassID, also show values from Classes::ClassName.

To display the selected class name, you can place the related Classes::ClassName in the portal (make it non-enterable to prevent accidental modification) - or use a pop-menu to select the ClassID.