Week 4 was forewarned.  At the start of the course we were told students tend to find this week the hardest and most heavy going.  Bizarrely, given I’ve struggled a lot so far, I didn’t find this week too tricky.  To date, my hardest week has probably been week 6.

But I digress.  Let’s jump right in and reflect on database week!

Before we begin…

Week 3 taught us some basic facets of web apps using Sinatra.  Week 4 built on week 3 by introducing databases and full three tier web architecture.  In Week 3 we’d only been using two tier web architecture (front end server (Webrick) plus application engine (Sinatra)).

Three tier web architecture

Three tier web architecture looks something like the below:

Three Tier Web Architecture

As you can see, the client makes requests via their web browser, which are sent to a front end web server (tier one).  Next, the front end web server talks to the application engine (in week 2 this was Sinatra and, in later weeks, this has been Rails) containing the MVC (see here for an explanation of MVC) (tier two).  Finally, the application engine talks to the database (tier three) to retrieve data, which is then fed back up the chain.

Here’s another diagram illustrating the same, albeit with some more information concerning the languages, tech and frameworks often used for each tier in three tier web architecture.


Alongside the Makers Academy walkthroughs and linked online resources we had a hilarious interactive workshop where we were split into groups to roleplay the various roles in three tier web architecture.   Seeing your classmates make silly computer noises and run about between each other with HTTP requests and error messages really made it stick.


What’s a database?

A database is simply an organised collection of data.  A real world example would be a filing cabinet or a bookshop.  In either example data (files or books) are purposefully organised in a logical manner, e.g. books ordered by author and genre etc.

Why do web apps use databases?

Web apps use databases where there is a need for certain data to persist (i.e. be remembered) beyond a single use and/or the process that created that data.  There are lots of ways to do this but commonly a relational database management system (“RDBMS“) is used.

What’s a relational database?

A relational database uses a relational model of data.  Ok, so what’s that when it’s at home?

The relational model of data organises data into one or more tables (or “relations“) of columns and rows.  Rows are also called tuples or records.


Generally, each table/relation represents one entity type, e.g. a Facebook user.   The rows in each table represent instances of that type of entity, e.g. the Facebook user Alistair.  Columns represent the values attributed to that instance, e.g. Alistair’s age, city and login details.  Each row is assigned a unique key or unique identifier, typically an integer number, which is used to identify and retrieve that row.   A database will consist of many such tables, e.g. Facebook may have a Facebook users table, a facebook posts table and so on.

And those, in essence, are the basic ingredients for relational databases.

Mapping Ruby onto a database

To map Ruby onto a relational database we used DataMapper, an Object Relational Mapper (“ORM“).


Essentially an ORM is a programming technique for converting data between incompatible type systems.  I like to think of ORMs as a bit like a translator able to communicate information in two languages so that the same content can be understood by the native speakers of each of the two languages, thereby allowing them to communicate and get jobs done.  For the purpose of the web apps we built during week 3, we were mapping Ruby objects onto POSTgreSQL.  psql

POSTgreSQL is a powerful open source object-relational database system.  It’s written in C and implements the majority of the SQL:2011 standard, which is a version of structured query language (“SQL“, often pronounced “sequel”), which is a special-purpose programming language designed for managing databases.  In our web apps we’ve used POSTgreSQL to manage our databases and interact with them using the psql terminal tool and basic SQL commands.   See here for a nifty SQL cheatsheet.

In this way we can map Ruby objects in our web app (see earlier post on these here) onto records in our database, thereby adding persistence to our web app.


When designing, building and manipulating databases there is often the need to represent associations between different data objects.  There are three main types of associations.

One to One: one Amazon user has one profile.

One to Manyone Amazon user has many purchases.

Many to Many: many items have many category listings (e.g. a book on computing may be listed in both books and computing categories) and many category listings have many items (e.g. computing category will have many items).

To simulate the above in Ruby with Sinatra and DataMapper is relatively easy.

One to One: we can use the DataMapper Ruby syntax has 1, profile (or belongs_to :profile) in the User class for Amazon users and has 1, :user (or belongs_to :user) in the Profile class.  This adds one column to each of the User and Profile tables in the database.  In each case, the additional column lists a foreign key.  A foreign key is the ID of the corresponding row from a separate table.  For instance, in the Users table the foreign key for each row will be the ID of the corresponding row in the Profile table.  Doing so associates each User with its Profile and vice versa.

