Classic WTF: DATABASE ABNORMALIZATION 101

It's that time of year, we're taking a brief little summer break this week, and thus reaching back into the archives for some classics. Enjoy these, and in the meantime, keep those submissions coming! For today, we have a unique way to keep track of when classes start... Original. --Remy

Working as a DBA in academia, Paul received a notice that a certain newly migrated user schema, specifically the one used by the enrollment tracking system, had swelled to 281 tables and was growing. This had struck Paul as being very strange since the tracking system wasn't all that complicated.

When a student is registering for a class, and want to know if there's room left, they need two pieces of information - the Course ID and the Semester Number.

Course IDs are found by simply looking up the course in the Course Catalog. However, the semesters (or terms), denoted by a 4-digit number, can be a little tricky for new students. The first digit denotes the century (2 for the 21st century), the second and third digits are the specific year (08 for 2008, 09 for 2009, and so on) and the last digit denotes the month the term begins (1 for spring term, 6 for the summer term, and 9 for the fall term). Therefore, if you are looking for classes for the Spring 2009 Term use 2091 or if you are looking for Summer 08 classes use 2086, and for Fall 2010 use 2109.

The student enters the Course and Semester code and they get back a count of spaces left in the class.

Thinking that the system should really be, tops, five tables in all, Paul couldn't figure where all the tables were coming from, that is until he checked the layout and found a very creative way of storing data in a database.

Each table was in the format _Enroll_hours_<term>_<week> each for enrollment going back in history several years.

[Advertisement] Keep the plebs out of prod. Restrict NuGet feed privileges with ProGet. Learn more.