Tables of data
Relational databases treat data in the same grid-like way as an Excel spreadsheet. There are rows and columns of data. But databases have extra features that make them easier and more intuitive to work with.
Databases, tables, rows, and columns
Each relational database can hold multiple tables. Tables are the spreadsheet-like grids of rows and columns of data. So if you are familiar with Microsoft Excel documents. you can think of the database as the Excel document, and the tables as the individual sheets within that document.
Databases are faster
With plain text files, As the amount of data stored grows, so too does the amount of time it takes for the server to open the text file and go line-by-line through it. After only several hundred lines of data, there will be a noticeable lag when reading data from a text file. The same problems exist for writing, updating, and deleting data – the more data you have, the more time-consuming those operations get, and other complications crop up.
Databases are optimized to quickly do the most common tasks with data: Creating new data in storage, Reading data from storage, Updating data, and Deleting data. These tasks, called CRUD for short, are exactly what databases like MySQL are designed to do quickly and efficiently. A database server can loop through thousands of rows of data in a tiny fraction of a second.
The database uses indexes to pre-sort data in a variety of ways to make it faster to sort through when you eventually request data from it.
Databases respond to queries
Databases also allow you to pinpoint individual pieces of data relatively quickly. At a high level, you can make a request to the database to return particular rows and columns of data you want to access (whether it be for creating, reading, updating or deleting), and the database will automatically find that data and return it without you having to loop through the other rows and columns manually.
Of course it gets complicated, like everything else, but the idea is straightforward. You make a request for data, and the server responds. A request like this for a particular set of data is called a “query”, and SQL is the language we will use to make queries.
A typical SQL query to request the contents of all the rows out of a table called “users” would look something like this:
SELECT * FROM users
We’ll get into the details of SQL syntax later.
Relationships between tables
Data in one table is often related to data in other tables. This can be most easily explained through an example.
Let’s say, hypothetically, that we’re building a site where users can upload photos to a blog. The first step would be to have a ‘users’ table that stores just the basic information about each user. It might looks something like this:
users table
Notice that there is an “id” field. So each row, meaning each entry in this table, has a unique identifier. Each table must have a unique identifier, known in database parlance as the primary key.
So if I were to ask a question, such as “what is the username of user #3?”, it would be possible, in code, to write a SQL query that answers this question. The code would look for the “username” field of the row in the “users” table with id=3, and that query would return the answer. That query might look something like this:
SELECT username FROM users WHERE id=3
In order to keep track of which images have been uploaded by which users, we might create another table called “images”. This table would have the details of each image which has been uploaded, including which user uploaded it. The “images” table might be set up as follows:
images table
Again, we see that there is an “id” field, which is the primary key for the “images” table, just like we had an “id” field in the “users” table. Each table will have an “id” field that serves as the primary key for that table.
But notice that the “images” table also has a field called “user_id”, which would be used indicate the primary key of the user who uploaded the image in each row. So when we, in our code, create a new row in the “images” table, we would tell the database what data to put in each of the fields; and in the “user_id” field, we would tell it to put in the unique identifier of the particular user who uploaded that image in that field.
Then, if we were, in code, to read the data out of the “images” table, we be able to see that user #1 uploaded photo #2, and user #3 uploaded photo #3, for example.
This “user_id” field in this case is called a foreign key. The term, foreign key, just means that we are using the primary key of one table (in this case, the “users” table) as a piece of data in another table (in this case, the “images” table). That is how we conceptually link the data between two tables.
It’s important to note that the relationship between the “id” field of the “users” table and the “user_id” field of the “images” table is not something that is automatically managed and handled by MySQL. Some database servers do have a special feature where the database automatically “knows” about this relationship. But the MySQL server we are using is ignorant of this relationship, so we have to make sure our code is smart enough to know what data is related to what other data – it’s not built into the database.