ER Modeling
Entities, attributes, and relationships - modeling the data your system must remember, before you write a single table.
Entity-relationship modeling answers one question: what must the system remember after the code that produced the data has finished running? A class model describes how objects collaborate at runtime; an ER model describes what survives a restart. Keeping the two concerns separate lets you reason about persistence without dragging in behavior — the behavioral side is covered in Objects and Classes.
The model has three building blocks:
- Entity — a thing with identity worth remembering over time: a customer, an order, a sensor. Two customers with identical names are still two customers.
- Attribute — a fact about an entity with no identity of its own: an email address, a unit price, a timestamp.
- Relationship — a meaningful connection between entities: a customer places orders.
The identity test separates entities from attributes. If you need to reference something from multiple places, track its history, or count its instances, it is an entity. If it only describes something else, it is an attribute.
Primary keys: natural vs surrogate
Every entity needs a primary key — an attribute (or combination) that uniquely identifies each instance. You can pick a natural key that already exists in the domain (an email, an ISBN, a national ID) or introduce a surrogate key: a meaningless generated value such as an auto-increment integer or a UUID.
| Natural key | Surrogate key | |
|---|---|---|
| Uniqueness | Guaranteed only as far as the business guarantees it | Guaranteed by the database |
| Stability | Changes when the business fact changes (people change emails) | Never changes |
| Readability | Self-explanatory in queries and logs | Opaque; needs a join to mean anything |
| Cost | Free — already there | One extra column and index per table |
Common industry practice is a surrogate primary key plus a unique constraint on the natural candidate. That is a heuristic, not a law: a two-row lookup table of currency codes does not need a synthetic id.
Reach for it
- The natural candidate can change over its lifetime
- External systems or URLs will hold long-lived references to the row
- No single attribute is guaranteed unique by the business
Avoid it
- Small stable reference data with an obvious immutable code
- Pure junction tables where the composite foreign-key pair is the identity
- Adding a surrogate while forgetting the unique constraint on the real-world identifier
Relationships and cardinality
Cardinality states how many instances may participate on each side of a relationship:
- One-to-one — a user has at most one profile. Rare; often a sign that two entities could be one table, or that one side is optional detail split out deliberately.
- One-to-many — a customer places many orders; each order belongs to exactly one customer. The workhorse of relational design.
- Many-to-many — a student takes many courses; a course has many students.
A many-to-many relationship cannot be stored directly in a relational schema — a foreign key column can point to only one row. You resolve it with an associative entity (also called a junction table): Student and Course connect through Enrollment, and the many-to-many becomes two one-to-many relationships. This is not a workaround; the associative entity is usually where the interesting data lives. A grade belongs to neither the student nor the course — it belongs to the enrollment:
CREATE TABLE enrollment (
student_id BIGINT NOT NULL REFERENCES student(id),
course_id BIGINT NOT NULL REFERENCES course(id),
enrolled_at DATE NOT NULL,
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);Notation: crow's foot and UML multiplicities
Crow's foot is the most common ER convention in industry tooling: line endings encode cardinality with bars and fork symbols. DiaGO has a dedicated ER editor that draws crow's-foot notation directly — open the Smart Library data model to see entities, primary/foreign keys, and cardinalities on a real schema. UML class diagrams are also widely used for data models (see Class Diagrams); the two conventions map directly:
| Crow's foot reading | UML multiplicity | Meaning |
|---|---|---|
| Exactly one | 1 | Mandatory single |
| Zero or one | 0..1 | Optional single |
| Zero or more | 0..* | Optional many |
The concepts are identical; only the drawing differs. How multiplicities annotate association ends is covered in Associations.
Worked example: e-commerce
Four entities: Customer, Order, OrderLine, Product.
- Customer
(id PK, email UNIQUE, name)— surrogate key, natural candidate constrained. - Order
(id PK, customer_id FK, placed_at, status)— Customer1to Order0..*. - Product
(id PK, sku UNIQUE, name, current_price). - OrderLine
(order_id FK, product_id FK, quantity, unit_price)— composite PK(order_id, product_id). Order1to OrderLine1..*; Product1to OrderLine0..*.
OrderLine is the associative entity resolving the many-to-many between Order and Product, and it demonstrates why relationship attributes matter: unit_price is copied onto the line at purchase time because current_price on Product will change, while the amount actually charged must not.
From class model to ER model
When translating a class model, classes with persistent identity become entities; value-like types collapse into columns of their owner. A Money or EmailAddress class earns its place in code for validation and behavior, but in the schema it is just amount, currency, email. In code an Order aggregate holds a List<OrderLine> and the lines cannot outlive it — a relationship discussed in Composition; in the schema that whole structure is implemented by one mechanism: a foreign key. Every relationship, whatever its cardinality, ends up as an FK column on the many side (or on the junction table). Associations in the class model have direction; foreign keys do not — any join can be traversed both ways.
Common mistakes
- Many-to-many left unresolved. The diagram looks fine until implementation, and there is nowhere to put attributes like
gradeorunit_price. Resolve it explicitly during modeling, not during coding. - Attributes that should be entities. A
shipping_addresscolumn set works until a customer needs both billing and shipping addresses, or until you must know where an old order actually shipped. The moment a fact needs history or multiplicity, it is an entity.
Once entities, keys, and relationships are in place, Normalization is the tool for checking that every attribute is attached to the right key.
Practice
Model a lending library in the ER editor. Members borrow physical copies of books; the same title can exist as many copies; a copy is lent to one member at a time, but over the years many members borrow the same copy. Identify the entities (be careful to separate Book, the title, from Copy, the physical item), choose primary keys and justify natural vs surrogate for each, set every crow's-foot cardinality, and resolve any many-to-many with an associative entity. Decide where due_date, returned_at, and late_fee belong — and explain why none of them can live on Member, Book, or Copy. When you're done, compare against the worked Smart Library data model.