ERD fundamentals: how to design database schemas visually

What is an ERD and why draw one before writing SQL

An Entity-Relationship Diagram (ERD) is a visual blueprint of a database. It shows the tables (entities), their columns (attributes), and how tables relate to one another (relationships). Drawing an ERD before writing SQL surfaces design decisions when they are cheap to change — before any code exists.

Developers who skip the diagram often discover structural problems during code review or, worse, after data is already in production. An ERD makes the structure of the database legible to engineers, analysts, and stakeholders all at once.

Entities, attributes, and relationships: the three building blocks

Every ERD is built from three concepts. An entity is a thing the system needs to track — users, orders, products. An attribute is a property of that entity — name, email, price. A relationship describes how two entities connect — a user writes many posts, a post has many comments.

In a database, entities become tables, attributes become columns, and relationships become foreign keys. Understanding these three concepts makes any ERD immediately readable.

Cardinality: expressing 1:1, 1:N, and N:M relationships

Cardinality defines how many instances of one entity can relate to how many instances of another. A user can have one profile (1:1). A user can write many posts (1:N). A post can have many tags, and a tag can belong to many posts (N:M).

Getting cardinality right at design time prevents the two most expensive modeling mistakes: under-specifying a relationship (a 1:N that should be N:M) and over-specifying it (an N:M when 1:N is sufficient).

// 1:1 — each user has exactly one profile
table users {
  user_id    int  [pk]
  username   varchar
}
table profiles {
  profile_id int  [pk]
  user_id    int  [ref: - users.user_id]  // hyphen = 1:1
  bio        text
  avatar_url varchar
}

// 1:N — one user writes many posts
table posts {
  post_id    int  [pk]
  user_id    int  [ref: > users.user_id]  // > = many side
  title      varchar
  body       text
  created_at timestamp
}

// N:M — posts have many tags, tags belong to many posts
// Requires a junction table
table post_tags {
  post_id    int  [ref: > posts.post_id]
  tag_id     int  [ref: > tags.tag_id]
}

• 1:1 relationships are rare — question whether they should be one table.
• 1:N is the most common relationship in relational databases.
• N:M relationships always require a junction table with two foreign keys.
• Cardinality mistakes discovered after data exists are expensive to fix.

Primary keys, foreign keys, and referential integrity

A primary key (PK) uniquely identifies each row in a table. A foreign key (FK) references the primary key of another table, creating the link between them. Together, PKs and FKs define the backbone of a relational schema.

Referential integrity means that every foreign key value must match an existing primary key in the referenced table. Enforcing referential integrity at the database level prevents orphaned rows — orders with no customer, comments on deleted posts — that corrupt analytics results.

table customers {
  customer_id  int     [pk]            // primary key
  name         varchar [not null]
  email        varchar [unique]         // unique constraint
  created_at   timestamp
}

table products {
  product_id   int     [pk]
  name         varchar [not null]
  price        decimal
  is_active    boolean
}

table orders {
  order_id     int     [pk]
  customer_id  int     [ref: > customers.customer_id]  // FK
  product_id   int     [ref: > products.product_id]    // FK
  quantity     int     [not null]
  total        decimal
  ordered_at   timestamp
}
// SchemaStruct exports this to SQL DDL with REFERENCES and
// ON DELETE constraints for the target dialect of your choice.

From diagram to deployable SQL with SchemaStruct

Once your ERD captures the entities, attributes, and relationships correctly, the next step is turning it into runnable SQL. SchemaStruct parses a DSL that matches the structure of an ERD and generates dialect-aware DDL for Snowflake, PostgreSQL, MySQL, or Databricks.

Designing visually first means your diagram and your SQL are always in sync. You review the diagram with the team, export the SQL when the design is approved, and never maintain a separate document describing what the database looks like.