Today, you will learn how to create a database with more than one related tables.
In this very simple and short tutorial, you will learn two main things:
This follows from the Simple Database Tutorial you completed early in the semester. We will use the same mandate, to design the database for a system that lets me keep track of my vinyl albums collection, but we will expand from it, and design a more complex data model for it.
You probably recall that to create a data model for a system you are asked to design, you need to analyse the mandate and determine the objects used by the system (those become tables), and then determine the properties of the objects (those become columns).
In the analysis of the mandate for the Simple Database Tutorial, we determined that the system will be handling vinyl albums. So, to handle such objects, we decided to create a table to store information about albums and their properties. We designed the table with three columns: title, artist and release year.
| albums | |
| id | int() |
| title | varchar() |
| artist | varchar() |
| year | int() |
Now, when you think about it, an artist is certainly a property of an album. But since you most likely will have several albums by a single artist, you can also think of an artist as an object. So it could be stored in its own table. This would help avoid duplicate entries.
So we can design this system with two tables: one for the albums, and one for the artists.
The same concept was used when we were looking at the family tree database. We had a table for the persons, and a table for the professions. The two tables were related (relational database) by making a connection from one table to another through one table’s ID column. In nerdy DB parlance, the id column from the reference table is called a foreign key in the referring table.
If we take this concept and apply it to the vinyl album system, we could have something like these two tables:
| albums | <— aka, referring table | |
| id | int() | |
| title | varchar() | |
| artist | artists.id | <— foreign key: reference to artists table |
| year | int() | |
| artists | <— aka, reference table | |
| id | int() | <— primary key: used in albums.artist |
| name | varchar() | |
| country | varchar() | |
Every entry in the artists table is assigned a unique ID (the primary key of this table). Let’s take for example the band Queen. Imagine that in the the artists table, the entry for Queen is at id = 7.
When you enter the album News of the World in the database, at the column artist of the albums table, you enter 7 instead of the name Queen. This value refers to the id column in the artists table (the foreign key) for the entry that has name = "Queen".
This lets you avoid duplicate entries with the value "Queen". Every album by Queen in the database has artist = 7. But not only that, arranging your data model that way also allows your system to store other properties of artists. In this example data model above, you will see that the artists table has a column to record the country of origin of each artist. This would not be possible if artists were treated as a property and not an object.
In Adminer, the tool we use to manage our databases, it is relatively simple to create relational tables, but you must follow a procedure.
For this work, you must create the tables that have reference to them in your system first. To follow the language we introduced above, we will call this table the reference table. Every table that refers to another table through a foreign key, a referring table, can only be created if the reference table exists. You cannot have a foreign key to a non-existing table. Pretty logical.
In our example, we would need to create the artists table first (that’s the reference table), then the albums table because albums refers to artists (that’s the referring table).
Note that you do not need to create these tables. I wrote this to explain the process to you, but it is not an assignment.
So the creation of the artists table would look like this:
Once you have done the reference table(s), you can create the referring table(s). In our example, the albums table would look like this:
Notice that the data type for the artist column is the name of the artists table. (left arrow) This is how Adminer manages foreign keys. All the tables in your database that have a primary key setup (in our case the id column) appear at the bottom of the data type drop menu. To establish the connection, you select the name of the table you want to use as foreign key as the data type of the column where the reference happens.
On the same screenshot, you will notice that there is an option setup for the artist column as well. (right arrow) This is to tell MySQL what to do if ever you delete an artist in the artists table and there are references to its id in some entries of the albums table. The same as you cannot have a foreign key to a non-existing table, you also cannot have a reference to a non-existing item in a reference table. In this case, we select the CASCADE option which means that if you delete an artist, every album of this artist will also be deleted.
After creating a table that contains a foreign key reference, you will see it documented in the table’s structure screen in Adminer. This is a good way of confirming that you did it properly. It looks like this:
When you create the tables for a project that has relational tables, make sure you pay attention to the following elements:
That’s it for today. I did say it was a “very simple and short tutorial”, didn’t I?