Database Explanation

2 Comments

What is a database?

You’ve probably used or seen an Excel spreadsheet.  Well, a database looks similar, but it’s a lot more powerful, a database can do things that Excel only dreamed of doing.  A “database” is made up of “tables” which are made up of a bunch of “columns” and “rows”.

Excel Terminology | Database Terminology
-----------------------------------------
Excel File        |  Database
Sheets            |  Tables
Letter Columns    |  Columns
Number Rows       |  Rows

Each table holds a specific type of records or rows.  So the tables of a simple database “schema” (or design) would look like this:

+-----------------------------+
| Tables_in_truth_development |
+-----------------------------+
| answers                     |
| choices                     |
| polls                       |
| users                       |
+-----------------------------+

Notice how it is broken up, there is a table for users in there.  That will hold the information for all the users.  We make tables logically for the different items we would like to keep track of.

Databases that are used most commonly are called “Relational Databases”.  What that means is that each of the tables can link to another table.  The most common way to imagine databases is to draw webs how they relate.  Truth Database
As you can see in the picture: A User is related to polls and answers.  A Poll is related to answers, choices and users.  A Choice is related to polls and answers.  Finally, an Answer is related to choices, polls, and users.

This lets us do stuff like to find a User record and then see all his answers and polls.

Now that we know a little bit more about tables, lets look and what a table consists of.  It is made up of columns (or fields) that we want to track. If we were to look at it it would look like this:

+-------------+
| Field       |
+-------------+
| id          |
| facebook_id |
| name        |
| created_at  |
| updated_at  |
+-------------+

Now if you want to picture it in your mind each field would be a letter in Excel.

A table is also made up of rows (or records) which is one full set of filled out columns.  If you were to look at it it would look like this:

+----+-------------+-----------+---------------------+---------------------+
| id | facebook_id | name      | created_at          | updated_at          |
+----+-------------+-----------+---------------------+---------------------+
|  2 |   678674096 | Dan Ahern | 2008-08-08 23:47:36 | 2008-08-08 23:47:36 |
+----+-------------+-----------+---------------------+---------------------+

Rows are the numbers in excel.

The way we link the tables together is to put information from one table into another.  Lets look at a Poll:

+----+---------+---------------------+---------------------+--------------------+
| id | user_id | question            | created_at          | updated_at         |
+----+---------+---------------------+---------------------+--------------------+
| 11 |       2 | What should I wear? | 2008-08-08 23:38:43 | 2008-08-08 23:38:43|
+----+---------+---------------------+---------------------+--------------------+

Notice how the poll has a column called “user_id” and that column has the same number as the “id” field in user?  This is how we know who the poll belongs to.

And then if we wanted to see the choices for the poll we can use the “id” column from poll and find those numbers in choices in the column called “poll_id”, and we get back this:

+----+---------+-----------------+---------------------+---------------------+
| id | poll_id | name            | created_at          | updated_at          |
+----+---------+-----------------+---------------------+---------------------+
| 33 |      11 | Nothing         | 2008-08-08 23:38:43 | 2008-08-08 23:38:43 |
| 34 |      11 | A Monkey        | 2008-08-08 23:38:43 | 2008-08-08 23:38:43 |
| 35 |      11 | T-Shirt & Jeans | 2008-08-08 23:38:43 | 2008-08-08 23:38:43 |
+----+---------+-----------------+---------------------+---------------------+

Now to actually look through a database ourselves would be time consuming and wouldn’t be very helpful (we would have to look at all the users, get the id of the user that we want, then look the polls and find the user_id that matches our user’s id, then look at all the choices and find the ones that match our poll’s id) so there is a way to interact with databases called SQL (Structured Query Language).  So we can basically ask the database questions and it will return answers.  To get the first result above (the tables) I typed:

show tables;

Notice how my SQL statement or query ends in a semicolon “;”?  From the command line this is usually how you tell your database that you are done typing the commands and it should go out and get you the answers.  The answer that query returns are the list of all the tables in the database.  In order the first three queries I performed were:

(Get the list of tables)
show tables;

(Get the columns in users)
describe users;

(Get all the user rows)
select * from users;

Now that last one was a little different.  In the last one I’m saying
“select” (go get)
“*” (all values of all the columns)
“from users” (from the users table)
So it reads “go get all the values of all the columns from the users table” and the database goes out and fetches it.

The next query I did after I saw the user was to do:

select * from polls where user_id = 2;

The “select * from polls” is the same as what we did for the users but we added a “condition” (something that the database should look for)
“where user_id = 2” (find only records where user_id is equal to 2)
So it reads “go get all the values of all the columns from the polls table and find only records where the user_id is equal to 2” and the database went out and fetched those results for me.

The final query I performed was:

select * from choices where poll_id = 11;
This does the exact same thing as the query above only it looks in the choices table and finds only records where the poll_id is equal to 11.

Multiple Databases

All the examples up until now are assuming you only have one database and you have already selected it.  However that isn’t normally the case. Lets move back into our Excel analogy and apply it to the database and look at some of the SQL used as well.

Starting from the beginning

GOAL: Find the database we want.
EXCEL EQUIVALENT:  Finding the Excel file we want in a folder full of excel files we have to look at the names for all the excel files.
SQL: show databases;
RESULT:

+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| classifieds_development      |
| dotcom_development           |
| environs_development         |
| fiveruns_blog                |
| hcl                          |
| inever_development           |
| kiobo_development            |
| mysql                        |
| playericious                 |
| test                         |
| truth_development            |
| upillar                      |
| upillar_database_development |
+------------------------------+

NEXT STEP: We want to get into the database.

GOAL: Get into the database we want to use.
EXCEL EQUIVALENT: Double click on the file
SQL: use truth_development;
RESULT:
Database changed
NEXT STEP: We want to look at all the tables.

GOAL: Look at all the tables
EXCEL EQUIVALENT: Look through the sheets tabs.
SQL: show tables;
RESULT:

+-----------------------------+
| Tables_in_truth_development |
+-----------------------------+
| answers                     |
| choices                     |

| polls                       |
| users                       |
+-----------------------------+

NEXT STEP: We want to look at all the users.

GOAL: Find all the users.
EXCEL EQUIVALENT: Click on the sheet labeled “Users”
SQL: select * from users;
RESULTS:

+----+-------------+------------------+---------------------+---------------------+
| id | facebook_id | name             | created_at          | updated_at          |
+----+-------------+------------------+---------------------+---------------------+
|  2 |   678674096 | Dan Ahern        | 2008-08-08 23:47:36 | 2008-08-08 23:47:36 |
|  4 |   569397655 | William Harris   | 2008-08-08 23:52:50 | 2008-08-08 23:52:50 |
|  5 |   500092293 | Lynn Wallenstein | 2008-08-24 01:16:52 | 2008-08-24 01:16:52 |
|  8 |  1424543768 | Benjamin Leiter  | 2008-09-11 08:58:08 | 2008-09-11 08:58:08 |
+----+-------------+------------------+---------------------+---------------------+

Hopefully this helps someone somewhere. I wrote it as a beginning tutorial for a friend on database design and administration.

2 Comments (+add yours?)

  1. Alex Chapman
    Mar 09, 2010 @ 19:05:07

    Reading this reminds me of my previous room mate. That guy was one of the smartest human beings I know, but he was a little nutty for my tastes though. Anyways I appreciated reading this, thanks. Will give me something to argue about when I see him.

Leave a Reply