Modeling Relationships and Cardinality for Relational Databases

1) Relationship types that map cleanly to tables

A relationship describes how rows in one table are allowed to connect to rows in another table. Thinking in relationship types helps you choose the right table structure and where foreign keys (FKs) belong.

One-to-one (1:1)

In a one-to-one relationship, each row in A matches at most one row in B, and each row in B matches at most one row in A.

• Business example: Person ↔ Passport. A person has at most one current passport, and a passport belongs to exactly one person.
• Table design translation: Use a foreign key with a UNIQUE constraint to enforce “at most one.” Decide which table holds the FK based on optionality and lifecycle. If Passport is optional, Passport can store person_id as FK and also make it UNIQUE.

Passport(passport_id PK, person_id FK UNIQUE, issue_date, expiry_date)

One-to-many (1:M)

In a one-to-many relationship, one row in A can match many rows in B, but each row in B matches one row in A.

• Business example: Customer → Order. A customer can place many orders; each order is placed by one customer.
• Table design translation: Put the FK on the “many” side (Order) pointing to the “one” side (Customer).

Order(order_id PK, customer_id FK, order_date, status)

Many-to-many (M:N)

In a many-to-many relationship, many rows in A can match many rows in B.

• Business example: Student ↔ Course. Students enroll in many courses; courses have many students.
• Table design translation: Create a junction (associative) table that holds two FKs. The pair of FKs is typically the primary key (or has a UNIQUE constraint) to prevent duplicates.

Enrollment(student_id FK, course_id FK, enrolled_on, PRIMARY KEY(student_id, course_id))

When you see M:N in requirements, you should immediately expect an extra table. This is one of the most direct “requirements-to-schema” moves you can make.

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

2) Optionality and min/max cardinality turned into enforceable rules

Cardinality answers: “How many?” Optionality answers: “Is it required?” Together they form min/max cardinality, which you can translate into constraints and validation rules.

Read cardinality as (min..max)

• 0..1 means optional and at most one.
• 1..1 means required and exactly one.
• 0..* means optional and many allowed.
• 1..* means required and at least one.

Translate min/max into database design rules

Use these translations when moving from a relationship diagram to tables.

• Rule A: “Exactly one” on the child side becomes NOT NULL on the FK. Example: each Order must belong to exactly one Customer.

Order(customer_id NOT NULL FK)

• Rule B: “At most one” becomes UNIQUE on the FK (or a UNIQUE constraint across relevant columns). Example: each Person has at most one Passport.

Passport(person_id FK UNIQUE)

• Rule C: “At least one” on the parent side is not automatically enforced by a simple FK. Example: each Order must have at least one OrderLine. This usually requires application logic, a deferred constraint strategy, or a trigger, because the parent row can exist before children are inserted.
• Rule D: “Optional relationship” becomes a nullable FK (when the FK is on the optional side). Example: an Order may optionally reference a Coupon.

Order(coupon_id NULL FK)

Step-by-step: annotate a relationship and decide constraints

Use this process for each relationship you model.

• Step 1: Identify the two entities and the business verb (Customer places Order).
• Step 2: Ask “For one Customer, how many Orders?” (0..*). Ask “For one Order, how many Customers?” (1..1).
• Step 3: Place the FK on the many side (Order.customer_id).
• Step 4: Convert min cardinality on the FK side into NULL/NOT NULL (1..1 means NOT NULL).
• Step 5: Convert max cardinality into UNIQUE if max is 1 (0..1 or 1..1 on the FK side).
• Step 6: Note any “at least one child” rules (1..*) that need extra enforcement beyond basic FK constraints.

3) Relationship attributes vs. entity attributes

An attribute belongs on an entity when it describes a single row of that entity regardless of relationships. An attribute belongs on a relationship when its value depends on the pairing of two entities.

How to tell where an attribute belongs

• Entity attribute test: If you can determine the value by looking at one entity instance alone, it likely belongs on that entity.
• Relationship attribute test: If the value only makes sense when two entities are connected (or varies per connection), it belongs on the relationship (often the junction table).

Classic example: OrderLine quantity belongs to the Order–Product relationship

Consider Order and Product. An order can contain many products, and a product can appear on many orders: that is M:N. The quantity is not a property of the Order alone (an order has many quantities, one per product). It is not a property of the Product alone (a product is sold in many quantities across orders). Quantity describes the specific pairing of (Order, Product).

