What My Teacher Didn’t Teach Me: Views
My teacher in the university taught me how to translate relationships among data models into a relational schema. For example for the following ER Diagram,
I was taught that to list down magazine’s authors, I would have to create a separate table with the foreign key ids from the author and magazine table. Before I found myself making use of triggers to populate the magazine-author table whenever an article was published. Triggers were a nightmare. At one point, I couldn’t figure out why my database tests were inserting initial data two times into a relationship table. The culprit as I found out was that I forgot there was already a trigger that was populating a relationship table after inserts on other tables. Hence, there was no need to populate the relationship table. I thought that was nasty and that I would never use triggers again if only for the mere fact that you could forget they exist.
Of course you could always join. But I was too lazy for that. Some ownerships go a long way up or down whichever way you look at it.
As I have found out however, I think Views posit a better solution to show relationships in some cases. Here are just some things that I like about them:
1. They are easier to query. They abstract the more complicated query of joining author to article to magazine just to show a list of magazine author’s.
2. They make more sense. Compare the following two queries that lists a particular magazine’s authors:
SELECT name FROM author JOIN submissions ON author.id = submissions.author_id JOIN publications ON submission.article_id = publications.article_id WHERE publications.magazine_id = 1
to this one:
SELECT name FROM magazine_authors WHERE magazine_id = 1;
I am really not so sure what the cons are right now. Performance perhaps? Maybe they will reveal themselves later on.