5 Relationships
This chapter shows how to turn a set of entities into a coherent schema by establishing and refining their relationships in an entity–relationship (E-R) diagram for The Sci‑Fi Collective online store. It treats E-R diagrams as a shared design language that encodes structure and rules, and demonstrates how to distill requirements into simple subject–verb–object statements to uncover relationships, iterate with stakeholders, and reconcile gaps (for example, ensuring each purchase references a payment method). With that foundation, the chapter frames relationships in terms of direction, cardinality, and dependency to guide both notation and implementation.
Cardinality specifies how many instances of one entity can be associated with a single instance of another, represented with Crow’s Foot symbols for minimum and maximum on each end. The chapter covers one-to-one, one-to-many, and many-to-many, emphasizing pragmatic adjustments to avoid insertion anomalies: relax certain minimums to zero; in one-to-many, place the foreign key on the “many” side; in one-to-one, put it on the optional side; and for many-to-many, use a junction table with a composite primary key to split the relationship into two one-to-many links. Applied to the store, user–review and user–purchase are one‑to‑many (with optionality from the user side), user–payment_method and payment_method–purchase are one‑to‑many, purchase–product becomes many‑to‑many via purchase_product, and user–user_address is one‑to‑one with the address optional until checkout.
Beyond counts, the chapter introduces strong and weak entities to capture dependency. A weak entity cannot be uniquely identified without its owning strong entity and generally uses a composite key—though in one‑to‑one cases the parent’s key alone may suffice. Junction tables like purchase_product are inherently weak; entities such as review and payment_method are kept strong to avoid unnecessary composite keys and performance costs; and address data is refactored into weak entities (user_address and billing_address) identified by their parent, simplifying constraints and removing extra foreign keys. The result is a cleaner, implementable E-R design that enforces business rules, reduces redundancy, and supports reliable data entry.
Summary
- Relationships are the binding glue between entities. The representations of relationships are informed by the information from requirement gathering and further analysis.
- To represent relationships in an E-R diagram, you typically go through a process involving three steps, including (1) establishing relationships, (2) identifying the cardinality of each relationship, and (3) identifying potential weak entities and determining if they should be converted to weak entities.
- Cardinality is an important characteristic of a relationship between two entities. Cardinalities come in three flavors, one-to-one, one-to-many, and many-to-many. Different flavors of cardinalities are represented differently, and the flavor has implications on where the foreign key is placed.
- In a one-to-one relationship, the max cardinalities are both one, represented by two bars. The foreign key connecting the two entities can be placed in either side.
- In a one-to-many relationship, the max cardinalities of the two directional relationships are one and many. “Many” is represented by the crow’s foot. The foreign key should be placed in the many side pointed by the crow’s foot.
- In a many-to-many relationship, the max cardinalities of the two directional relationships are both many. The representation of a many-to-many relationship requires a junction table sitting between the two entities, and converting the relationship to two one-to-many relationships between each entity and the junction table. The junction table contains the primary keys from both entities.
- Strong and weak entities are another characteristic of a relationship between two entities. If two entities are possible to be represented as strong and weak entities, you need to make decisions on if to do so, depending on whether such a representation makes the E-R diagram simpler and the database more efficient.
- A weak entity would use the primary key of its corresponding strong entity as a part of its composite primary key. Other than that, a weak entity is not represented differently in Crow’s foot notation.
FAQ
What is a relationship in an E-R diagram and how do I identify it from requirements?
Relationships connect pairs of entities that interact. A practical way to identify them is to synthesize requirements into simple subject–verb–object sentences (for example, “A user writes reviews”). Nouns map to entities; verbs suggest a relationship. Connect each referenced pair with a line in the diagram, then refine with cardinalities.What is cardinality and how is it represented in Crow’s Foot notation?
Cardinality describes how many instances of one entity can be associated with a single instance of another, in a given direction. It has two parts: minimum (inner symbol) and maximum (outer symbol), placed near the target entity. Symbols: zero (circle), one (bar), many (crow’s foot). Example: “User to Account” could be 0..many on the Account side and exactly 1 on the User side (from the Account perspective).Why do we sometimes relax minimum cardinalities from 1 to 0?
If both sides require an immediate counterpart (min=1), initial data entry becomes impossible due to foreign key constraints (neither record can be inserted first). Relaxing one minimum to 0 allows one side to exist temporarily without the other. Rules of thumb: relax the “many” side in one-to-many, one side in one-to-one (often the one created later), and commonly both sides in many-to-many unless business rules require otherwise.How do I model a one-to-one relationship and where should the foreign key go?
In one-to-one, each instance on either side relates to at most one instance on the other. If both mins are 1, make one side optional (min=0) to enable inserts. Place the foreign key on the side with min=0 (the optional side). Example: Department–Manager is often modeled with the manager table holding a department_id foreign key, allowing departments to be created before managers.How do I model a one-to-many relationship and where should the foreign key go?
In one-to-many, one side has max=1 and the other has max=many. Always place the foreign key on the “many” side so multiple rows can reference the same “one” row. Examples from the chapter: Review holds user_id; Purchase holds user_id; Payment_method holds user_id; and Purchase holds payment_method_id.How do I model a many-to-many relationship and why use a junction table?
Represent many-to-many as two one-to-many relationships via a junction table that contains the primary keys of both entities. This avoids data redundancy and update anomalies that arise from trying to store foreign keys directly in both base tables. Example: Purchase–Product becomes Purchase_product with purchase_id and product_id (often as a composite primary key), and crow’s feet point toward the junction table.What is a composite primary key and when should I use it?
A composite primary key combines two or more columns to uniquely identify a row. It’s typical in junction tables for many-to-many relationships (for example, purchase_id + product_id) and in some weak entities that depend on a strong entity’s key plus a partial key. Use it when no single column alone guarantees uniqueness or when modeling associative data.What are strong and weak entities, and how do they affect keys?
A strong entity can be uniquely identified by its own attributes; a weak entity depends on a related strong entity for identification. Weak entities often use a composite key that includes the strong entity’s primary key plus a partial key. Choose a weak-entity design if it simplifies the model or improves integrity; otherwise prefer two strong entities for simplicity and performance.Why are User_address and Billing_address modeled as weak entities in the chapter?
They each depend on a single parent (User and Payment_method respectively) in a one-to-one relationship. Making them weak entities lets you key them directly by the parent’s primary key (for example, email for User_address), removing an extra foreign key and simplifying constraints, while still supporting attribute-level filtering (city, state, postal code).What common pitfalls should I watch for when establishing relationships?
- Ambiguous wording like “can” may hide optionality; confirm minimum cardinalities with stakeholders.- One-to-one with both mins=1 blocks inserts; relax one side to 0 and place the FK on that side.
- Putting the FK on the “one” side of a one-to-many won’t work; it must be on the “many” side.
- Modeling many-to-many without a junction table causes redundancy and update issues.
- Be cautious of multiple paths implying the same relationship (for example, Payment_method to User directly and via Purchase), which can introduce inconsistencies.
Grokking Relational Database Design ebook for free
