Entity-Relationship Diagrams for Database Design

An entity-relationship diagram (ERD) describes the things a system records and the associations between them. It helps you turn business statements such as “customers place orders” into a model with explicit identifiers and relationship rules.

An ERD is useful before writing SQL because it exposes decisions that table names alone cannot answer. Can a customer exist without an order? Can a student enroll in several classes? What identifies an enrollment? This module develops those questions through worked examples and then shows how to implement the answers with keys and constraints.

The diagram is a model of agreed requirements. Its accuracy depends on the rules you establish with the people who use the system.

1. Identify entities, their attributes and meaningful relationships.
2. Read one-to-one, one-to-many and many-to-many relationships in both directions.
3. Distinguish a relationship's maximum cardinality from its minimum participation.
4. Implement relationships with foreign keys, uniqueness rules and associative tables.
5. Recognize optional participation and understand which rules require more than a foreign key.

In a typical relational implementation, an entity type maps to a table, an instance to a row, and an attribute to a column. The logical design may introduce additional tables or restructure the initial conceptual model. A relationship can also have its own attributes: a grade describes a particular student's enrollment, rather than the student or class alone.

The labels 1:1, 1:N and M:N summarize maximum cardinalities. They do not establish whether participation is optional. Read each relationship from both ends.

The passport example is a deliberately limited rule about one current passport in the modeled system. Recording multiple passports or passport history would require a different model. State such boundaries rather than presenting a simplified example as a universal real-world rule.

A one-to-one relationship can support separate security, lifecycle or optional-detail requirements. It should have a reason beyond splitting columns arbitrarily. A one-to-many relationship read in reverse is many-to-one; the foreign key still belongs on the many side.

A many-to-many association is valid in a conceptual ERD. Its relational implementation normally introduces an associative table, also called a junction or linking table. Enrollment turns the table representation into Student → Enrollment and Class → Enrollment, two 1:N relationships, while preserving the original business association.

Optional participation permits zero related instances. Mandatory participation requires at least one. Determine this separately for each direction.

• For one order, how many customers? Exactly one: minimum 1, maximum 1.
• For one customer, how many orders? Zero or many: minimum 0, maximum many.
• For one person in the passport example, how many current passports? Zero or one.
• For one student, how many enrollments? Zero or many under this module's example rules.

A customer can therefore participate optionally in the customer/order relationship even though every order has a required customer reference. Do not describe the entire relationship as mandatory or optional without identifying the viewpoint.

ER modeling uses several notations. Chen commonly shows entities as rectangles, attributes as ovals and relationships as diamonds. Crow's Foot uses endpoint symbols for one, many and optionality. IDEF1X is a distinct notation with its own conventions; it should not be treated as another name for Crow's Foot.

The updated examples in this module also use numeric labels such as 1, 0..1 and 0..many. A label near an entity tells you how many instances of that entity can relate to one instance at the opposite end. Thus a 1 near Customer means one customer per order; 0..many near Order means zero or many orders per customer.

Read an unfamiliar diagram's legend before interpreting its symbols. Continue with the notation comparison and Crow's Foot guide when you need the notation details.

1. Choose identifiers. Define a key that distinguishes each entity instance. Names are not automatically unique identifiers.
2. Place references. For a 1:N relationship, put the parent reference in the child table. A foreign key checks that a supplied reference matches an existing parent key.
3. Enforce a required parent. NOT NULL and a foreign key together require a child row to identify an existing parent. NOT NULL alone cannot validate the identifier.
4. Enforce one-to-one maxima. A foreign key alone allows several child rows to reference the same parent. Add the appropriate uniqueness rule when the maximum is one.
5. Represent M:N associations. Create a table with both parent references and an appropriate key. A pair key such as (StudentID, ClassID) is suitable when each pairing occurs at most once under the stated rules.
6. Handle optionality according to the viewpoint. An unassigned employee may have a nullable DepartmentID. A student with no classes has no Enrollment rows; both references in an existing enrollment remain required.
7. Review additional rules. A required child-to-parent reference does not force every parent to have children. Minimum-child requirements, history and deletion policies need separate decisions.

For example, ClassID should identify the relevant scheduled offering if students can take the same subject in different terms. If repeated attempts are recorded, the enrollment key must distinguish them. Diagram and key choices depend on what one stored instance actually means.

• Guessing from names: a term such as Inventory or MedicalHistory may describe an entity, collection or report depending on the requirements. Define its identity, facts and purpose before deciding.
• Reading only one direction: “a manager has projects” leaves both the employee/project minimums and maximums unclear.
• Mixing maxima and minima: one-to-many does not imply every parent has a child, or that every child may omit its parent.
• Repeating parent facts: enrollment rows should reference student and class facts, rather than copying names into every association. Repeated names in a joined report are a different matter.
• Assuming all rules are encoded: a well-drawn ERD still needs appropriate SQL constraints and transactional rules to preserve its meaning.

Start with a Sentence You Can Test

Write “each order belongs to exactly one customer; each customer may have zero or many orders.” This gives you entities, a relationship, both maxima and both minima. Repeat that process for each association before choosing keys or drawing symbols.

Up next: Lesson 2 introduces the entity-relationship concept through the Employee–Project example. Later lessons develop the three relationship types, associative tables, and mandatory and optional participation.