Straight Edge Life

Ok. Yet another entry about my favorite database system, MySQL. MySQL 5.0 was recently released and I’ll say straight away that it’s a massive improvement on MySQL 4.x. I have only one word for this particular entry. Views. Views are a fairly standard feature for RDBMSs. Views are this really nifty way to make dealing with the disjointed nature relational databases much easier. Take for example a database of class lists for teachers. Each teacher has a list of students. You have a table filled with your teachers, another table full of students, and a third that contains only student ids and teacher ids. You need this third table because there isn’t a one-to-one relationship between students and teachers. One student can have multiple teachers and one teacher can have multiple students. The third table which holds only the student and teacher ids is commonly called a Join table. It contains no real information, it’s just a way to connect other bits of info together. Got it? Good. Ok…

So let’s say you want to get a list of students for a given teacher, “Ari Denison”. Without Views you would be forced to tell the database to spit out a combination of info from all three of our tables: Give me the first and last name of students who all share the same teacher id. It would look something like this without using views…

SELECT
  students.full_name,
  teachers.full_name
FROM
  students,
  teachers,
  join_table
WHERE
  teachers.full_name = “aran j. denison”
AND
  teachers.id = join_table.teacher_id
AND
  students.id = join_table.student_id;

A bit confusing eh? Especially just to pull some really simple data. Unfortunately the advantage of an RDBMS is also one big pain in the butt. Multiple tables related by common fields. So… Views were created to eliminate or reduce this complexity. Actually, they have a number of other purposes, but this is my favorite.

A View allows you to create a sort of fourth table that already contains a combination of the other three so that you can tell the database to give you data from the View rather than from the other three like so…

SELECT
  student_name,
  teacher_name,
FROM
  student_teacher_view
WHERE
  teacher_name = “ari_denison”

Way better, eh? Well, it’s way, way, way better when you are pulling info from more than 3 tables. The WHERE stuff can get amazingly complex. Creating Views greatly simplifies lookups and that makes me smile just a bit each time I eliminate an hour of coding by using them. I’m glad that Views and a few other stylin’ features like Triggers and Stored Procedures have been worked into MySQL 5.0. They will quite literally shave hours off my work week.