2 Replies Latest reply on Jan 13, 2012 4:12 PM by JacquiAdler

    Question/suggestions regarding relationships



      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!

        • 1. Re: Question/suggestions regarding relationships

          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.

          • 2. Re: Question/suggestions regarding relationships

            Thank you for your reply.  What you said makes sense and I think I am going to go with your suggestion. I was starting to lean that way, so it was nice to get my concerns validated!