Rockstar Engineering

What My Teacher Didn’t Teach Me: Views

Posted in Software Design, Software Development by Jose Asuncion on April 11, 2012

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.

About these ads

2 Responses

Subscribe to comments with RSS.

  1. dragoonxryu said, on April 11, 2012 at 3:34 pm

    “Performance perhaps? Maybe they will reveal themselves later on. ”

    And you shouldn’t think about it right now. Programmer time is more valuable than machine time.
    Optimize when you know what to optimize.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: