To the above table I have two personal observations:
1. The table looks like the result of a join.
2. The table looks like a work table, prepping for a report.
Obviously there is no one solution, so I have to agree to... keep our options open.
The problem arises when for the same int values, we apply different grading scales.
4 could mean A or F (100 or 40).
As for all the other types, there are a lot of grading systems and grading scales. Trying to oversimplify the grading systems and the grading scales for the grading systems by reducing them to basic columns creates confusion and restricts the adoptions of new similar or even completely different systems and scales.
So, having just the (grade type, grade) couple in the lookup table to represent a grade (instead of a n-uple of null columns), separate from the grades systems classification table, works better, in my personal opinion.
On the other hand, you could have (grade type, grade) couple, where grade is always expressed as percentage, and interpret the values in different systems: integer, text, based on notation conventions.
EX: 2 | 80.00, is int:8 and 3 | 80.00 is B.
'2' grade type being int, '3' grade type being text.
1 | %
2 | Z
3 | α
203 | 158 | 1 | 80.00
508 | 158 | 2 | 80.00
1056 | 158 | 3 | 80.00
B | 80.00
to the above post, i have only one personal observation --
1. sure... whatever...
i'm going to wait for ryan before i comment again
I would store the grade as a float and have a separate column for the desired conversion/display format. I don't see any reason why it is necessary to store different formats since the conversion can be derived from the decimal one. Storing separate formats seems to be making the problem for complex than it actually is. if you want you could even use a case statement to handle the conversion of a decimal value to a format such as; A, C-, etc since that logic *should be fixed. Also, using a single column will make aggregate calculations less convoluted and more efficient. I just don't understand why it is necessary to store formatted values in the database when those values can be derived in either the query or application side using the most specific decimal (float) data type.
Thinking about it even more I don't even think the format is necessary – considering that would be based on the context which the data (grades) where being viewed. So all you really need is a single column for the grade as a decimal and handle the formatting either within queries on a case by case basis or application side. If your using an ORM this would be a prime example to use a virtual property for the different grade type conversions from the base decimal one.