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
distinctisn’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.
