What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
An entity-relationship diagram (ERD), or E-R diagram, is a visual model of the things a system stores information about, the facts recorded about them, and how they are related. It helps turn business rules into a database design before tables and SQL are built.
For example, an online store might model customers, orders, and products. The key design questions are not just what boxes to draw, but whether an order can exist without a customer, how many products an order can contain, and where to record each product’s quantity.
What is an E-R diagram?
“E-R” stands for Entity-Relationship. The entity-relationship model is a way to describe data and its associations; an ERD is a diagram that expresses that model. Peter Chen is widely credited with introducing the entity-relationship model for database design in the 1970s. Lucid’s ERD tutorial provides an overview of the model and its history.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →An ERD is a design and communication aid, not the database itself. An entity is a concept in the model; a table is one way to implement that concept in a relational database. Depending on its purpose, an ERD can show a broad business view, a database-independent logical design, or implementation details such as data types and constraints.
#1 Best Overall
Why use an ERD?
Making the data model visible helps a team agree on requirements before implementation. An ERD can help you:
- Identify what information a system needs to retain.
- Clarify which facts belong together and which associations are optional or required.
- Decide where identifiers and foreign keys belong.
- Spot duplicated or multivalued data before it becomes difficult to maintain.
- Explain a proposed or existing database to technical and nontechnical readers.
- Investigate missing constraints or confusing relationships in an existing schema.
These benefits depend on accurate business rules. A neat diagram cannot correct a mistaken assumption about how the business works.
The building blocks: entities, attributes, and relationships
Entities
An entity is a distinguishable person, object, event, place, or concept about which the system stores information. Common examples include Customer, Order, Product, Employee, and Appointment.
Recommended Free Tools
An entity type describes a category; an entity instance is one member of that category. Customer is an entity type, while the customer represented by customer_id = 1042 is an instance. In a relational implementation, an entity type is often represented by a table, but the terms are not interchangeable: the entity is part of the model, while the table is an implementation structure.
Not every noun in a requirement needs to become an entity. A concept is more likely to deserve one if it needs its own identity, has several attributes or relationships, can occur repeatedly, or has a distinct lifecycle. An address might be an attribute in a simple system; it might need its own entity if customers can have several addresses with different purposes or histories.
Attributes
An attribute is a property of an entity, or sometimes of a relationship. A Customer might have customer_id, first_name, last_name, email, and created_at.
- Simple: treated as one meaningful value, such as
age. - Composite: can be split into useful components, such as an address with street, city, region, and postal code.
- Single-valued: has one value for an instance, such as a birth date.
- Multivalued: may have several values, such as a customer’s phone numbers.
- Derived: calculated from other data, such as age from date of birth.
Relational designs commonly put repeatable values, such as several phone numbers, into a related table rather than a comma-separated column. Composite values may also be split when the application needs to validate, search, or report on their components separately.
Relationships
A relationship describes a meaningful association between entities: a customer places an order, an order contains items, or an employee manages a department. Looking for nouns and verbs in a written requirement is a useful first pass—nouns may suggest entities and verbs may suggest relationships—but it is not a rule. Some nouns are only attributes, and some actions need no stored representation unless their history matters.
Keys: identifying records and connecting entities
A candidate key is an attribute or combination of attributes that can uniquely identify an instance. A table declares one primary key, though it may have other candidate keys enforced as unique constraints. A primary key (PK) is the chosen identifier. A foreign key (FK) is an attribute or group of attributes that refers to a key in another table and helps implement a relationship.
Customer
--------
customer_id PK
name
Order
-----
order_id PK
order_date
customer_id FK → Customer.customer_idHere, the customer_id in Order points to the customer associated with that order. A foreign key is not necessarily unique: many order rows can refer to the same customer. It can be nullable if the business rule permits the association to be absent. A foreign key may also be one part of a composite primary key.
Key choices depend on the requirements:
- A natural key is a meaningful business value, such as an ISBN. It is appropriate only if its uniqueness and stability meet the system’s needs.
- A surrogate key is an assigned identifier, such as an integer ID or UUID, rather than a business fact.
- A composite key uses multiple attributes together, as when an order line is identified by both order and line number.
There is no universally best key strategy. Consider stability, uniqueness, integration needs, and the database implementation.
Cardinality and optionality
Cardinality describes the maximum number of instances that may participate in a relationship. Optionality (also called minimum participation) says whether participation is required. Read both ends of a relationship: “one customer places many orders” also means each order is associated with one customer, if that is the business rule.
| Pattern | Meaning | Example |
|---|---|---|
| 1:1 | At most one instance on either side is associated with one on the other side. | A person and a passport, if each is limited to one. |
| 1:M | One instance on one side can relate to many on the other. | One customer may place many orders. |
| M:N | Many instances on each side can relate to many on the other. | Many students can enroll in many courses. |
Minimums add important detail. For example, a customer may have 0..* orders, while each order must have exactly 1 customer. An employee may manage 0..1 department. Cardinality alone does not say whether a relationship is mandatory.
Common ERD notations
Chen notation
Traditional Chen notation uses rectangles for entities, ovals for attributes, diamonds for relationships, and connecting lines. It makes the distinction among those concepts visible and is often useful for conceptual modeling and teaching.
Crow’s Foot notation
Crow’s Foot notation commonly shows entities or tables as boxes with attributes inside, connected by lines. At a line’s end, a circle indicates zero/optional participation, a bar indicates one, and a three-pronged crow’s foot indicates many. Combinations show minimum and maximum: a circle with a crow’s foot commonly means zero or many; a bar with a crow’s foot means one or many.
For instance, a customer-to-order relationship can be read as customer 1 to orders 0..*, while each order has exactly one customer. Crow’s Foot is widely used, especially for logical and physical relational models, but it is not universal. Tools may vary in symbols and conventions, so check the diagram’s legend.
UML and other conventions
UML class diagrams can show classes and associations and may be used in data modeling, but they are not identical to classic ERDs: their purposes and semantics differ. Across notations, check whether symbols indicate required participation, identifying keys, dependency, or merely navigation. Do not infer a relationship’s meaning from a line style without its legend.
Conceptual, logical, and physical models
- Conceptual: a broad business view of major entities and relationships, often without detailed attributes or implementation choices. Example:
Customer places Order. - Logical: a more detailed, database-independent design with attributes, identifiers, relationship rules, and normalization decisions.
- Physical: a design for a particular database implementation, with tables, columns, data types, nullability, constraints, indexes, and possibly DBMS-specific features.
A logical model does not by itself determine every physical choice. The same design may be implemented differently in PostgreSQL, MySQL, SQL Server, Oracle, or another system. Lucid’s ERD notation guide discusses these model levels.
How to create an ERD
- Set the boundary. Decide what the diagram covers—such as ordering, catalog, payment, and shipping—and leave unrelated areas out of the first model. Large systems are easier to communicate as an overview plus focused subject-area diagrams.
- Write the business rules. Use explicit statements: “A customer may place many orders”; “Every submitted order belongs to one customer”; “An order must contain at least one product.” Resolve ambiguous wording with stakeholders before drawing.
- List candidate entities. Choose durable concepts the system must remember. Do not create an entity just because a noun appears in a sentence; ask whether it needs identity, repeated instances, relationships, or its own lifecycle.
- Assign attributes. For each entity, ask whether each value is atomic, repeatable, derived, required, unique, or actually belongs to a relationship.
- Choose identifiers. Select a primary key for each entity that needs unique identification. Document any other unique business keys and use composite keys only when their combined meaning is appropriate.
- Add and name relationships. Use clear verbs, such as
Customer places OrderandProduct appears in OrderItem. Label roles clearly when the same entity participates twice. - Set minimums and maximums. At each end, ask both “What is the most?” and “What is the least?” Do not leave a line’s meaning implicit.
- Resolve many-to-many associations. In a conventional relational implementation, use an associative entity (junction table) to connect the two sides; put facts about the association there.
- Check redundancy and dependencies. Avoid repeating groups, duplicated facts, and attributes dependent on the wrong entity. Normalization reduces redundancy and update anomalies; it is not a rule to split tables until a diagram looks tidy. Some physical systems intentionally denormalize for workload reasons.
- Test scenarios. Walk through realistic cases: can a customer exist before ordering? Can an order be drafted empty? What remains if a product is discontinued? What should happen when a referenced customer is deleted? The answers may require constraints or history rules beyond the diagram.
Worked example: an online store
Suppose the requirements say: a customer can place many orders; every order belongs to one customer; an order can contain many products; a product can appear in many orders; and the quantity of each product in an order must be recorded.
A direct Order-to-Product many-to-many association cannot hold the quantity for each particular pairing as a single fact on either entity. Introduce OrderItem, an associative entity representing a product on an order:
Customer 1 ─── 0..* Order
Order 1 ─── 1..* OrderItem
Product 1 ─── 0..* OrderItemThe corresponding logical structure could be:
Customer
--------
customer_id PK
name
email
Order
-----
order_id PK
order_date
customer_id FK → Customer.customer_id
Product
-------
product_id PK
name
price
OrderItem
---------
order_id PK, FK → Order.order_id
product_id PK, FK → Product.product_id
quantityThe composite key on OrderItem means one product can appear at most once per order. If the business permits the same product on multiple lines, add a line identifier (for example line_no) and use (order_id, line_no) as the key instead. The diagram therefore encodes a business decision, not just a drawing convention.
The following illustrative SQL enforces core identifiers and references. Exact types, identity syntax, delete behavior, and other constraint details vary by database engine and by the application’s rules.
CREATE TABLE customer (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE product (
product_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
);
CREATE TABLE order_item (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id)
REFERENCES orders(order_id),
FOREIGN KEY (product_id)
REFERENCES product(product_id)
);This represents zero or many orders per customer, at least one order item per completed order as a business rule, and zero or many order items referencing a product. The SQL shown does not itself enforce every business rule: for example, a simple foreign key does not ensure that every order has at least one item, and a check constraint would be needed to require a positive quantity. A production design also needs decisions about draft orders, price snapshots, product deletion, historical names, and repeated product lines.
Edge cases worth recognizing
- Self-referencing relationships: an employee may supervise another employee, or a category may contain subcategories. Show the entity connected to itself and label roles such as
managerandreport. - Weak entities: a weak entity cannot be identified by its own attributes alone and depends on an owner’s key. An order line identified by
(order_id, line_no)is an example. A child table with a foreign key is not automatically weak. - Relationship attributes: quantity, enrollment date, or role may describe an association, not either entity alone. An associative entity provides a place for these facts.
- One-to-one relationships: separate tables can reflect distinct lifecycle, security, retention, or optional extension data, but a 1:1 link can also suggest that two tables belong together. Decide based on access, nullability, lifecycle, and future change—not the ratio alone.
- Subtypes and inheritance: an employee may be full-time or a contractor. ER notations differ in how they show subtypes, and implementation may use one table for the hierarchy, one per subtype, or a base table plus subtype tables.
- Higher-degree relationships: an association among three or more entity types should not automatically be split into binary relationships if that loses the original business meaning. An associative entity may preserve the full rule.
- History and time: if a price, address, or status must be retained as it was at a particular time, a current-value attribute may be insufficient. Model the required history explicitly.
Common ERD mistakes
- Treating every noun as a table: decide whether the concept needs independent identity or lifecycle before promoting it to an entity.
- Putting multiple values in one field: comma-separated phone numbers are hard to validate and query. Model repeatable values in related rows when they need to be managed individually.
- Leaving cardinality or optionality off: a bare line does not say whether zero, one, or many are allowed. Specify both minimum and maximum at each end.
- Modeling M:N directly in a relational schema: a conceptual ERD may show the association directly, but a relational implementation generally resolves it with a junction table.
- Assuming a foreign key is unique: many child rows can refer to one parent. Mark uniqueness only if the business rule requires it.
- Confusing the diagram with the schema: an ERD may omit implementation details; a physical schema needs types, constraints, indexes, and engine-specific decisions.
- Making a giant diagram: showing every audit column and index alongside business concepts can obscure the structure. Separate conceptual overview, logical subject-area models, and physical diagrams.
- Assuming reverse engineering reveals everything: tools can use declared keys and constraints, but implied relationships may be missed, especially when the database has no declared foreign keys.
Choosing an ERD tool
You do not need paid software to learn ER modeling. A simple canvas, a code-based schema tool, or a database-aware modeling suite may each be right depending on the workflow:
- Collaborative visual work: Lucid offers ERD shapes, templates, collaboration features, and database import/export workflows; see its ER diagram tool and database diagram templates.
- Schema as code: dbdiagram uses DBML to define and visualize models, which can suit text-based workflows and version control. Its relationship syntax documents how links are expressed.
- General-purpose diagramming: diagrams.net (draw.io) includes ER table shapes; its SQL plugin can create shapes from SQL.
Features and plan limits can change, so check each product’s current documentation and pricing before relying on a specific capability. For reverse engineering of a production database, a DBMS-specific or enterprise tool may offer more useful navigation and dependency analysis. No tool can infer unstated business rules or guarantee that a generated schema is correct.
What an ERD does not tell you
An ERD does not fully specify application workflows, query behavior, permissions, performance, operational backups, distributed consistency, or every historical-data rule. Traditional ER modeling is most directly suited to relational database design. It can help document other systems, but a conventional ERD does not fully express document nesting, graph traversal, event histories, partitioning, or distributed behavior. Treat the ERD as one part of design and review—not a substitute for schema testing and operational planning.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →
