Database Correction Interlude

This is the third post in a series, documenting the progression of the project and the challenges I faced at each stage. I’m intending these posts to be almost reflective in nature, rather than very technical, though I will lightly justify some technical choices also. Once I feel that I can move the project out of a prototype stage, I’ll put up a more formal page documenting the technology and some overall reflections.

Because the state of the database kept bugging me, I went back to the original ER diagrams with my friend, and we came to a quite simple solution.

Just simply remove the Faculty relation, adding instead a ‘faculty’ attribute to both the School relation and Plan/Program relation.

A few reasons this is better and still valid:

  • I wouldn’t be saving any space with the dedicated relation anyway (unless I used ID numbers, but then there’s more querying to join Faculty name with its ID and to the School).
  • There are only around 20 – 30 unique Schools at UQ so, querying it with distinct  isn’t really an issue.
  • Because Plan/Program would be a 1:M relationship with Faculty anyway, you’d still have to search every row in the Plan/Program table to determine all study plans offered by a Faculty.
  • Because I’m not actually storing any further data about the faculty, there’s no point in devoting a whole table to it.

This really simplifies the data creation process, and keeping the faculty against both the school and plan whilst still allowing me to run interesting queries.

I also took the opportunity to update the database schema so the columns were of a decent length.


Posted by Anthony