ER Diagram for MySQL and PostgreSQL — Schema Design Without Auto-Import

Designing a MySQL or PostgreSQL schema? An ER diagram helps you plan relationships before writing CREATE TABLE statements — or document a schema you have already built. This guide covers how to create an ER diagram for SQL databases manually using text-based notation, with complete example schemas for common MySQL and PostgreSQL use cases.

Note: the free ERD maker is a design and documentation tool — it does not connect to a live database. For auto-generating an ERD from an existing MySQL or PostgreSQL database, see the DBeaver section below.

Designing Your MySQL Schema with an ER Diagram

The recommended workflow for database design using an ERD:

1. Draft the ERD first. Before writing SQL, draw the entities (tables), columns, and relationships. The visual makes it easy to spot missing tables, wrong cardinalities, and over-complicated relationships.
2. Verify the relationships. Every relationship line in the ERD corresponds to a foreign key constraint in SQL. Make sure every FK is accounted for in both the ERD and your planned CREATE TABLE statements.
3. Write the SQL from the ERD. Translate each entity block to a CREATE TABLE statement. Add FOREIGN KEY constraints for every relationship line.
4. Keep the ERD updated. When the schema changes, update the ERD code and re-export. Store the .mmd file in your git repo alongside migrations.

Here is an example MySQL e-commerce schema in Mermaid erDiagram format — paste this into the ERD maker to render it:

erDiagram
    users {
        int id PK
        varchar email
        varchar password_hash
        varchar first_name
        varchar last_name
        timestamp created_at
    }
    products {
        int id PK
        varchar name
        text description
        decimal price
        int stock
        int category_id FK
        timestamp created_at
    }
    orders {
        int id PK
        int user_id FK
        decimal total_amount
        enum status
        timestamp created_at
    }
    order_items {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal price_at_time
    }
    categories {
        int id PK
        varchar name
        int parent_id FK
    }

    users ||--o{ orders : "places"
    orders ||--|{ order_items : "contains"
    products ||--o{ order_items : "included in"
    categories ||--o{ products : "contains"
    categories ||--o{ categories : "parent of"

PostgreSQL-Specific Schema Considerations

PostgreSQL supports more data types than MySQL. When designing your ERD for PostgreSQL, you can use PostgreSQL-specific types in the entity definitions — the ERD tool displays whatever text you enter as a data type:

erDiagram
    blog_posts {
        uuid id PK
        int author_id FK
        varchar title
        text content
        jsonb metadata
        text[] tags
        tstzrange publish_window
        tsvector search_vector
        timestamptz created_at
        timestamptz updated_at
    }
    authors {
        int id PK
        varchar display_name
        varchar email
        jsonb preferences
    }
    comments {
        uuid id PK
        uuid post_id FK
        int author_id FK
        text body
        ltree thread_path
        timestamptz created_at
    }

    authors ||--o{ blog_posts : "writes"
    blog_posts ||--o{ comments : "receives"
    authors ||--o{ comments : "writes"

PostgreSQL-specific data types displayed in the ERD (uuid, jsonb, tsvector, ltree, tstzrange) serve as documentation hints — they communicate to other developers that these columns use PostgreSQL features and will not transfer directly to MySQL.

Sell Custom Apparel — We Handle Printing & Free Shipping

ER Diagram for SQL Server (MSSQL)

SQL Server schemas follow the same relational model. Here is a simplified HR schema with MSSQL-style naming conventions:

erDiagram
    Employees {
        int EmployeeID PK
        nvarchar FirstName
        nvarchar LastName
        nvarchar Email
        int DepartmentID FK
        int ManagerID FK
        decimal Salary
        datetime HireDate
    }
    Departments {
        int DepartmentID PK
        nvarchar DepartmentName
        int ManagerID FK
    }
    JobHistory {
        int HistoryID PK
        int EmployeeID FK
        int DepartmentID FK
        nvarchar JobTitle
        decimal Salary
        datetime StartDate
        datetime EndDate
    }

    Employees ||--|| Departments : "belongs to"
    Employees ||--o{ Employees : "managed by"
    Departments ||--|| Employees : "managed by"
    Employees ||--o{ JobHistory : "has history"

The ERD tool is agnostic to database platform — it displays whatever text you use as data types. Use nvarchar, datetime2, uniqueidentifier and other SQL Server types to make the diagram serve as accurate documentation.

Auto-Generating ERDs from Existing Databases

If you already have a MySQL or PostgreSQL database running and want to generate an ERD from it automatically — without writing the schema manually — the free ERD maker cannot do that. It is a manual design tool. For auto-generation, use:

Tool	Databases	How it works	Cost
DBeaver Community	MySQL, PostgreSQL, SQLite, MSSQL, Oracle, MariaDB +	Connect to DB, navigate to schema, right-click to generate ERD	Free
MySQL Workbench	MySQL only	Database menu > Reverse Engineer to generate ERD from live connection	Free
pgAdmin 4	PostgreSQL only	Tools menu > ERD Tool, then auto-load from selected schema	Free
dbdiagram.io	MySQL, PostgreSQL (via SQL export)	Run SHOW CREATE TABLE, paste SQL, dbdiagram parses it	Free account required

DBeaver Community Edition is the most recommended free tool for auto-generating ERDs. It supports more database types than any other free tool, and the ERD view in DBeaver is consistently praised in r/SQL and r/Database communities.

From ERD to SQL CREATE TABLE Statements

Once your ERD is finalized, translating it to SQL follows a direct pattern. For each entity in the ERD:

1. Create a CREATE TABLE statement with the entity name as the table name.
2. Add each column from the entity block with its data type and constraints.
3. Mark the PK column as PRIMARY KEY.
4. For each relationship where this table holds the FK, add a FOREIGN KEY constraint referencing the related table.

-- Example: translating the ORDERS entity and its relationship to USERS

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2),
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

The ERD relationship line USERS ||--o{ ORDERS : "places" corresponds to the FOREIGN KEY constraint: orders.user_id references users.id. Every relationship line in the ERD maps to a FK definition in SQL.

Frequently Asked Questions

Can I create an ER diagram from a MySQL dump file?

Not with the free ERD maker — it does not parse SQL files. However, dbdiagram.io accepts SQL input: paste your CREATE TABLE statements into dbdiagram.io and it auto-generates the ERD. For recurring auto-generation from a live MySQL database, DBeaver Community is the best free tool.

Which ER diagram tool works best with PostgreSQL?

For auto-generating from a live PostgreSQL database: pgAdmin 4 (free, built for PostgreSQL) or DBeaver Community (free, supports PostgreSQL and many others). For manually designing a PostgreSQL schema as a diagram: the free ERD maker or dbdiagram.io both work — just use PostgreSQL data type names in your entity definitions.

How do I show a self-referencing relationship in an ER diagram?

A self-referencing relationship (where a table references itself, like EMPLOYEES with a manager_id that references another EMPLOYEE) is shown as a line from the entity back to itself. In Mermaid syntax: EMPLOYEES ||--o{ EMPLOYEES : "managed by". The diagram renders this as an arc from the entity box back to the same box.

Does the ER diagram validate my database design?

No. The ERD maker is a visual documentation tool — it renders whatever you write without validating SQL correctness, data types, or relationship logic. The design validation happens when you write and run your actual CREATE TABLE statements against a database. The ERD helps you spot structural issues visually before writing SQL.