Potential database field optimizations

[ajcord]

1. Replace char(4) availability.semester with tinyint
   • Should save around 5.80 MB/semester
     • Assumes current average of 11,101 CRNs/semester (calculated from FA15, SP16, and FA16)
   • Would have to give each semester its own numeric ID - limited to 256 semesters
     • Queries become more complicated due to extra join, but should minimally impact speed due to very small size of semesters
2. Replace int availability.enrollmentstatus with tinyint
   • Should save around 5.80 MB/semester
   • No code effect
3. Replace int availability.crn with mediumint
   • Should save around 1.93 MB/semester
   • No code effect
   • Could almost get away with smallint, but the max CRN currently in the database (67030) is slightly too big.
     • Crazy idea: the min CRN is 10001, which seems like a predefined limit, so maybe subtract that from all CRNs to save space. But that is too risky if the CRN limits are ever adjusted. Better to spend an extra 1.93 MB for breathing room than to potentially have CRN collisions.
4. Replace varchar(50) sections.sectiontype field with char(3) codes (see here) or tinyint IDs
   • Should save 0.13 MB/semester using char(3) or 0.16 MB/semester using tinyint
     • Assumes average field length of 14.75 (calculated from FA15, SP16, and FA16)
   • Would require creating a table to map descriptions to codes/IDs
   • This mapping would have to be updated with any new section types
     • These very rarely change, if ever. Might be hardcoded into Banner.

Total: 13.66–13.69 MB saved per semester