Entity–relationship modelling

Before writing CREATE TABLE statements for a new application, a designer must decide what the database should record. The entity–relationship (ER) model is the standard tool for that conceptual design. It describes the world in terms of entities, the things we keep information about, the attributes of those things, and the relationships among them, together with constraints such as 'each copy of a book belongs to exactly one branch'. An ER diagram is a picture that clients, analysts and developers can all discuss before any code exists. This lesson teaches the concepts and notation of ER modelling and the judgment needed to use them well, through a running example: a public library system. Lesson 7 turns ER diagrams into tables.

Why design first

A database schema outlives most of the programs that use it. Tables designed in a hurry, with the wrong keys or with one fact stored in several places, cause bugs and awkward queries for years, and changing a schema once millions of rows and dozens of programs depend on it is costly. Conceptual design separates two questions: what information the organization needs, and how to store it. Answering the first well, with the people who know the domain, is most of the work. The usual steps are to gather requirements by interviewing users and reading existing forms and reports, build an ER model, review it with the users, map it to relations, refine those relations with normalization (lesson 8), then choose physical structures such as indexes (lesson 9).

Here are the requirements for the running example, as a librarian might state them. The Riverbend Public Library has several branches, each with a name and address. It holds titles, each with an ISBN, a title and a publication year, written by one or more authors. A title can have many physical copies; each copy has a copy number unique within its title, a condition, and belongs to one branch. Members have a card number, name, email and phone, and borrow copies; the library records the date out, the due date and the date returned for each loan, and keeps loan history. Members may place a hold on a title (not a particular copy) and are notified in order. Some members are students, for whom the library records the school; staff members are employed at one branch and have a hire date.

Entities and attributes

An entity is a distinguishable thing in the domain, such as a particular member or a particular branch; an entity set is the collection of all entities of one kind, such as Member. In diagrams the words entity and entity set are often used loosely for the set. Each entity set has attributes that describe its members, and a key, an attribute or set of attributes that uniquely identifies each entity. For Member the key is the card number; for Title it is the ISBN.

Deciding what is an entity and what is an attribute is a judgment call. A useful rule: if something has its own attributes or takes part in relationships of its own, make it an entity; otherwise it can be an attribute. An author has a name and a biography and writes many titles, so Author is an entity, not an attribute of Title. A copy's condition has no further properties, so it is an attribute. A member's school is an attribute until the library needs to store information about schools, at which point School becomes an entity.

Relationships

A relationship associates entities: a member borrows a copy; an author writes a title; a copy belongs to a branch. A relationship set is the collection of such associations of one kind. Relationships can have attributes of their own: a loan has a date out, a due date and a date returned, which describe neither the member nor the copy alone but the borrowing event. The degree of a relationship is the number of entity sets it connects. Most are binary, connecting two; a ternary relationship connects three, and lesson 7 discusses when it is truly needed. A relationship can connect an entity set to itself, a recursive relationship, such as a staff member supervising other staff; the two participants then have role names such as supervisor and supervisee.

Cardinality and participation

Cardinality constraints say how many entities can take part in a relationship with a given entity. For a binary relationship between A and B the main possibilities are one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N). A copy belongs to one branch, and a branch has many copies, so Branch to Copy is one-to-many. An author can write many titles, and a title can have many authors, so Writes is many-to-many. If the library gave each staff member at most one desk and each desk to at most one person, Assigned would be one-to-one.

Participation says whether every entity must take part. If every copy must belong to a branch, Copy's participation in Belongs is total (mandatory); a branch might have no copies yet, so Branch's participation is partial (optional). Together, cardinality and participation give the minimum and maximum number of related entities: a copy belongs to exactly one branch (min 1, max 1); a branch has zero or more copies (min 0, max many). These numbers will decide where foreign keys go, and whether they may be NULL, in lesson 7.

Several notations exist. Peter Chen's original 1976 notation draws entity sets as rectangles, relationships as diamonds and attributes as ovals, with cardinalities written on the lines. Crow's foot notation, used by most drawing tools and in this course's figures, puts relationship names on lines and shows cardinality with symbols at each end: a bar for one, a circle for zero, and a three-pronged crow's foot for many. UML class diagrams express the same ideas with multiplicities such as 1, 0..1 and 0..*. The concepts matter more than the symbols; always include a legend when sharing a diagram with people who may know a different notation.

