need help with relationship logic for school registration database

[skula]

I am using "Filemaker Pro 10: The Missing Manual" to learn how to relate various tables in databases. I'm developing a database to track students and classes for a community learning program (non-profit org.)

Right now, I have a table with student info, including a student ID, a table with the list of classes offered this semester, with a class ID, and a table that lists first, second and third preferences for each student for each hour, as well as final placements in classes (by a value list that is created by the class list table).

Hope that makes sense so far.

So my question has to do with HOW to relate all these tables.

In the book, they discourage having "many to many" relationships, but I'm having a hard time conceptually getting past this: each students takes many classes, and each class has many students in it. I'm not sure how to connect these tables.

What I'm looking for is to be able to enter all the students' class preferences for each hour, then generate a count of how many students have selected each class as a first preference, so that I can see where we'd be overenrolled if we give everyone their first choice.

Then, when I place the students in their classes, I want to be able to generate a list of all the students in each class, to give to the teachers as a attendance sheet.

If you can help me conceptually understand how these tables should all relate, that would be very helpful. Thanks!

~Stacy

[comment]

each students takes many classes, and each class has many students in it. I'm not sure how to connect these tables.

Via a third table of Enrollments, where each record is an instance of a student taking a class.

What I'm looking for is to be able to enter all the students' class preferences for each hour

Is "hour" the same thing as a "class"?

[skula]

There are five hours of classes (like first period, second period, etc. in high school), and about 6 classes per hour. So I need to be able to generate a schedule of classes for each student (that's easy), and then a roster for each class, for teachers to take attendance with.

[comment]

I am not sure I follow the order of actions here. Are you saying the student indicates 3 preferences for each period? What if someone selects Math as their top preference for the first period and also as their third preference for the second period? Will they be allowed to get both?

[skula]

The preferences are irrelevant, actually. I don't need Filemaker to deal with the preferences.

I'll re-explain my problem, and hopefully it'll be clearer this time.

I have a table called "studentinfo" where I have student names and IDs, ages, addresses, etc. This also has a "schedule" area where I put down students' schedules--each student takes 5 classes, one per hour.

I have a second table called "classinfo" where I put down class ID and name, name of teacher, time, and location of the class.

I have a fourth table called "rosterinfo" that is related to "classinfo" by Class ID (many to many), and to "studentinfo" by Class ID to Hour 1 assignment AND Hour 2 assignment AND Hour 3 assignment AND Hour 4 assignment AND Hour 5 assignment. (all many to many).

So here's my version of the diagram:

Table: Studentinfo Table: Rosterinfo Table: Classinfo

Hour 1 assignment > ---------

Hour 2 assignment > ---------

Hour 3 assignment > --------- < Class ID > --------- < Class ID

Hour 4 assignment > ---------

Hour 5 assignment > ---------

My layout for "rosterinfo" has the class ID and class name up top, and then a portal below that, which looks up student info such as names, ages, and parent contact info. I'm just trying to get a record for every class, and a list of all the students that are taking each class. But nothing is coming up in my portals, so I'm assuming my relationships are all wrong. I don't know how to tell Filemaker to do what I want it to do.

Again, if you can help, that'd be great.

BTW, these are all many to many relationships because I wanted to determine the Class ID rather than have it be serialized. I don't know if that makes a huge difference.

[comment]

As I said in my first reply, you need a join table of Enrollments. A record in this table would look something like:

StudentID: 123

ClassID: 45

Hour: 2

This table is related to Students by StudentID and to Classes by ClassID. The Hour1, Hour2, etc. fields in the Students table are not required. To produce schedules for students, sort the enrollments by student and by hour (with a subsummary part for student). To list students by class, sort the enrollments by class.

See also:

http://www.fmforums.com/forum/showpost.php?post/246136/

[Vaughan]

I'll re-explain my problem, and hopefully it'll be clearer this time.

I have a table called "studentinfo"...

That's not your "problem" that's your "implementation". This is your problem description (so far):

... generate a count of how many students have selected each class as a first preference, so that I can see where we'd be overenrolled if we give everyone their first choice.

Everything else you've posted is your implementation detail. Clients often get this confused. :-)

Clients describe *what* they want to happen: the developer decides *how* it gets implemented.

[skula]

First of all, thanks for your suggestions so far. I'm still encountering major issues, though, and would appreciate your help.

I tried implementing what I want to happen with a fourth table called "enrollments", as suggested. I even downloaded a sample database from databasepros.com to reverse engineer, and copied the basic format as much as I know how to. But it isn't working like I want.

Here's an attachment with an image of my relationship window so you can see what I've done so far:

relatinship table.tiff

Here are my problems:

1. Even though I have "allow creation of records in this table" and "allow deletion..." under the Class Rosters section of the Edit Relationship box, when I add a class to the Class Info table, I don't get a new record in the Class Rosters layout. Shouldn't it be doing that?

2. None of my portals display any information when I click on them. I have a portal in "Class Rosters" that is connected with "Enrollments" and has fields that look up student name, age, and parent contact info from the "GenInfo" table. I also have a portal in "GenInfo" that is connected with Enrollments and has fields to look up course name, time, and location from the "Class Info" table. But it's like the fields are locked... nothing at all happens when i click on anything, and I can't even input data.

What the heck am I doing wrong? Thanks in advance for any and all help. Please be kind... I already feel like a dunce for not being able to get this right.

Okay, apparently I don't know how to get an image on here... any help on that too?

[comment]

I tried implementing what I want to happen with a fourth table called "enrollments"

Fourth?

[skula]

I was able to get the attachment... if you look you can see the four tables. I don't understand why there are four necessary, but the idea of an "enrollments" table came up in the responses AND in the sample database that I downloaded off of the website mentioned above--that database works, and I tried to copy the relationships in my own database. It just isn't working the same way.

Let me know if you need any other info. Thanks!

[comment]

I don't understand why there are four necessary

There are four tables, because the problem they are solving is different from the one you stated. That's probably also the reason why it doesn't work for you.

[skula]

So I can do without the Enrollments table?

[comment]

No, you can do without the "roster" table and link Enrollments directly to Classes. Have you looked at the join table demo I have mentioned?