Search for a command to run...
Learn the fundamentals with practice questions and next steps at the end
Lead Software Engineer. Mentor. Views are mine alone.
No comments yet. Be the first to comment.
A relational database is, at its core, just a group of data tables that are related to one-another. So we’ll start by looking at this essential building-block: tables.
Typically each table represents one kind of entity, a table is a list/collection of those entities. Each one of these entities in a table is represented by one row/record in the table, while each attribute about those entities are one column in the table.
Database tables typically have a primary key column/attribute called id which is a unique value for every row/record in a particular table. This primary key is the core of how record identification and referencing works, as you will see in the next example.
This animal shelter database has two tables: animals and rooms, so staff can keep track of where their animals are located.
(Note for mobile users: the tables scroll horizontally)
Table: animals
| id | name | species | room_id |
| 1 | Howell | Dog | 1 |
| 2 | Jiji | Cat | 1 |
| 3 | Hops-a-lot | Rabbit | 2 |
Table: rooms
| id | capacity |
| 1 | 10 |
| 2 | 5 |
We know there is a relationship between animals and rooms, but how is that relationship stored in the database? Notice that the animals table has an attribute called room_id. This is what's called a foreign key - an attribute that references the primary key of another table, creating a link that tells us which room each animal is inside.
How many animals are in the room with a capacity of 10? What are their names? Refer to the tables above.
What is a primary key? Describe it in your own words, then compare with the answer below.
What is a foreign key? Describe it in your own words, then compare with the answer below.
There are 3 types of relationships in relational databases:
One-to-one (1:1) relationships: Each row in a table may match up with one, and only one, row from another table.
One-to-many (1:N) relationships: Each row in a table can have a relationship with multiple rows from another table.
Many-to-many (N:N) relationships: Multiple rows from one table can have relationships with multiple rows from another table.
What kind of relationship do the animals and rooms tables have with each other?
If we added a new table called general_medications, what kind of relationship should the general_medications table have with the animals table?
If we added a new table called named_collars, what kind of relationship should the named_collars table have with the animals table?
Entity-Relationship Diagrams (ERDs) are widely used to depict the structure or schema of relational databases. ERDs graphically display tables, their columns called attributes, and their relationships.
Here's what our animal shelter database ERD could look like:
Each box represents a table (entity) in your database. It does not show the data in the table, rather it shows the structure of the table. You can see a list of that table’s attributes, their data type (integer, text etc), and other information about them, like if the attribute is a primary key (PK) or foreign key (FK).
There's a line connecting the tables that represent their relationship. On one end there's a bar |, this means the rooms table is the "one" side of the relationship, while on the other end there’s a crow’s foot shape that means the animals table is the "many" side of the relationship.
(If you’re curious, the circle on the animals side indicates optionality, meaning rooms can be empty. The double bar || on the rooms side means that every animal must have a room. This is called cardinality, a kind of participation constraint.)
Let's say we add a vets table, where one vet can treat multiple animals, and each animal is assigned only one primary vet. There's a one-to-many relationship line between the vets and animals tables. On which side of the line would you put the crow’s foot symbol?
A core philosophy used in relational databases is the concept of normalization. The most important rule of normalization is that each piece of information must exist in exactly one place, a single source of truth, to eliminate redundancy.
For example, notice that we don't store room capacity directly in the animals table. If we did, we'd have to repeat "capacity: 10" in multiple different rows.
Table: animals_unnormalized (bad design!)
| id | name | room_id | room_capacity |
| 1 | Howell | 1 | 10 —> 8? |
| 2 | Jiji | 1 | 10 |
| 3 | Hops-a-lot | 2 | 5 |
If we later needed to change that room's capacity, we'd have to update all those rows and hope we didn't miss any. For example, if someone changed only Howell's room capacity to 8 instead of 10, then we'd have a conflict and we could not be sure if room 1 has a capacity of 8 or 10. Database normalization makes this kind of inconsistency impossible.
To fix this poor design, we store capacity once in the rooms table. Each animal just stores a room_id pointing to that room. Then if the capacity changes, we update it in exactly one place.
This principle of eliminating redundancy is just one aspect of normalization. There are formal levels of normalization called "normal forms", but understanding redundancy is most important when starting out.
Let's say someone suggested adding animal_names directly to the rooms table like this:
Table: rooms (proposed bad design!)
| id | capacity | animal_names |
| 1 | 10 | Howell, Jiji |
| 2 | 5 | Hops-a-lot |
Give one example of something that could go wrong, then compare with the answer below.
rooms table could become out of sync with the actual animals table.
animals table.
animals table) ensures a single source of truth, eliminating redundancy.
SQL is the declarative programming language traditionally used to interact with relational databases. It lets you create tables, and insert, update, and delete, records. Importantly, SQL also allows you to query across multiple related tables using JOINs.
For example, here's a SQL SELECT query to list all cats:
SELECT name, species, room_id
FROM animals
WHERE species = 'Cat';
Executing this SQL statement would return:
name | species | room_id
---------+---------+---------
Jiji | Cat | 1
(1 row)
SQL can query across relationships to combine and filter information from multiple tables. For example, here's a JOIN query to answer Practice Question 1 - finding all animals in the room with a capacity of 10:
SELECT animals.name, animals.species, animals.room_id
FROM animals
JOIN rooms ON animals.room_id = rooms.id
WHERE rooms.capacity = 10;
Executing this SQL statement would return:
name | species | room_id
---------+---------+---------
Howell | Dog | 1
Jiji | Cat | 1
(2 rows)
Importantly, SQL can also be used to create, update, or delete data in the database:
UPDATE animals
SET room_id = 2
WHERE name = 'Jiji';
Executing this SQL statement would move Jiji to room 2 and return:
1 row updated.
Imagine how useful queries like these might be for staff of the animal shelter. They can maintain accurate records and quickly answer questions like “where are all our cats?” or “which animals are in our large rooms?”.
In practice, developers use SQL to update persisted data, feed business logic, and drive user interfaces. In production applications, SQL queries are often embedded within server-side code where they're executed to fetch or modify data based on user requests. During development, developers often test queries directly in a database's command-line interface to explore data and debug issues. Database administrators and data analysts might also write SQL scripts for reporting, migrations, or batch operations.
Note that modern projects often use Object-Relational Mapping (ORM) libraries to expose database operations as functions so that developers can call functions instead of writing SQL. Even so, SQL is important to know for debugging, performance optimization, and understanding what your code is actually doing under the hood.
Now that you have the basics, here are some next steps:
Learn SQL: Head to sqlbolt.com so you can learn and practice SQL for free right in your web browser.
Learn Database Design: Look up and study database normalization and how to create more advanced relationships like many-to-many relationships using junction tables.
Explore Real Databases: Find tutorials to try out popular relational databases for yourself, like PostgreSQL, MySQL, or SQLite - these are common databases used in production applications.
Learn Indexing: Learn how databases use indexes to make queries fast - this becomes crucial as the size of data grows.
Have fun!