# Infer the cardinality ratio a sample of relationship data is CONSISTENT with.
# (Data can rule a cardinality out; only requirements can confirm it.)
from collections import Counter

writes = [("Okri", "978-1"), ("Atwood", "978-2"), ("Atwood", "978-3"),
          ("Gaiman", "978-4"), ("Pratchett", "978-4")]           # (author, isbn)
belongs = [("978-1#1", "Central"), ("978-1#2", "Northside"), ("978-2#1", "Central")]  # (copy, branch)

def consistent_with(pairs):
    left  = max(Counter(a for a, _ in pairs).values())   # max partners per left entity
    right = max(Counter(b for _, b in pairs).values())   # max partners per right entity
    l = "many" if left > 1 else "one"
    r = "many" if right > 1 else "one"
    return f"each left entity relates to {l}, each right entity to {r}"

print("Writes :", consistent_with(writes))
print("Belongs:", consistent_with(belongs))

Writes : each left entity relates to many, each right entity to many
Belongs: each left entity relates to one, each right entity to many

The sample shows Atwood with two titles and title 978-4 with two authors, which rules out anything but many-to-many for Writes. For Belongs it shows each copy at one branch and Central with two copies, which is consistent with many-to-one; but three rows cannot prove that a copy could never be recorded at two branches. The requirement 'each copy belongs to exactly one branch' is what decides. As with keys in lesson 2, data can disprove a constraint but never prove it.

Weak entities

Some entities cannot be identified by their own attributes alone. Copy number 2 is meaningful only within a particular title: copy 2 of one ISBN is a different copy from copy 2 of another. Such an entity set is a weak entity set. It is identified by its partial key (copy number) together with the key of its owner or identifying entity set (Title), through an identifying relationship in which the weak entity's participation is total and one-to-many from the owner. In Chen notation weak entities and identifying relationships are drawn with double lines; in crow's foot diagrams the partial key is often marked and the relationship line drawn solid. Other common weak entities are line items within an order, rooms within a building and dependants of an employee.

Not every dependent thing needs to be weak. If the library stamped every copy with a unique barcode, the barcode would be a key on its own, and Copy would be an ordinary (strong) entity with a many-to-one relationship to Title. Designers often prefer such surrogate identifiers because they simplify foreign keys. The weak entity concept remains useful for understanding identity, and many real schemas use composite keys that come from it.

Specialization and generalization

Sometimes entities of one set fall into subsets with extra attributes or relationships. Every student member and every staff member is a member, with a card number and name, but students also have a school and staff have a hire date and a branch. The ER model expresses this with specialization, also called an ISA (is-a) hierarchy: Member is the superclass, and Student and Staff are subclasses that inherit Member's attributes and key and add their own. Generalization is the same structure reached in the other direction, by noticing that several entity sets share attributes and factoring them into a superclass.

Two constraints describe a specialization. Disjointness says whether an entity can belong to more than one subclass: if a staff member can also be a student, the subclasses overlap; otherwise they are disjoint. Completeness says whether every superclass entity must belong to some subclass: if ordinary members exist who are neither students nor staff, the specialization is partial; otherwise it is total. The library's specialization is overlapping and partial. Lesson 7 shows three ways to turn such a hierarchy into tables.

Design choices and common pitfalls

ER modelling involves trade-offs, and experienced designers learn a few recurring patterns. An entity versus an attribute: model a thing as an entity when it has its own properties or relationships. An entity versus a relationship: an event such as a loan, which has its own attributes and history and may happen repeatedly between the same pair, is often clearer as an entity of its own, an associative entity, connected to Member and Copy, than as a plain relationship. A binary versus a ternary relationship: use a ternary only when the fact genuinely involves three entities at once and cannot be reconstructed from binary relationships.

Finally, an ER diagram is a communication tool. Name entity sets with singular nouns (Member, not Members) and relationships with verbs read in a stated direction (Member borrows Copy). Record constraints the diagram cannot show, such as 'a member may have at most ten copies on loan' or 'the due date is after the date out', in accompanying notes; many become CHECK constraints or application rules later. Review the diagram with users by reading it back in sentences: 'Each copy belongs to exactly one branch; a branch may have many copies.' Mistakes that users catch at this stage cost nothing to fix.