Designing a Simple ERD for Your App

Before writing a single line of code, a good application starts with data design. An ERD (Entity Relationship Diagram) is a map that describes what data your application stores and how those pieces of data connect. This article teaches how to design a simple ERD from scratch, understand relationship types, apply basic normalization, then translate it into Laravel migrations using a real example: a simple online store.

Why bother drawing an ERD first? Because database structure mistakes are far more expensive to fix than code mistakes. Renaming a variable takes five minutes, but changing a table structure after the app is live with thousands of rows can mean a complex migration, downtime, and the risk of data loss. An ERD forces you to think through the shape of your data thoroughly before pouring it into code, so important decisions are made while the cost of change is still low. You can draw an ERD with tools like dbdiagram.io, draw.io, or even on paper — what matters is the thinking process.

1. Understanding ERD Components

• Entity: the main object whose data you store, e.g. User, Product, Order. In the database, one entity usually becomes one table.
• Attribute: a property of an entity, e.g. Product has name, price, stock. Attributes become columns.
• Primary Key (PK): a unique column identifying each row, usually id.
• Foreign Key (FK): a column pointing to another table's primary key, forming a relationship between tables.

2. Types of Relationships

Understanding cardinality is the core of ERD design:

• One-to-One (1:1): one row in table A pairs with exactly one row in table B. Example: one User has one Profile. The FK goes on one table with a unique constraint.
• One-to-Many (1:N): one row in A can have many rows in B. Example: one User places many Orders. The FK goes on the "many" side (the orders table has user_id).
• Many-to-Many (N:M): many rows of A relate to many rows of B. Example: one Order contains many Products, and one Product can appear in many Orders. This requires a pivot table in the middle, e.g. order_items.

3. Worked Example: A Simple Online Store

We design four entities: users, products, orders, and order_items. The relationships:

• One User places many Orders (1:N).
• One Order contains many Products through order_items (N:M).

Briefly, the ERD can be drawn like this:

users (1) ----< (N) orders (1) ----< (N) order_items (N) >---- (1) products

Note that order_items is not just an empty pivot; it stores extra attributes like quantity and price (the price at purchase time). That is why we make it its own entity, not a bare pivot.

4. Basic Normalization

Normalization prevents duplicated and inconsistent data. The first three rules to remember:

• 1NF (First Normal Form): each column holds one atomic value, no lists in a single column. Do not store "product A, product B" in one field.
• 2NF: every non-key attribute depends on the whole primary key. This is why order line details are split into order_items.
• 3NF: no attribute depends on another non-key attribute. Example: don't store product_name in orders because it belongs to products; just store product_id.

But note an important exception: in order_items we deliberately store price, because a product's price can change and we want to record the price at the moment of the transaction. This is intentional denormalization for historical accuracy.

5. Translating the ERD into Laravel Migrations

Once the ERD is clear, turn it into migrations. Start with tables that have no dependencies (parents) and move toward tables that depend on them (children).

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->integer('price');
    $table->integer('stock')->default(0);
    $table->timestamps();
});

The orders table has a foreign key to users — this is the 1:N relationship:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->integer('total')->default(0);
    $table->string('status')->default('pending');
    $table->timestamps();
});

The linking table order_items realizes the N:M relationship between orders and products:

Schema::create('order_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained()->cascadeOnDelete();
    $table->foreignId('product_id')->constrained();
    $table->integer('quantity');
    $table->integer('price'); // price at transaction time
    $table->timestamps();
});

6. Reflecting Relationships in Eloquent Models

The ERD also dictates the relationship methods in your models. The 1:N relation from User to Order:

class User extends Authenticatable
{
    public function orders()
    {
        return $this->hasMany(Order::class);
    }
}

The N:M relation from Order to Product through the pivot table, pulling extra columns:

class Order extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class, 'order_items')
            ->withPivot(['quantity', 'price'])
            ->withTimestamps();
    }
}

7. Common ERD Design Mistakes

• Putting the FK on the wrong side: in a 1:N, the foreign key always sits on the "many" side.
• Forgetting the pivot table for N:M: a many-to-many relation cannot be direct; it needs a linking table.
• Duplicating data: storing the product name across many tables makes updates prone to inconsistency.
• Ignoring history: for transactions, copy important values such as price so they don't change when the parent record is updated.
• Wrong migration order: creating a child table before its parent makes the foreign key fail. Always create the referenced table first.

8. From ERD to Real Features

Once the database structure exists, your ERD becomes a guide for building features. Each entity usually pairs with a CRUD controller, each relationship dictates the eager loading you use to keep queries efficient, and each foreign key reminds you to validate that the referenced data actually exists. For example, when saving an order, you loop over the cart items to fill order_items, copying the product's current price at the same time. Because the ERD already describes the links between tables, this code flow becomes clear and predictable.

An ERD also helps the team communicate. A new developer looking at your ERD immediately grasps how the app works without reading thousands of lines of code. So keep the ERD diagram in your repository or project docs and update it whenever the structure changes. A well-maintained diagram is a small investment that saves a lot of onboarding time and prevents misunderstandings.

By mapping entities, choosing the right relationship types, normalizing as needed, then translating to migrations and models, you have a clean database foundation that is easy to extend. Designing the ERD first saves a lot of debugging time later, and it is a habit that separates a thrown-together app from one that is genuinely ready to grow.