Week 4 – The Internet of Some (Better) Things… the SQL

Monday morning code review was damning. We all turned up bright and excited to share our impressively snazzy Rock Paper Scissor apps and absorb the praise that would surely be lavished upon us.

giphy (2).gif

Instead, in a brutal assault on our web dev egos, our coach matter-of-factly informed us that we hadn’t actually made a web app at all. We are The Deceived. Apparently, what we had created was a simple series of static web pages… Ok fair enough, I can kinda see that.

Anyway, we were reassured that this week was The One. You know, The One Where We Spontaneously Morph Into Web Developers. (Finally. Jeez, it’s been three whole weeks already). Our task for the week was to build a bookmark manager web app. A user should be able to save links, tag them with multiple keywords and filter their saved links by these tags. In order to achieve this, we needed to get up close and personal with The Database.

‘Database Week’ as it is called, strikes fear into the hearts of many a Maker, but I was secretly looking forward to it. It might be something to do with the fact I’m a bonafide maths geek (or maybe because I’m just a little odd), but I do love me some data.

Databases

Whoop databases! Love them. But before we could truly experience the magic, we needed to equip ourselves with some totally rad new technologies. Dude.

First up: postgresql

A bitch to install but a beauty to behold. Scroll down to witness the slickness that is the user interface. Much graphics.

According to the website, postgresql is a “sophisticated open-source Object-Relational DBMS supporting almost all SQL constructs, including subselects, transactions, and user-defined types”. According to me, it’s a database manager.

In a nutshell, postgresql allows you to store data in separate tables within databases. Using SQL, you can interact with your databases – adding data, deleting data and querying data to your hearts content. DATA. Ahh.

Second up: DataMapper

Although playing with databases directly is super duper fun, SQL is rather clunky. To de-clunk our database interactions we called upon the services of DataMapper.

According to the website “DataMapper is an Object Relational Mapper written in Ruby”. According to me, that’s a pretty good explanation. ‘Grats DataMapper.

Using an Object Relational Mapper (or ORM) allows us to translate directly between our good ole Ruby code (the nuts and bolts of our application) and our databases (where all our real life data lives).

Turns out DataMapper is pretty bloody clever too. As well as creating and populating databases for you, it has a myriad of funky in-built capabilities and data validation methods that make life ever so much easier… yanno, if data manipulation is your thing.

Many-to-many relationships

Once we’d got to grips with our databases and ORM, we needed to decide how to use them – what kind of relationship do we have?

In the case of a bookmark manager, we are storing individual links – each of which can have multiple tags attached to them. We should be able to view our link and check all the tags we have associated with it. At the same time, we are storing individual tags – each of which can have multiple links attached to them. We should be able to search for a specific tag and check all the links we have associated with it. Got it?

The reciprocity of this requirement indicates that we are dealing with a many-to-many relationship – links have many tags and tags have many links – totally debaucherous.

So, how do we handle this in our database?

  1. We need a links table where links are added and serialised – this is created by DataMapper
  2. We need a tags table where tags are added and serialised – this is created by DataMapper too

Screen Shot 2016-09-13 at 23.25.48.png

Screen Shot 2016-09-13 at 23.26.15.png

Screen Shot 2016-09-13 at 23.26.32.png

And where does the magic happen? In a join table… which is is created by DataMapper. What did I even do this week? Did DataMapper write my whole project? Maybe.

A join table combines columns from multiple tables in a database so that relationships can blossom. I can now check what links have which tags (and which tags have which links!) by inspecting the matched up IDs in the join table. For example, if I filter for the tag ‘code’ (which has ID of 1) the join table sends me to two links (with IDs 1 and 2), which I can identify in the links table. Clever huh?

screen-shot-2016-09-13-at-23-27-02

And I bet you thought databases were dull…

Advertisements

One thought on “Week 4 – The Internet of Some (Better) Things… the SQL

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