October 25, 2016

SQL databases are like airstrips

At a very basic level, SQL databases are tables with an x and y axis that bear relationship to one another. Each column represents a property and each row represents an entry.

Remarkably though, both ‘entry’ and ‘property’ are symbiotes: the properties don’t have anything to apply or attach to without an entry; entries, on the other hand, cannot be described without properties:

# Properties existing without entries:
 ___________________________________
| property1 | property2 | property3 |
|-----------+-----------+-----------|
|     ∅     |     ∅     |     ∅     |
|-----------+-----------+-----------|
|     ∅     |     ∅     |     ∅     |
 -----------------------------------

# Entries existing without properties:
 ___________________________________
|     ∅     |     ∅     |     ∅     |
|-----------+-----------+-----------|
|   value   |   value   |   value   |
|-----------+-----------+-----------|
|   value   |   value   |   value   |
 -----------------------------------

# Values tie together properties(columns) and entries(rows) together:
 _____________________________
|        theodinproject       |
|-----+-----------+-----------|
| id  |   name    |   skills  |
|-----+-----------+-----------|
|  1  |   Kevin   |    Rails  |
|-----+-----------+-----------|
|  2  |   Cody    |    Ruby   |
|-----+-----------+-----------|
|  3  | Cornelius |    Ruby   |
------------------------------

We then use SQL, the structured query language, to retrieve sets of data from these relational databases:

SELECT name
FROM theodinproject

Retrieves the name column from the theodinproject table:

 ___________
|   name    |
|-----------|
|   Kevin   |
|-----------|
|   Cody    |
|-----------|
| Cornelius |
 -----------

On a single table, multiple columns are easy to get:

SELECT name, skills
FROM theodinproject

 ___________ ___________
|   name    |   skills  |
|-----------+-----------|
|   Kevin   |    Rails  |
|-----------+-----------|
|   Cody    |    Ruby   |
|-----------+-----------|
| Cornelius |    Ruby   |
------------------------

These examples are especially easy to grasp because we are gathering data on all entries and the table returned by SQL retains almost full semblance of the original table. The difficult part then, is to visualise this table as a fluid container where parts change readily like a Transformer would.

For example the following SQL command,

SELECT name, skills
FROM theodinproject
WHERE skills LIKE 'Ru%'

would produce a table only including with both the name and skills column but only entries for skills that starting with ‘Ru’:

# Bye Kevin (too good for us)
 ___________ ___________
|   name    |   skills  |
|-----------+-----------|
|   Cody    |    Ruby   |
|-----------+-----------|
| Cornelius |    Ruby   |
------------------------

Now although there are only two axes at work to represent the data here, the columns and rows have rearranged themselves from the initial table to match the query. How you interpret this table shuffling will determine how you visualuse more complex SQL commands.

The analogy I use to best visualise this table shuffling is to think of the database as an airstrip:

Airstrip

All the properties/columns are represented as airstrip lights:

  id      name      skills  
   ▼        ▼         ▼     
   ▼        ▼         ▼     
   ▼        ▼         ▼     
   ▼        ▼         ▼     
   ▼        ▼         ▼     
   ▼        ▼         ▼

While the entries/rows are represented by plane landings (don’t ask me why planes land tangentially to the airstrip lights… I’m trying my best to describe a SQL table):

  id      name      skills  
   1 ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ 
   
   2 ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ 
    
   3 ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞ ➞

When I call:

SELECT name, skills

I set up the name and skills airstrip lights. They go on for as far as the eye can see:

 name      skills  
   ▼         ▼     
   ▼         ▼     
   ▼         ▼     
   ▼         ▼     
   ▼         ▼     
   ▼         ▼

Then when I call:

SELECT name, skills
FROM theodinproject

I know that I am retrieving data from theodinproject airspace:

 _____________________________
|       theodinproject        |
|-----------------------------|
        name      skills  
          ▼         ▼     
          ▼         ▼     
          ▼         ▼     
          ▼         ▼     
          ▼         ▼     
          ▼         ▼

Then when I call the final part of the query:

SELECT name, skills
FROM theodinproject
WHERE skills LIKE 'Ru%'

This sets up the number of possible landings on our airstrip. Each landing corresponds to one entry:

|-----------------------------|
        name      skills  
          ▼         ▼     
➞ ➞ ➞ Cody ➞ ➞  Ruby ➞ ➞ ➞ ➞ 
          ▼         ▼     
 ➞ ➞ Cornelius ➞ Ruby ➞ ➞ ➞ ➞ 
          ▼         ▼     
          ▼         ▼

Which is really just a table:

 ___________ ___________
|   name    |   skills  |
|-----------+-----------|
|   Cody    |    Ruby   |
|-----------+-----------|
| Cornelius |    Ruby   |
------------------------

This analogy is really useful for me because it conveys how Ruby objects can be stored as persistent data. Those property columns are actually mirroring attributes declared with attr_reader :id, :name, :skills! And then each row entry conveys an instance of the class TheOdinProject:

class TheOdinProject
  attr_reader :id, :name, :skills
  
  def initialize(args)
    @id = args[:id]
    @name = args[:name]
    @skills = args[:skills]
  end

end

Wonderful. Now everything in SQL is an object too.