Reverse Engineering an ERD from an Existing Database

From SQL to Diagram

Reverse engineering an ERD means taking an existing database schema (typically a collection of CREATE TABLE statements) and reconstructing the visual Entity-Relationship Diagram. This is essential when joining a project with an undocumented database or when you need to understand a legacy system.

Step 1: Gather the Schema

Extract the DDL from your database:

-- PostgreSQL
\d+ table_name
-- or
pg_dump --schema-only database_name > schema.sql

-- MySQL
SHOW CREATE TABLE table_name;
-- or
mysqldump --no-data database_name > schema.sql

-- SQLite
.schema table_name

Step 2: Identify Entities

Each CREATE TABLE statement becomes an entity. List all tables and their columns:

• Table name → Entity name
• Each column → Attribute with type, nullability, uniqueness
• PRIMARY KEY → Mark as PK (gold in the ERD editor)

Step 3: Identify Relationships

Look for these patterns to discover relationships:

Pattern	Relationship Type
FOREIGN KEY (x_id) REFERENCES other(id)	One-to-many (FK side is "many")
FOREIGN KEY (x_id) REFERENCES other(id) UNIQUE	One-to-one
Junction table with two FKs and composite PK	Many-to-many
x_id REFERENCES same_table(id)	Self-referencing

Step 4: Reconstruct in the ERD Editor

1. Create an entity for each table
2. Add all columns with their types and constraints
3. Mark primary keys and foreign keys
4. Draw relationships based on the foreign key analysis
5. Arrange entities logically (related entities near each other)

Step 5: Verify and Document

Compare the diagram with the actual data:

• Do the cardinalities make sense? (Check with COUNT queries)
• Are there implicit relationships not enforced by foreign keys?
• Are there unused columns or orphaned tables?

Tips for Large Schemas

• Group by domain: Cluster related tables (user management, orders, inventory)
• Start with core entities: Identify the 5-10 most important tables first
• Use color coding: Different colors for different modules or domains
• Ignore metadata tables: Skip migration history, session storage, etc.