For the last few weeks, my entire focus has been on SQL, writing queries, joining tables, using window functions, and figuring out how to transform data that already existed. But this week, I finally hit the inevitable wall: how did those tables get there in the first place?
If you just start writing CREATE TABLE statements the moment you have an idea for an app, you’re going to have a bad time. You end up with duplicate data, missing relationships, and a schema that fights you every time you try to query it.
So, I took a step back and started learning Database Design and Data Modeling proper. I’ve been watching Ansh Lamba’s course on it, and it’s been eye-opening.
I just published Database Design & Modeling Part 1: From Requirements to a Real ER Diagram, where I walked through the exact process of taking a plain-English business requirement (“The system should record orders placed by customers”) and turning it into a structured Entity-Relationship (ER) Diagram.
What Actually Clicked For Me
The biggest realization so far isn’t the syntax of the diagrams, it’s the separation of OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing). I always thought a database was just a database. Realizing that the database powering a live web app (OLTP) is structured completely differently from the data warehouse used by data scientists to run reports (OLAP) made so many things suddenly make sense.
I also finally got to use a Bridge Table in practice. Connecting an Order to a Product in a many-to-many relationship always felt abstract until I actually drew the order_items table sitting between them, holding just the order_id and product_id.
What’s Next?
Now that I have my ER diagram built, the next step is making sure it’s actually bulletproof. That means diving into Normalization (1NF, 2NF, 3NF), making sure there’s no redundancy and that every piece of data lives exactly where it belongs.
Once I get through normalization, I’ll be able to confidently take these abstract diagrams and turn them into physical PostgreSQL databases for my own projects without second-guessing my architecture.
I’ll keep updating the Data Engineering Dashboard on the homepage as I go. Part 2 on Normalization is up next!
