Question/suggestions regarding relationships
I am in the process of developing a database for one of my school partners. Let me start by saying I am NOT an expert, though I am working hard to learn!
The database is for an educational program that runs summer programs and has a school year model as well. What I have created thus far is one table for Current Students, one for Graduated Students, a table for the Summer Activities, and a table called "Student Assignments" which relates the Summer Activities and the Students tables (basically, for every activity a student does over the summer, there is one record, so each student may have multiple activities and each activity will have multiple students.)
The problem I am running into is that the way I have it set up (and perhaps I didn't do this is the smartest way, but like I said, I am learning) I will have to have two Student Assignment tables...one for current students and one for graduated students. I don't know if this is the smartest thing to do? Or perhaps I am looking at it wrong and there is a way to keep just one Student Assignment table?
I know I could keep the graduates and the current students in one table, but the folks I am making this database for are not very tech savy, and I would like to keep the data as clean an simple to search as possible.
Any feedback or suggestions? Thanks in advance!