Three small questions regarding datatypes and redundant indexes in MySQL

[QUOTE=itmitică;5156843]The grade_type_id would point to the right grades table, and the grade_id would point to a record in that particular grades table.[/quote]this is a hack, and will never allow proper foreign keys to be declared

also, where is the actual grade stored? for integer grades, you would declare a table with 100 rows, one for each grade? and use a surrogate grade_id to point to the row for, say, a grade of 98?

the hackishness compounds astonishingly

Exactly my point! :wink:

[QUOTE=itmitică;5156843]
All in all, having int and percentage saved as text, and to have only one grades table would be better, I think. What I suggested first. Doing something like ‘85.25’*1 would implicitly cast the int/percentage value stored as text back to numeric.

GRADE_TYPES

1 | Z (http://en.wikipedia.org/wiki/Integer)
2 | % (http://en.wikipedia.org/wiki/Percentage)
3 | α (http://en.wikipedia.org/wiki/Alpha)

LOOKUP

203 | 158 | 1 | 8
508 | 158 | 2 | 86.56
1056 | 158 | 3 | B+[/QUOTE]

[QUOTE=itmitică;5156843]GRADE_TYPES

1 | Z (http://en.wikipedia.org/wiki/Integer)
2 | % (http://en.wikipedia.org/wiki/Percentage)
3 | α (http://en.wikipedia.org/wiki/Alpha)

LOOKUP

203 | 158 | 1 | 8
508 | 158 | 2 | 86.56
1056 | 158 | 3 | B+[/QUOTE]

LOOKUP

203 | 158 | 8
508 | 158 | 86.56
1056 | 158 | B+

FTFY


SELECT *
  FROM Lookup
 WHERE grade_type_id = 3

vs


SELECT
       CASE 
         WHEN isdigits(grade) THEN NULL
         ELSE (SOME_QUERY)
       END
  FROM Lookup;

So allow me to FTFY:

LOOKUP

203 | 158 | 1 | 8
508 | 158 | 2 | 86.56
1056 | 158 | 3 | B+

 Lookup Table
 ------------------------------------------------------------
 tests_id | students_id | grade_int | grade_dec | grade_str |
 ------------------------------------------------------------
   203    |     158     |    88     |   NULL    |   NULL
   508    |     158     |   NULL    |   86.56   |   NULL
  1056    |     158     |   NULL    |   NULL    |    B+

SELECT *
  FROM Lookup
 WHERE grade_str IS NOT NULL  

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.

GRADE_TYPES

1 | %
2 | Z
3 | α

LOOKUP

203 | 158 | 1 | 80.00
508 | 158 | 2 | 80.00
1056 | 158 | 3 | 80.00

GRADES_ALPHA

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.