10.3 - Relationship Types

One-to-One

• Definition: Each row in Table A is linked to one and only one row in Table B.
• Example: A User table and a UserProfile table, where each user has one profile.

Deep Dive: One-to-One Relationship Example

Users Table:

UserID	UserName
1	Alice
2	Bob

UserProfiles Table:

ProfileID	Bio
1	"Bio for Alice"
2	"Bio for Bob"

Here, UserID in the UserProfiles table is a foreign key that references UserID in the Users table, illustrating a one-to-one relationship. One user would only have one unique user ID and one unique profile ID.

One-to-Many

• Definition: A row in Table A can have multiple linked rows in Table B.
• Example: A Customer table and an Orders table, where each customer can have multiple orders.

Deep Dive: One-to-Many Relationship Example

Customers Table:

CustomerID	Name	Email
1	John Doe	john@example.com
2	Jane Smith	jane@example.com

Orders Table:

OrderID	OrderDate	CustomerID
101	2023-07-01	1
102	2023-07-02	1
103	2023-07-03	2

Here, CustomerID in the Orders table is a foreign key that references CustomerID in the Customers table, illustrating a one-to-many relationship where each customer can have multiple orders.

Many-to-Many

• Definition: Rows in Table A can have multiple links to rows in Table B and vice versa.
• Example: A Students table and a Courses table, where each student can enroll in multiple courses and each course can have multiple students.

Deep Dive: Many-to-Many Relationship Example

Students Table:

StudentID	Name
1	Alice
2	Bob

Courses Table:

CourseID	CourseName
101	Math
102	Science

Enrollments Table (Junction Table):

StudentID	CourseID
1	101
1	102
2	101

Here, the Enrollments table creates a many-to-many relationship between the Students and Courses tables. Each student can enroll in multiple courses and each course can have multiple students.

Self-Referencing Relationship

• Definition: A table has a relationship with itself (aka. Recursive Relationship).
• Example: An Employees table where each employee can have a manager who is also an employee.

---