Join Table that is a Child of a Join Table?

[ktalley]

Hello,

I need some advice on if this is possible or a good approach. Part of my database solution includes enrolling students into a summer music camp (an event) and then enrolling in classes in that particular camp. Here are the relevant tables I have:

Students

Contacts (faculty who teach at the camps and others)

Classes

Events (includes camps and other departmental events)

Student-Events (join table to events)

StudentEnrollment (join table to classes)

I currently have these relationships:

Students --< Student-Events >-- Events

Students --< StudentEnrollment >-- Classes

I also have a similar setup for the Contacts who are hired for each camp and assigned to teach each class.

Currently, the StudentEnrollment table does not contain an EventID or any link to a specific event; only a ClassID so I can get a roster of students in the same class.

This works fine, but I'm concerned about next year's enrollment. I could just delete all the entries in the StudentEnrollment table each year, but I wouldn't be able to track what students took each year. Would it be a good approach to have the StudentEnrollment table as a child table of the StudentEvent table?

Something like:

Students --< Student-Events --< StudentEnrollment

with

Student-Events >-- Events

StudentEnrollment >-- Classes

I've never seen a join-table linked with another join table, and I am probably making this too complicated. There's probably an easy solution staring me in the face and I just can't see it.

Thanks for your assistance

kt

[comment]

There's nothing unusual in linking a join table to another join table. However, you say that:

Part of my database solution includes enrolling students into a summer music camp (an event) and then enrolling in classes in that particular camp.

but your classes are not in any particular camp - otherwise you'd have:

Students --< Student-Events >-- Events

Students --< StudentEnrollment >-- Classes >-- Events

[ktalley]

Thanks for your reply. Your observation is correct (as usual).

Since the classes themselves rarely change, and are unique to each type of camp, and happen every year, I have a "Camp Name" field in the Classes table instead of linking a specific EventID to it. I currently enroll students into a class using a conditional value list that selects the camp name (not tied to an event in the even table), type of class, and the class itself.

I create a new 'event' for each camp every year since I hire different faculty and have different students attend each year. I set it up that way so that I don't have to choose the same 60 classes for each camp every year for the new 'event.' Perhaps there is better way of doing that?

[comment]

There are different ways - which is "better" depends on what's more convenient for your situation. You can either enroll student camp enrollments into classes (as suggested in your original post) or you can enroll students into camp class instances. These two are symmetrical.

Another option is to have a field in StudentEnrollments to indicate the year or the camp or ?? But you need to consider how that will satisfy your reporting needs.

[Ron Cates]

Since the classes themselves rarely change, and are unique to each type of camp, and happen every year, I have a "Camp Name" field in the Classes table instead of linking a specific EventID to it.

Since the classes themselves rarely change ≠ never

I can't help but think this will come back to bite you down the road. And that dog usually has much bigger teeth than you realise :)

[ktalley]

Hello Ron,

Very true. I've been bitten by that dog several times and am trying my best to avoid it in the future (-;

It's not like these classes are for any type of school credit - the camps last 4-6 days and we only need class rosters in order to take roll. So keeping track from year to year really isn't important. All the needed information to track is in other tables. I currently have a Year calc field in the enrollment table so in the class portal I have it filtered to display the classes of the current year.

Since I've made some poor decisions in the past, I keep trying to get it 'right.' But if it does what I need it to do and doesn't require a lot of extra steps, I should be content.

Thanks for the suggestions comment. I'll try them out and see which one seems to fit what we do