Trying To Wrap My Head Around Database Queries

Hello DB Queries…

I guess I knew it was coming at some point, but I wasn’t as ready for it as… well, lets just leave it there.

Using WordPress for the last number of years has given me a very small glimpse into the world of databases. When I say small glimpse I mean just that. I knew what the database did (sort of) I knew where the database lived (sort of) and finally I new that it was a very smart way to be able to store and access data on a dynamic website.

But I really didn’t know what I was doing with databases. Every time I had to make a change that required diving into the database, it was with the knowledge that I could totally ruin my entire installation of WordPress. After much googling and being sure to create a backup of the database I could creep into the back end and make the change. Usually it worked just fine… but having a backup saved my bacon a time or two.

So… What about Ruby and Sinatra and sqlite?

Earlier this week we (the Ruby on Rails class at The Iron Yard) dove into the concept of keeping data between program sessions. So data that is created and saved in one instance of the program could still be accessed even if we shut the program down and then start it back up.

The databases we are using right now are squlite… A pretty little database that does just enough. (It actually probably does way more than I know about.) In fact if I am not mistaking the newcomer to the blog platform world Ghost is using (by default) squlite.

I Still Don’t Understand It All

Yesterday we were introduced to the concept of having multiple tables within our database and then pulling data together from these several tables to help keep data organized.

At the time that our guest speaker was talking about inner joins and outer joins, I heard an audible “whoosh” as it all went over my head.

Sure, I could see sort of how queries worked and why they would be helpful, but I still didn’t understand exactly how it was working.

Like a lot of things I’m learning these days… I got a glimpse of the “concept” during class, but didn’t really figure things out till later while working on my homework.

Getting it to work…

As I dove into homework I realized that the initial setup of the different tables that I would join later was pretty important. It took me literally an hour (ok, maybe an hour and a half) to get the initial table structure figured out, set-up and then filled with data.

Once the data was all safely inside of my database tables I was able to start the fun of trying to write queries that tied all the info together.

Around 7:00 last night I finally was able to figure out how to do a “table join” in a query. That feeling of “wow, that could be really powerful” was pretty intense.

So Where do we go from here?

Well our homework for the weekend is pretty ambitious. At least I think it is… but that is the nature of things here at The Iron Yard. A pool is created and then we are tossed into one end (the deep end) and asked to swim to the other end.

What exactly is in the homework for the weekend? We need to build a front end for the back end database structure that we have already created.

This front end will give users the ability to create new entries for the tables and then tie entries from different tables together.

The usefulness of this is becoming quite evident. This is what makes a website more than simply static content. This is what makes it possible to build things like gmail, Facebook, twitter and a host of other web applications.

How the homework will turn out, time will tell, but I’m quite confident that I will learn at least ten new things this weekend.

So much for Easter meaning a break from work… no vacation for this coder in training.

But I will do my best to not work on homework on Sunday. Having one day each week that is a full break from all things code has proven to be helpful in the last few weeks. I believe it will be helpful again this week end.