From ERDs to SQL: Building My First Relational Database

When I first started the Back-End Development section of the Codecademy Full-Stack Career Path, SQL felt like another language to learn. I could write individual queries and understand the syntax, but I hadn't yet experienced what it was like to build a complete database from scratch.

That changed with my Arsenal Club Database project. Rather than working with an existing dataset, I was responsible for designing the entire database, deciding how information should relate to one another, creating the schema, populating the tables, and finally writing SQL queries against my own data.

Looking back, this was one of the most rewarding projects I've completed so far because it brought together everything I'd learned about relational databases into one complete application.

Starting with the Design

One of the biggest lessons I learned was that good SQL starts long before the first query is written. Before opening PostgreSQL, I spent time planning the database structure using an Entity Relationship Diagram (ERD).

Thinking carefully about entities, relationships, primary keys and foreign keys made the implementation process much smoother. It also highlighted how much planning goes into designing scalable databases before any data is ever inserted.

Spending extra time on the design saved me countless hours later when creating relationships between clubs, players, managers, matches, transfers and competitions.

Building the Database

After completing the design, I implemented the schema in PostgreSQL, creating thirteen relational tables and defining the relationships between them using primary and foreign keys.

I also populated every table with realistic football data. Rather than generating random values, I used current Premier League clubs, players, managers and transfers to make the database feel authentic while keeping the scope manageable.

Testing the database in Postbird became an important part of the development process. It allowed me to validate relationships, troubleshoot foreign key constraints and refine the schema as new requirements emerged.

Where SQL Started to Click

The most satisfying part of the project came once the database had been populated. Writing SQL queries against data that I had designed myself felt completely different to working through isolated exercises.

Using joins to connect multiple tables, grouping data with aggregate functions and applying conditional logic using CASE statements demonstrated how powerful SQL can be when supported by a well-designed database.

By the end of the project, SQL no longer felt like a collection of commands. Instead, it became a tool for answering meaningful questions using structured data.

Looking Ahead

This project has given me a much stronger appreciation for back-end development. It's one thing to learn SQL syntax, but it's another to understand why databases are designed the way they are and how thoughtful planning makes querying simpler later on.

More importantly, it has given me confidence heading into larger full-stack projects. Designing a relational database from scratch has shown me that good software isn't just about writing code—it's about modelling real-world problems in a logical, maintainable way.

As I continue through the Codecademy Full-Stack Career Path, I'll undoubtedly build larger and more complex applications, but I suspect this project will always stand out as the point where database design really started to make sense.