Phase 1: Foundations

Entity-relationship diagrams & physical data models

Beginner ~2 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you want to build an amazing LEGO city. You wouldn't just dump all your bricks on the floor and start sticking them together randomly, right? That would be confusing! You'd probably start with a plan. You might draw a picture of your city, deciding you'll have a brave "Minifigure" character, a cool "Car" for them to drive, and a tall "Building" where they live. This early drawing, where you decide on the main parts – like your Minifigure, Car, and Building – is like what grown-up data engineers call an Entity-Relationship Diagram, or ERD for short. It's your big picture map, showing the important 'entities' (the key things) in your city.

On your LEGO city plan, for your "Minifigure" entity, you'd probably write down details like their hair color, shirt color, and maybe their special job. These details are like the "attributes" that describe each important thing. You'd also draw lines connecting them: maybe your "Minifigure" drives the "Car," or the "Minifigure" lives in the "Building." These connections are the "relationships" between your entities, showing how they interact. An ERD helps everyone understand your grand LEGO city idea, even before you've picked up a single brick. It's about making sure everyone agrees on what you're building and how the main parts fit together, without worrying about the tiny details yet.

Once your big LEGO city plan (your ERD) is clear, it's time to actually build it! This is where you move from your drawing to creating the real thing, which is exactly like making a "Physical Data Model." On your drawing, a "Minifigure" was just a box, but now you pick a real minifigure with specific hair (a yellow bob), a specific torso (a red shirt with a star), and specific legs (blue). In computers, those general 'entities' become actual 'tables' in a database. And the 'attributes' like 'hair color' become 'columns' with specific types – like saying 'hair color' will always be text, and 'number of wheels' will always be a whole number.

And remember those lines you drew to show the "Minifigure" drives the "Car"? In your physical LEGO build, you make sure the minifigure can actually sit inside the car, using a specific stud. In a database, we use special linking pins, called 'primary keys,' to make sure those relationships work and the computer knows exactly which minifigure belongs to which car. So, when designing systems to organize lots of information, like for an online store or a school's records, thinking first about the "big plan" (the ERD) and then about "how to build it with real pieces" (the Physical Data Model) helps you create super clear, organized ways to store and use information.

As a Data Engineer, you'll constantly work with data stored in databases, and understanding how that data is structured is fundamental. Entity-Relationship Diagrams (ERDs) are your high-level map or blueprint. Think of an ERD as a conceptual design that shows the major 'entities' (the key things or concepts in your system, like 'Customer' or 'Product'), their 'attributes' (the properties or details of each entity, like 'Customer Name' or 'Product Price'), and the 'relationships' between these entities (how they interact, such as a 'Customer places an Order'). ERDs are excellent for understanding business requirements and communicating design ideas without getting bogged down in technical details.

Once you have a clear ERD, you then translate it into a Physical Data Model. This is where the rubber meets the road: you take those conceptual entities and relationships and define exactly how they will be built in an actual database. Entities become 'tables,' attributes become 'columns' with specific 'data types' (e.g., INT for numbers, VARCHAR for text, DATE for dates). Crucially, the relationships defined in your ERD are enforced using 'primary keys' (unique identifiers for each row in a table) and 'foreign keys' (columns in one table that refer to the primary key in another table), ensuring data integrity and allowing you to link related information across tables.

For a Data Engineer, mastering ERDs and physical data models is not just academic; it's intensely practical. You'll use this knowledge to design efficient database schemas for new projects, understand complex existing data structures for ETL/ELT pipelines, troubleshoot data quality issues, and optimize queries. It's the language you'll use to communicate with database administrators, developers, and business stakeholders about how data is organized and should be used, forming the backbone of robust data solutions.

Key Takeaways

  • ERDs are the conceptual blueprints showing entities, attributes, and relationships.
  • Physical Data Models translate ERDs into concrete database tables, columns, and data types.
  • Primary and foreign keys are essential for enforcing relationships and data integrity.
  • These models define how data is structured and stored in a database.
  • Understanding them is crucial for designing, querying, and managing data as a Data Engineer.

Code Example

sql
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

How this code works

This SQL code's job is to create the foundational structure for a database, defining how customer and order information will be stored and linked. This is a direct translation from a conceptual entity-relationship diagram into a physical data model. The CREATE TABLE Customers statement establishes the first table, where each customer_id is a PRIMARY KEY, guaranteeing a unique identifier for every customer. Fields like first_name and last_name use VARCHAR(50) NOT NULL to store text that cannot be empty, while email is VARCHAR(100) UNIQUE, ensuring no two customers share the same email address.

The CREATE TABLE Orders statement then defines a second table for recording orders. Each order receives a unique order_id as its own PRIMARY KEY. Critically, the customer_id in the Orders table is defined with a FOREIGN KEY (customer_id) REFERENCES Customers(customer_id). This creates a relationship between the two tables, ensuring that every order must be associated with an existing customer. A subtle but important benefit of this FOREIGN KEY constraint is that it prevents the creation of "orphaned" orders – orders without a matching customer – which would otherwise lead to inconsistent data. Other fields like order_date store date values, and total_amount uses DECIMAL(10, 2) for precise numerical values.

Related Articles

Hand-picked reads to go deeper on data modeling.

Back to Data Engineer Roadmap