Either of these.
If you don't have to create yet another relationship to pull the data, sometimes that's ok, so the second method works. You can just find (or even use as a filter in a portal) to show the student grade level and scores.
But purist will say make another table...
On 06 Apr 2012, at 6:39 PM, Jeremy Brown asked in whole or in part:
Should I create a new "StudentGrade" table with three fields: __pk_StudentID ; AcademicYear; GradeLevel
Or should I create a new field in the academic info table and record the grade level that they earned the particular score? Is one better? Is one more efficient?
Beverly is right, you can do it either way, but think this through very carefully. I can think of a scenario in which a student may be in more than one "grade level" in one "academic" year. I have even known of schools where a student may be in different grade levels for different subjects. 4th grade Math and 5th grade English for example, even where the subject does not match the students' "grade".
The question I always ask my clients is - what is "usual" what is "rare" what is "possible" and what is "never" and then I design the database for "rare" or even "possible". Even if these scenarios do not occur in your school now - are they possible in the future? And if they are possible, how much of a pain will it be to change your design to accomodate them?
I would personally lean toward storing the grade AND academic year in the Grade table and then use a report or portal filter to show the appropriate results - NOT a join table, since academic year and grade level may not be enough of a filter for what you need..
I'm considering doing it this way. I understand the purist's point of view, and i'm trying to be pure.Since I do have a lot of other academic data, i feel if i create a join table with student's grades in there by year, i will not have to rewrite the grade in each table for each record.
At my school, there's never a time when a kid would be in 2 separate grades. We do have a few kids in 8th grade doing 9th grade math, but as of now, that' considered their 'math class' regardless of what grade it is in. Karen, so your consieration wouldn't happen, ever. In the 5 years I've been there, we've never had this situation. For you, Karen, does that get rid of this consideration?
Speaking of join tables. How many join tables should I use? Should I use one for multiple parent phone numbers? What's a general rule about join tables? Is it just "if one kid could have multiple _________, then a join table is necessary?
I have quite a few join tables now, developing them without complete confidence i'm doing it correct. The data comes up neatly whenever it is called by other teachers, but I feel like I'm missing something - that i'm doing too many join tables.
As my quote on here says, I'm learning so much about that which I do now yet know.....
My comments were just comments - just something for you to consider and discard if you think its unneeded. Just some ideas to get you thinking about HOW to approach the problem - NOT a suggestion for how to solve it. My recommendation is for you to creatively imagine every possible and some impossible scenarios for using the data and try to determine which ones could impact your solution.
By the way - no matter how much I do that, I always, always, ALWAYS find out later that I missed some crazy thing that no one told me they needed that makes me revamp something in a completely different way.
Now, the "general rule" for join tables is that you might need to use them whenever you have a "many-to-many" relationship. But it really depends on when and how you want to look at the data.
A good example would be students and courses. One student can be in many courses. One course can have many students. So you would use a join table, say "registrations" to connect them, IF you wanted to report what courses a student was currently or past registered for, or IF you wanted to report which students were in a course. (Obviously, you probably do want to see those things in this example!! But again, it is a good exercise to think about WHY you need a join table, as opposed to simply doing a query/find to report data.
Usually when I am doing a registration join, that table may also store the final grade or a pass/fail field for that student in that course. Multiple grades for a course are usually related to the student through the course/registration table, so a separate join table for grade list is not needed.
I have never used a join table for phone numbers (yet). Usually one person has many phone numbers, and it's possible for one phone number to have many users, but most people don't create a join table for that - just a related file to store multiple phone numbers for one person (one-to-many relationship - no join). While there may be a couple people with the same phone number, it isn't like there are hundreds of people with the same phone number (or if there are, then many it's a "company" table that joins them together). And it's unlikely that you would need to report or print a phone list using data in a join table, so it's an unnecessary complication. On the other hand, if your project is publishing the white or yellow pages, then maybe...
Even purists are never pure, Jeremy. You need to design the best structure for the data and the uses you have. While it is good to know what the "rules" are, it is better to understand when and when NOT to apply them in order to have the best performing, most usable database for your needs.
Hope that helps!
You have some good advice. I realized that my use of the join table was wrong, as far as using it to describe what i'm doing.
I've got a very decent system going, learned from 3 years of strife, reading, and just figuring it out. I'm looking to see if there are ways to optimize the solution.
Thanks for the good ideas.