Title
Question/suggestions regarding relationships
Post
Hi,
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!
When you find yourself maintaining two very similar tables (in this case Past and Present Students) it is usually a sign that you should have the records in just one table, and add a field called 'Status' which defines the difference that would previously have allocated them to one table or the other.
In this case I would keep all students in one table, and have a Status Field that is a Radio Button with values 'Graduate' and 'Current'. It will not be hard for even tech-unsavvy people to tick the additional 'Current' button when defining their searches. Additionally, if they really do find that a problem, you could think about scripting to automatically show only only current students, say.
I think they will be able to master searching in one file every time, rather than having to remember which file to look in. And you will find it a lot easier to maintain one table than two, and be exporting complete records, assignments, etc as students graduate.