Torna al blog
Sviluppo software

From ER Diagram to MySQL Database: How I Design Relational Schemas That Hold Up in Production

From the ER model to JOINs and aggregation functions: how I learned to design MySQL relational databases starting from requirements, before writing a single line of SQL.

A Badly Designed Database Always Costs You

I've seen applications go to production with databases full of redundancy, missing foreign keys, and many-to-many relationships implemented as comma-separated ID lists in text columns. The result is always the same: slow queries, inconsistent data, painful migrations.

Relational database design isn't complicated — but it requires following a methodology. The ER diagram (Entity-Relationship) is the tool I use to think through data structure before writing a single line of SQL.

Entities, Attributes, and Relationships

An ER diagram describes the problem domain in terms of: Entities (User, Product, Order, Category), Attributes (name, email, price, created_at), and Relationships (a User has many Orders, an Order contains many Products). The critical part is identifying cardinality: One-to-One, One-to-Many, Many-to-Many — each translates to MySQL differently.

One-to-Many: The Most Common Relationship

Implemented with a foreign key on the "many" table. The orders table has a customer_id FK pointing to customers.id. The FK enforces referential integrity — you can't insert an order for a non-existent customer. Decide between ON DELETE CASCADE and ON DELETE SET NULL based on your business rules.

Many-to-Many: The Pivot Table

Requires a junction table: category_product with category_id and product_id, composite primary key, FK to both target tables. You can add attributes to the relationship itself (position, created_at).

JOINs: Querying Relationships

INNER JOIN returns only records with matches in both tables. LEFT JOIN returns all left-table records with NULLs where there's no right match. A common mistake is unintentionally multiplying rows in Many-to-Many JOINs — the course shows how to identify and fix this with subqueries or aggregations.

GROUP BY and HAVING: Analysis Queries

The SELECT ... GROUP BY ... HAVING pattern answers real business questions. HAVING applies after grouping, WHERE applies before — a distinction that confuses almost everyone at first.

What's in the Course

The From ER to MySQL course covers ER design on real cases (e-commerce, booking system, social network) through advanced SQL queries across 24 practical lessons. If you want to build databases that hold up over time, start with design.

Condividi