One to Many: we can use the syntax has_n :purchases in the User class for Amazon users and the belongs_to :user in the Purchase class.  At the database level this adds a foreign key to the Users table, associating each row in the Users table (i.e. each User) with the corresponding row in the Purchases table (i.e. each Purchase for that User).

Many to Many: we can use the syntax has_n :items, :through => Resource in the Category class and has_n :category, :through => Resource in the Category class.  To represent this association we have to create a new table, known as a join table.  This table will include two columns: a column for the item ids and another for the category ids.


Another big concept introduced to us this week was CRUD.  CRUD stands for Create Read Update Delete.  These are the four basic functions of persistent storage (the characteristic state of something that outlives the process that created it).mongodb-crud-operations1

Seems familiar? Well, if you’ve ever interacted with a personal computer before you will have used CRUD.  This very blog post uses CRUD.  I created it, I read it (i.e. previewed how it would look), updated it and, if I decide to, could ultimately delete it.

Three level validation

We were also teased with a little knowledge data validation and data integrity.  To this end we learnt about three level validation.  Briefly this means validating data inputs at in the browser (i.e. those pesky red messages you get when you incorrectly complete or forget to complete a field in an online form), at the server level using model validations and finally at the database level.  By doing so security is improved and data integrity (maintaining and assuring the accuracy and consistency of data) is enhanced.

User authentication and sign-up

My friend Tom works for a global IT security company, LogRhythm.  He’s really passionate about security and knows alot about hacking and security vulnerabilities.  Chatting with him about those topics always fascinated me, especially when there’s now relatively realistic (so I’m told) shows like Mr. Robot (well worth a watch) about hacking.  As a result, I loved the parts of this week that touched on security, particularly password hashingcyber-security.jpg

How do websites protect passwords?

Essentially when you create a user account password for a website there is usually a hashing algorithm.  That algorithm does the following:

  1. Generates a random piece of text, known as a salt.
  2. The salt is then added to the string of alphanumeric characters you submitted into the form as your desired password on sign-up, e.g. “password12345″(which would be a terrible password…)) might get added to “dyba28b” to create “dyba28bpassword12345”.
  3.  “dyba28bpassword12345” is then run through the hashing algorithm, which turns it into a long string of seemingly random letters and numbers (the hashed password + salt).
  4. The hashed password + salt is stored in the database against that user, as is the unique salt, which is stored separately.  It’s worth noting each salt is random for each password.
  5. When you next try to login you will need to re-enter your password.  When doing so the salt is retrieved from the database and added back to the user’s inputted password.  If the user’s input plus the salt after passing through the hashing algorithm equals the hashed password + salt stored in the database the user will be logged in.  If not, they will be denied access and prompted to try again.

The amazing thing with all of the above is that even very slight variations in passwords will result in widly different hashed passwords.  For instance:


To achieve this with our web apps at Makers Academy this week we used Bcrypt.  Bcrypt is a password hashing function.  To implement this in our database we had to first install the Bcrypt ruby gem and follow the documentation to set up our user authentication systems for sign-ups, sign-ins and sign-outs.

The challenges

Bookmark manager

The week challenge was to create a bookmark manager.  Although it’s not the sexiest web app (and it’s been done to death), it is an easy to understand structure that lends itself well to teaching the fundamental concepts of databases and three tier web architecture.

I didn’t really struggle with this challenge.  I had many great pairing sessions this week and took quite a bit of time to read around each area and check my understanding with my pairs. This worked really well and I feel I came away from week 4 with a fairly solid understanding of the basics of relational databases.twitter-logo

Check out my code here.


Continuing the concepts from the week we were tasked with creating a basic Twitter clone, Chitter.  I really enjoyed this as I felt like Jack Dorsey (Twitter co-founder and current CEO)… sadly my efforts won’t be outstripping Twitter’s daily active users.

It was a fun challenge and I managed to complete it relatively quickly and tried my hand at integrating some Bootstrap, albeit badly.  Check out my efforts here but be warned I decided (as I was feeling rather childish) to brand my Chitter with a ruder rhyming word…