Relationships
Relationships define how entities connect and interact with each other—understanding relationships is essential for designing integrated databases where data from different entities can be meaningfully combined—this topic explores relationship types, how to identify relationships, and design principles that ensure data integrity and query efficiency.
What is a Relationship?
Definition
A relationship is an association or connection between two or more entities. Relationships describe how entities are related and interact with each other in the business domain.
Why Relationships Matter
Without relationships:
With relationships:
Types of Relationships
1. One-to-One (1:1) Relationships
Definition: One instance of Entity A relates to exactly one instance of Entity B
PERSON ———- HAS ———- PASSPORT
(One Person) (One Passport)
Person: John Smith Passport: XYZ123
Person: Jane Doe Passport: ABC456
Each person has exactly one passport
Each passport belongs to exactly one person
Examples:
Design Pattern:
PERSON Table:
– PersonID (Primary Key)
– Name
– PassportID (Foreign Key – unique!)
PASSPORT Table:
– PassportID (Primary Key)
– Number
– IssueDate
– ExpiryDate
Note: PassportID in PERSON must be UNIQUE
(that's what makes it 1:1)
When to Use 1:1
Use 1:1 when:
2. One-to-Many (1:M) Relationships
Definition: One instance of Entity A relates to many instances of Entity B
CUSTOMER ———- PLACES ———- ORDER
(One Customer) (Many Orders)
Customer: John Smith
– Order: #001 – $100
– Order: #002 – $250
– Order: #003 – $75
One customer can place many orders
Each order belongs to exactly one customer
Examples:
Design Pattern:
CUSTOMER Table:
– CustomerID (Primary Key)
– CustomerName
ORDER Table:
– OrderID (Primary Key)
– CustomerID (Foreign Key – NOT UNIQUE)
– OrderDate
– Amount
Note: One customer (ID 1) can have multiple orders
( CustomerID 1 appears multiple times in ORDER table)
3. Many-to-Many (M:M) Relationships
Definition: One instance of Entity A relates to many instances of Entity B, and vice versa
STUDENT ———- ENROLLS-IN ———- COURSE
(Many Students) (Many Courses)
Student: John
– Math 101
– Physics 201
– Chemistry 301
Student: Jane
– Math 101
– Biology 150
Course: Math 101
– Student: John
– Student: Jane
– Student: Bob
Many students take many courses
Many courses have many students
Examples:
Design Pattern (Uses Junction Table):
STUDENT Table:
– StudentID (Primary Key)
– StudentName
COURSE Table:
– CourseID (Primary Key)
– CourseName
STUDENT_COURSE Table (Junction/Bridge Table):
– StudentID (Foreign Key + Primary Key)
– CourseID (Foreign Key + Primary Key)
– EnrollmentDate
– Grade
This table represents the relationship
One row for each Student-Course pair
Relationship Representation
Crow's Foot Notation (Most Common)
One-to-One:
PERSON ||————— PASSPORT
One-to-Many:
CUSTOMER |———— ORDER
Many-to-Many (with junction table):
STUDENT ————|< ENROLLMENT >|———— COURSE
Symbols:
| = one side (exactly one)
< = many side (zero or more)
|| = exactly one on both sides
Identifying Relationships
Step 1: Find Business Rules
Business Rules:
"A customer can place multiple orders"
→ CUSTOMER (1) PLACES (M) ORDER
"An order contains multiple products"
→ ORDER (M) CONTAINS (M) PRODUCT
"A product belongs to one category"
→ PRODUCT (M) BELONGS-TO (1) CATEGORY
"An employee works in one department"
→ EMPLOYEE (M) WORKS-IN (1) DEPARTMENT
Step 2: Verify Cardinality
Ask these questions:
CUSTOMER and ORDER:
Q1: Can a customer have many orders? YES
Q2: Can an order have many customers? NO
Result: 1:M (One-to-Many)
EMPLOYEE and PROJECT:
Q1: Can an employee work on many projects? YES
Q2: Can a project have many employees? YES
Result: M:M (Many-to-Many)
Relationship Attributes
Some relationships have attributes (properties) of their own
STUDENT ENROLLS-IN COURSE
The relationship itself has attributes:
– EnrollmentDate : "2024-01-15"
– Grade: "A"
– Status: "Completed"
In M:M with attributes, these go in junction table:
ENROLLMENT Table:
– StudentID (FK)
– CourseID (FK)
– EnrollmentDate
– Grade
– Status
– CompletionDate
Real-World Relationship Examples
E-commerce System
CUSTOMER (1) ———— PLACES (M) —— ORDER
ORDER (1) —————— CONTAINS (M) —— PRODUCT
PRODUCT (M) ——— BELONGS-TO (1) ——— CATEGORY
CUSTOMER (M) —— FOLLOWS (M) —— PRODUCT (through junction table)
SUPPLIER (1) —— SUPPLIES (M) —— PRODUCT
School System
STUDENT (M) —— ENROLLS-IN (M) —— COURSE
TEACHER (1) —— TEACHES (M) —— COURSE
DEPARTMENT (1) —— OFFERS (M) —— COURSE
STUDENT (M) —— ASSIGNED-TO (1) —— CLASSROOM
COURSE (M) —— HELD-IN (1) —— CLASSROOM
Common Relationship Mistakes
Mistake 1: Wrong Cardinality
WRONG:
CUSTOMER ||————— ORDER (implies one customer has one order)
CORRECT:
CUSTOMER |———— ORDER (one customer places many orders)
Reality: Customers place multiple orders!
Mistake 2: Missing Relationship
WRONG:
Separate STUDENT and COURSE tables with no relationship
No way to query "Which courses does student take?"
CORRECT:
Create ENROLLMENT junction table
Can query students by course, courses by student
Mistake 3: Confusing M:M Representation
WRONG:
STUDENT Table:
– StudentID
– CoursesID (comma-separated: "101,201,301")
Problems: Can't query individual courses, violates normalization
CORRECT:
STUDENT Table: StudentID , Name
COURSE Table: CourseID , CourseName
ENROLLMENT Table: StudentID , CourseID
Allows proper queries and relationships
Relationship Design Patterns
Pattern 1: Optional vs Mandatory
OPTIONAL (0 or many):
Customer can have zero or many credit cards
CUSTOMER |———< CREDIT_CARD
MANDATORY (one or many):
Employee must be in exactly one department
EMPLOYEE |———— DEPARTMENT
Pattern 2: Recursive Relationships
An entity relating to itself:
EMPLOYEE ———— MANAGES ———— EMPLOYEE
One employee (Manager) oversees many employees (Staff)
EmployeeID Name ManagerID
1 John NULL (CEO)
2 Jane 1 (reports to John)
3 Bob 1 (reports to John)
4 Alice 2 (reports to Jane)
Manager (Employee 1) HAS multiple Staff (Employees 2,3)
Staff (Employee 2) WORKS FOR Manager (Employee 1)
Best Practices
💡 Key Insights
📚 Study Notes