DataslopeEntity-Relationship Diagrams
Learn how ER diagrams show entities, attributes, keys, and relationships before you build tables.
Before you build a schema, it helps to draw it. An entity-relationship diagram — usually called an ER diagram — is a picture of the main things in your system and how they connect.
The goal is not art. The goal is shared understanding before the SQL hardens your choices into a database.
Why draw before building?
A schema is easier to change on a whiteboard than after data depends on it. ER diagrams let you discuss questions like:
- What are the entities?
- Which attributes belong to each entity?
- Which column identifies a row?
- How do the entities relate?
- Is the relationship one-to-many, many-to-many, or something else?
Drawing first slows you down just enough to prevent expensive design mistakes.
The pieces of an ER diagram
Most ER diagrams show three ideas:
- Entities — the boxes, usually future tables.
- Attributes — the facts inside each box, usually future columns.
- Relationships — the lines between boxes.
Read this as: one customer can place zero or many orders, and each
order belongs to exactly one customer. The PK marker means primary
key. The FK marker means foreign key. The UK marker means unique
key or unique constraint.
Reading the relationship line
Crow's-foot notation puts symbols at both ends of the relationship line. The symbols tell you how many rows may connect on each side.
You do not have to memorize every symbol at once. Start by reading the plain-English relationship label, then inspect the ends of the line.
Example: customers and orders
This is the classic one-to-many model. Customers are independent entities. Orders are also entities, but each order points back to the customer who placed it.
The diagram already hints at the SQL: orders.customer_id will
reference customers.id.
The diagram is not separate from the schema. It is a compact way to reason about the same structure.
Quick check
In an ER diagram, what does FK usually mark?
A column that formats text for display.
A foreign key column that points to a row in another table.
A column that must be first alphabetically.
A table that cannot have relationships.
Example: products and categories
Here is another one-to-many relationship. A category can contain many products. In this simplified model, each product belongs to one category.
The diagram does not show every business rule. For example, it does not say whether prices can be negative. ER diagrams show the core shape; constraints fill in more detail.
Example: students and courses
A student can take many courses, and a course can have many students. The ER diagram shows this by placing a junction entity in the middle.
enrollments is an entity because each enrollment is a real fact: one
student in one course, possibly with its own date or grade.
Example: employees and desks
Sometimes a relationship is one-to-one or optional. Here, an employee may have zero or one desk, and a desk is assigned to exactly one employee when it is assigned.
The UK marker on desks.employee_id hints that the same employee
cannot appear on many desk rows. That uniqueness is what keeps the
relationship one-to-one rather than one-to-many.
Diagrams are models, not screenshots
An ER diagram is a simplified model of what matters for the design. It may leave out indexes, UI labels, seed data, or rarely used columns. That is okay. The diagram's job is to make the structure discussable.
Check your understanding
Why do database designers often draw an ER diagram before writing CREATE TABLE statements?
Because PostgreSQL requires a diagram file before tables can be created.
Because the diagram makes entities, attributes, keys, and relationships easier to discuss before implementation.
Because diagrams automatically generate perfect application code.
Because tables cannot be changed after creation.
In the customers/orders diagram, why does orders contain customer_id FK?
To copy the customer's name into every order.
To connect each order row to the customer row that placed it.
To make every customer have exactly one order.
To sort orders by customer name automatically.
What does the PK marker inside an ER diagram entity usually mean?
The column is optional.
The column stores a password key.
The column is part of the primary key that identifies rows of that entity.
The column is hidden from all queries.