database-relations/relationships

12 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Many-to-Many junction table example with students and courses

Create a students table with id and name, a courses table with id, name, and credits, and an enrollments junction table with id (IDENTITY primary key), student_id (foreign key to students), course_id (foreign key to courses), enrollment_date, and a UNIQUE constraint on (student_id, course_id) to prevent duplicate enrollments.

One-to-One relationship use cases

One-to-One relationships are used in scenarios like: User Profiles and User Account Details (each user account has exactly one user profile), Employees and Parking Spaces (each employee assigned at most one parking space), and splitting very wide tables into two for better organization or security reasons.

One-to-Many relationship use cases

One-to-Many relationships are used in scenarios like: Customers and Orders (one customer places many orders, each order belongs to one customer), Authors and Books (one author writes many books, each book written by one primary author), and Departments and Employees (one department has many employees, each employee belongs to one department).

Many-to-Many relationship use cases

Many-to-Many relationships are used in scenarios like: Students and Courses (one student enrolls in many courses, one course has many students), Products and Categories (one product belongs to multiple categories, one category contains many products), and Authors and Books (a book written by multiple authors, an author writes multiple books).

Many-to-Many implementation with junction table

Many-to-many relationships are implemented using a junction table (also called an associative table or bridging table) that acts as an intermediary to link records from both tables. The junction table contains foreign keys referencing both main tables. A UNIQUE constraint on the composite foreign keys prevents duplicate relationships.

Many-to-Many SQL example with junction table

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE courses ( id INT PRIMARY KEY, name VARCHAR(255), credits INT ); CREATE TABLE enrollments ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT, course_id INT, enrollment_date DATE, FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id), UNIQUE KEY (student_id, course_id) );

Database relations concept

Relations in databases are connections and links between different pieces of data. They model how different types of information are connected and work together.

Many-to-many relationship pattern

Many-to-many relationships use a junction table with two foreign keys. Each foreign key references a different parent table. Queries join the junction table with both parent tables to retrieve related data. Foreign key options like onDelete: 'CASCADE' can be used to define referential integrity behavior.

One-to-One relationship definition

In a one-to-one relationship, each record in table A is related to at most one record in table B, and each record in table B is related to at most one record in table A. It is a direct, exclusive pairing. Examples include a Users table with exactly one corresponding UserProfiles table, or an Employees table with a one-to-one relationship to a ParkingSpaces table.

One-to-Many relationship definition

In a one-to-many relationship, one record in table A can be related to many records in table B, but each record in table B is related to at most one record in table A. This is a parent-child relationship. Examples include a Customers table with many Orders, an Authors table with many Books, or a Departments table with many Employees.

Many-to-Many relationship definition

In a many-to-many relationship, one record in table A can be related to many records in table B, and one record in table B can be related to many records in table A. It is a complex, bidirectional relationship. Examples include Students and Courses (one student can enroll in many courses, one course can have many students), or Products and Categories (one product can belong to multiple categories, one category can contain many products).

Many-to-Many relationships require a junction table

Many-to-many relationships are not directly implemented with foreign keys between the two main tables. Instead, a junction table (also called an associative table or bridging table) acts as an intermediary to link records from both tables. For example, in a Students-Courses many-to-many relationship, create an Enrollments junction table with foreign keys to both the students and courses tables. The junction table can include additional columns like enrollment_date and a UNIQUE constraint on the (student_id, course_id) pair to prevent duplicate enrollments.

Give your agent this brain