Table design translation: create an OrderLine (junction) table and store relationship attributes there.

OrderLine(order_id FK, product_id FK, quantity, unit_price_at_order_time, PRIMARY KEY(order_id, product_id))

Notice unit_price_at_order_time is also a relationship attribute: it captures the price used for that product on that specific order, which may differ from the product’s current list price.

More examples of relationship attributes

• Enrollment(student_id, course_id): grade, enrolled_on, status.
• Membership(user_id, team_id): role_in_team, joined_on.
• Assignment(employee_id, project_id): allocation_percent, start_date, end_date.

Step-by-step: decide if an attribute is on an entity or relationship

• Step 1: Write the relationship as a sentence: “Order includes Product.”
• Step 2: Ask: “Does this attribute describe the Order regardless of which Product?” If yes, it’s an Order attribute.
• Step 3: Ask: “Does this attribute describe the Product regardless of which Order?” If yes, it’s a Product attribute.
• Step 4: If the attribute changes depending on which Order and which Product are paired, it is a relationship attribute and belongs on the junction table (OrderLine).

Guided practice: draw relationships, annotate cardinalities, explain business rules

Practice narrative

You are designing a database for a small clinic scheduling system. The clinic stores Patients, Doctors, Appointments, and InsurancePolicies. Business rules:

• A patient can have zero or many appointments.
• Each appointment is for exactly one patient.
• A doctor can have zero or many appointments.
• Each appointment is with exactly one doctor.
• A patient may have zero or one insurance policy on file.
• Each insurance policy record belongs to exactly one patient.
• During an appointment, the doctor records a diagnosis note specific to that appointment.

Task A: Identify relationship types

• Patient ↔ Appointment is a one-to-many relationship (Patient 1 to Appointment many).
• Doctor ↔ Appointment is a one-to-many relationship (Doctor 1 to Appointment many).
• Patient ↔ InsurancePolicy is a one-to-one relationship (optional on Patient side).

Task B: Annotate min/max cardinalities

Write min/max next to each end of each relationship.

• Patient–Appointment: Patient side is 0..* (a patient may have none or many). Appointment side is 1..1 (each appointment must have exactly one patient).
• Doctor–Appointment: Doctor side is 0..* (a doctor may have none or many). Appointment side is 1..1 (each appointment must have exactly one doctor).
• Patient–InsurancePolicy: Patient side is 0..1 (optional, at most one). InsurancePolicy side is 1..1 (each policy belongs to exactly one patient).

Task C: Explain the business rule behind each cardinality

• Patient–Appointment (0..* on Patient): A new patient may exist in the system before any appointment is booked.
• Patient–Appointment (1..1 on Appointment): An appointment cannot exist without being assigned to a specific patient.
• Doctor–Appointment (0..* on Doctor): A doctor can be employed and listed even if they have no appointments yet.
• Doctor–Appointment (1..1 on Appointment): Each appointment must be scheduled with one specific doctor (not “TBD”).
• Patient–InsurancePolicy (0..1 on Patient): Some patients are self-pay, and the clinic stores at most one active policy record per patient.
• Patient–InsurancePolicy (1..1 on InsurancePolicy): A policy record in this system is stored only in the context of a single patient.

Task D: Place attributes correctly (entity vs. relationship)

Decide where “diagnosis note” belongs. It is recorded during a specific appointment, and it varies per appointment. Therefore it belongs to the Appointment entity (because Appointment is the event that connects one Patient and one Doctor). It is not a property of the Patient or Doctor alone.

Appointment(appointment_id PK, patient_id FK NOT NULL, doctor_id FK NOT NULL, scheduled_at, diagnosis_note)

Now decide where “coverage_start_date” belongs for InsurancePolicy. It describes the policy record itself, so it belongs on InsurancePolicy.

InsurancePolicy(policy_id PK, patient_id FK UNIQUE NOT NULL, provider_name, coverage_start_date, coverage_end_date)

Optional extension: convert your diagram into constraint decisions

• Appointment.patient_id is NOT NULL because Appointment is 1..1 to Patient.
• Appointment.doctor_id is NOT NULL because Appointment is 1..1 to Doctor.
• InsurancePolicy.patient_id is NOT NULL and UNIQUE to enforce the optional 1:1 (each patient has at most one policy, each policy belongs to exactly one patient).